Subform query using variable from mainform selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a main form with and ID that is selected from one table. A subform on
that form needs to show related records using the ID as a filter. There is a
many to many relationship between the two main tables and I cannot figure out
how to use the Query, CodeView or Macro to make this happen.

ie: MainForm using tblVessel.ID, tblVessel.Name, tblVesselAction.ID,
tblVesselAction.Name, tblAction.ID, tblAction.Name

1) User selects tblVessel and the tblVessel.ID will like to
tblVesselAction.ID that may have many actions for the vessel.
2) I want the subform to use tblVessel.ID to screen out results so the
related tblVesselActions.
3) Next I need to allow user to add / remove values by comparing the
existing records in tblVesselActions and all possible actions in tblActions.

Guidance, white papers, walkthoughs or prayers welcome. :-)

I have tried working through Access 2003 Inside Out and Access 2003 Inside
Track but still can't quite get it.
 
CalNativeKid,

1) & 2) Set the Link Master Fields and Link Child Fields properties of
the subform, to [ID].
3) Represent the Action (ID??) on the subform by a combobox whose Row
Source is the tblAction table.

That's the basic idea, I guess. I got a bit lost with all the IDs etc,
as to exactly how these 3 tables relate to each other, and what fields
are at the basis of the relationships.

And as a complete aside, 'name' is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.

These two articles are somewhat relevant, and may assist you...
http://accesstips.datamanagementsolutions.biz/lookup.htm
http://accesstips.datamanagementsolutions.biz/many.htm
 
Back
Top