Linking multiple subforms

[Soupy8728]

Hello all,

I have a MainForm (unbound) that has 4 subforms on it. The subforms are called Locations, Inventory, Groups, and SubGroups. I have been able to link master/child fields using the LocationID on the Locations subform. I watched Richard Rost's YouTube video on how to syncronize the forms and all is working well with the following exception. When I select a Location, the Inventory and Groups subforms filter appropriately. What I'd like to further do is link the SubGroups subform to the Groups subform so that when I select a record on Groups, the SubGroups form filters further. (man I hope I'm explaining this well). If you are not too confused by now, I'd really appreciate some guidance.

[Welshgasman]

One way is to put the value of thesubform control on the mainform and use that to link the other subform. You would hide that control on the mainform

[Micron]

As long as OP is willing to use VBA because you need to use a form's Current event to set the value of the linking control? However, it may be that the control you refer to needs to go on the parent subform? Not sure of what the relationship is from one form to another, but I'm thinking the problem subform contains records that are related to a parent subform. There might not be a pk/fk relationship between the main form and this last subform.

EDIT - WGM, never mind. I misinterpreted your suggestion.

[Soupy8728]

One way is to put the value of thesubform control on the mainform and use that to link the other subform. You would hide that control on the mainform

Yes sir, I did that using Richard Rost's video information and everything links fine (all four subforms link with no issues). Would I have to add another control in addition to the first one? They all link with LocationID but I'd like the SubGroups subform to link to the Groups subform via GroupID. Thank you for you awesome help!

[mike60smart]

Are you able to upload a screenshot of the relationship diagram so we can see all table fields?

[Welshgasman]

Well you can only link one or the other.
Groups are linked by locationid, so by relationships, if you link groups to subgroups, they will inherit the locationid from groups parent?

[ArviLaanemets]

You didn't gave any info about your table structures (what would be essential to get better responses) in your post, so I can only guess here, but I suspect it is off somewhat.

It looks like you have some items. Every item belongs into some item subgroup (like stool, or desk). Those subgroups then belong into some item groups (like furniture). All those items are placed somewhere (in some location). And probably this location of at least for some items may change (the item may be moved to another location).

So you need tables like:
tblLocations: LocationID, LocqationDescription, ...;
tblItemGroups: ItemGroupID, ItemGroupName;
tblItemSubgroups: ItemSubgroupID, ItemGroupID, ItemSubgroupName;
tblItems: ItemID, ItemSubgroupID, ItemDescription, ...;
tblItemLocqations: ItemLocationID, ItemID, LocationID, LocatedAtDate, ..., [LocationStatus] (this table is essentially what you have named Inventory, but it contains the full history of item movements to different locations. LocationStatus is optional field, which is 1 wehn the locatoin of item is current one, and 0 otherwise. Or you can have an UDF instead, which returns the current location of item at any date.)

Is this something like you have?

[Gicu]

Yes, you just add a second hidden control to the parent form that is bound to the GroupID on the Groups subform; then use that as the master field in the linking for the subgroup subform (depending on your design needs you could use both, separated by semicolon Link Child: LocationID;GroupID, Link Master: frmMain!txtHiddenLocationID;frmMain!txtHiddenGroup ID).

Cheers,