Building Subforms that relate to information in the current form

[Daniel Brashby]

I have an Item Form that has two sets of related tables:

Items -> Location (many to one)
Location -> Building (many to one)

and

Items -> Category (many to one)
Category -> SubCategory (one to many)

Basically, while the user fills out the information of the Item, it's
serial number, model number, and so on, they also need to be able to
choose where the item is and what categories it belongs to (which are
used for later searching where things are based on buildings, offices
(locations) within buildings, or categories, such as "computers").

Using drop down combo boxes, I can make the first set of links easily.
It's the second set that hurts.

I need another combo box whose options change based on what location
(or category) is chosen in the one before it.

While I can handle minor multi-threaded coding in C#, I'm not up on SQL
or Access, so any assitance would be grand.

PS - there are keys in "Items" that relate to the unique IDs that
"Locations" and "Categories" uses, but it doesn't store information
that directly links to the next level as I felt that would be wrong.
And yes, I have set up my Relations to reflect what I have discussed
here. It seems to work as I can make drop-down combo boxes in the
"Buildings" form that (one to many) automagically pulls up a list of
possible Locations from that building, and likewise with Categories and
SubCategories.

 

[Seth Schwarm]

Daniel:

I remember when I was here years ago. When you see it, I think you will
agree it is simple.

Queries
Modify the query Combo2's Row Source is based on by specifying this criteria
for the field on the grid (which is the same field as Combo1 in your form) -

Forms!ItemFormNameHere!Combo1

Forms
Set the Locked property of the second combo to Yes and save the form. In
the After Update event of the first combo box enter this code -

If Not IsNull(Me!Combo1) Then
Me!Combo2.Locked = False
Me!Combo2.Requery
End If

In the OnCurrent event of the FORM enter this code -

If Not IsNull(Me!Combo1) Then
Me!Combo2.Locked = False
Me!Combo2.Requery
Else
Me!Combo2.Locked = True
End If

I don't think I missed anything. Hope this is helpful.

Seth