Matching from a form to a subform

[Jeffrey Marks]

I am currently working on a database where the user can select an
outing from a form on a combo box.

The outing is assigned to a particular Program (via Program name). The
outing might be applicable to more than 1 program (which would then
have a program name which looks like (program 1/program2)

In a subform on that outing form, I have a combo box that allows the
user to select members to include on that outing. I want to be able to
limit the member names to only those members who have the same Program
name as assigned to the Outing. I'm not clear on how to drive this in
a subform when the Program name for the outing is in the main form.
Can someone help me to understand the best way to do this?

Thanks

Jeff

 

[Tom van Stiphout]

On Fri, 28 May 2010 15:37:41 -0700 (PDT), Jeffrey Marks

It sounds like you have a really bad database design. If there is a
many-to-many relationship between Programs and Outings then there is a
standard way to deal with that in an RDBMS. Something like:
tblPrograms
ProgramID autonumber PK
ProgramName text50 required uniqueindex

tblOutings
OutingID autonumber PK
OutingName text50 required uniqueindex
(this is a base list of outings, not individual instances thereof)

tblProgramOutings
ProgramID long int FK PK
OutingID long int FK PK
(this is the "junction table" for the many-to-many relation. This
allows an outing to be supported by several programs)

tblTrips
TripID autonumber PK
OutingID long int FK required
TripDate datetime required
(this is an instance of an outing)

tblPeopleOnTrips
TripID long int FK PK
PersonID long int FK PK

Do you have a database design along these lines? It sounds like you do
not, and that will make building forms and reports much harder.

-Tom.
Microsoft Access MVP