Table Design for Assets Inventory with Multiple Asset Type/Asset Attribute Combos

[RCopeland]

Alright, I need to set up a database for tracking fishing supplies inventory. The problem is that for each type of asset (rods, reels, jigs, hooks, etc.) there are different combinations of attributes that need to be entered.

Eventually I would like to be able to create a form for adding new assets to the database, but am having trouble structuring my tables in a way that allows me to create the form that I want.

The form that I envision would pop-up and give me the option to type the new assets name in a text box, and select the asset type from a combo box. After these are filled out, there will be a "next" button that moves me to a new pop-up form.

This new pop-up form will allow me to enter specific data about the asset I have just entered. The dilemma is that when I select "Rods" for asset type, I should be able to enter specific data that would not apply to a selection for "Reels." I am having a lot of trouble designing my tables to give me this kind of freedom in my forms.

 

[Pat Hartman]

If you are designing an application to sell, it needs the tables need to be structured correctly to allow precise data entry but since you are doing this application for a family member, you will need to decide how normalized you actually need to be. In a system where there probably are never going to be more than 20-30 of something, how discrete does the search feature need to be?

To create a completely normalized schema, use the asset table to hold all common fields. The Asset PK is the field that will be used throughout the app as the FK when linking back to the Asset table. Then create separate tables for each unique class of Asset. Each of these will be related 1-1 with the Asset table.

tblAsset:
AssetID (autonumber PK)
AssetTypeID (FK to tblAssetType)
AssetDesc
Model
Manufacturer
SerialNumber
DatePurchased
...
tblRods:
AssetID (Integer PK and FK to tblAsset)
Length
Material
...

The form will show all the common Asset fields. In the Current event of the form, the subform will be replaced with the subform for that particular type of Asset.

Me.SubFormContainer.SourceObject = "sfrm" & Me.AssetTypeID

 

[RCopeland]

Me.SubFormContainer.SourceObject = "sfrm" & Me.AssetTypeID

Can you please explain exactly how this particular part works? The me part seems to be a Macro. Is this right?

 

[RCopeland]

And just to be totally honest, I am no good with anything code. I am a college student just getting started so please don't get upset with my lack of understanding!

 

[Pat Hartman]

"Me." is an object qualifier and only works within the code module of a form or report. It refers to "me" the open form/report object and is used to reference properties/methods of the form/report. In this case, we are referring to a subform control that I named "SubFormContainer" and its "SourceObject" property which specifies the name of the form that the container holds. The code is concatenating a prefix "sfrm" with the AssetTypeID to identify the form to load.

If AssetTypeID is numeric, you would name your subforms
sfrm1
sfrm2
etc.
If AssetTypeID is text (I would name it AssetTypeCD in this case), you would name your subforms:
sfrmRods
sfrmReels
etc.

 

[RCopeland]

So, in my main Assets form, I would add a subform. This subform would then need to have a subform control named subformcontainer? how do I do that?

 

[Pat Hartman]

Open the form in design view. Grab the subform control and put it on the main form. If the wizard makes you choose an actual subform, choose one of the forms from the set. Change the Name of the control to the name I suggested. Then you can take out the SourceObject name so the control is unbound. Size it to fit the largest of the subforms.

 

[RCopeland]

Thank you! Alright, I have successfully done this. Now, when I put the code in my On Current for the main form, I get a runtime error. I am not exactly sure why.

What I have is the main form combo box you named AssetTypeID I have named Catagory. My subforms are named sfrmRods, sfrmReels, etc. When I put the code in my main form, it looks like this:

Private Sub Form_Current()
Me.SubFormContainer.SourceObject = "sfrm" & Me.Catagory
End Sub

 

[RCopeland]

More specifically, it is a Run-time error '2101': The setting you entered isn't valid for this property.

What am I doing wrong now?

 

[Pat Hartman]

Are the subforms all built and will sfrm+category result in the correct name?

What happens if you comment out the code and hard-code the name of one of the subforms in the SourceObject.

 

[RCopeland]

Yes, the subforms are all built and named according to "sfrm"+Catagory.

Not sure what you mean by commenting out the code and hard-coding the name of one of the subforms in the sourceobject. Sorry, you probably feel like you're spoon feeding me this stuff.

 

[RCopeland]

You know what, I am just too uneducated in VBA to do anything without you giving me very explicit step by step directions. I think it would be of benefit to both of us if I just put my project on hold for now, step away from all this frustration, and take the time to learn the basics of VB from the most simple up.

Do you have any advice on where I need to go to begin learning how to write code from the beginning? TIA!

 

[Pat Hartman]

I don't normally recommend unnormalized solutions but I will make an exception here. Try making a single table with all the attributes. This technically doesn't violate any of the normal forms for the purists but what it does do is result in large numbers of null columns because they are only applicable for certain types of assets. You may find that there are many attributes that are similar enough so you would feel comfortable using them for more than one type of asset. Keep the table cleanly organized - common fields, rod fields, reel fields, etc. That will make it easier for you when you are creating queries and for when you ultimately split the single table into several 1-1 tables.

You can create a tabbed interface with all the tabs always available so you don't need to write any code to hide or show specific tabs. Organize the specific fields on their own tab. Common fields in the "header" part of the main form. Rod fields on the "rod" tab, etc. You can create this structure will little or no VBA. The main form would be bound to a query that selects all the fields from the table and since the tabs contain bound controls rather than subforms, they are essentially just one big form that you view in pieces. Given this design, I would not make separate subforms for each asset type because I don't like having multiple queries against the same record open at the same time.