I have an inventory asset database that I would like to add a treeview control to that would show all the different types of assets by location, or by asset type, or by whatever sort I choose to implement. Selecting an item in the lefthand treeview would show summary info common to all assets (vendor, model, cost, date acquired, etc.) on a righthand summary tab. There would also be one or more righthand details tabs showing data specific to an asset type (routers, for example). Currently, all my asset types have their own tables and unique forms.

I am thinking I could have a global "entity" table for all assets for common data, and then details tables for asset specific data. My problem is I cannot envision how to have the correct details subform displayed automatically when the asset is selected in the treeview without having an asset type field and manually displaying the proper subform via code. Many of the assets have multiple specific subforms as well and coding everything could get complicated and it doesn't seem very elegant.

I'm wondering if there is a design I'm not seeing where Access would do the bulk of the work?

ScottGem

First, let me say I'm not a big fan of the treeview control (though I know people who are) and I rarely use it. I'm not even visualizing how you want this to work.

You definitely do not have proper design if you have all your asset types in separate tables. The way I have set up such an app is to have an Asset table. That table lists the data that identifies the asset and is specific to the asset itself (i.e make, model, type, serial number, locationID, etc.)

I would then have a second table for asset attributes that would be more specific to the asset type. That table would look like this:

tblAssetAttributes
AssetAttributeID (PK Autonumber)
AssetID (FK)
AttributeID (FK)
AttributeValue

You would then add a lookup table for attributes:

tluAttributes
AttributeID (PK Autonumber)
Attribute
AssetTypeID (FK)

For example, lets consider routers. you might add an asset record for a router with 16 ports. So you would select the asset, select Ports as the attribute and enter 16 as the value. By including the AssetTypeID in the lookup you can restrict the Attribute list to those specific to that asset type.

I think this would then work well in your treeview, allowing you to drill down to a listing of attributes.

Scleros

I understand what you're saying, but the lightbulb hasn't come on. Attached is a sample image of what I had in mind. How do I make the tabs on the right come and go depending on the asset selected in the treeview as not all assets have the same data. Is there a sample database somewhere like this I could dissect?

Attached Images

 

ScottGem

OK, Now I see what you are looking for, and its a good use of the Treeview control. However, as I indicated, I really don't use that control so I'm not the best person to help here. What I can tell you is that when you get to the level of the actual asset, you will need to capture the PK value of the asset and pass that to the form to filter the data for that asset. How to do that I'm not sure.

What I suggest is that you repost this at utteraccess.com. You can attach the screenshot to your post. There are a number of Access gurus there and I'm sure they can help with this one, better than I.

Scleros

Ok, thanks.

ScottGem

Send me a link to your post there. I'd like to followup on this.