Jeff:

Does that mean a row in the outings table could have a value such as
'Program1/Program2' in the ProgarmID column? If so then it would be;

WHERE Forms!ParentFormName!ProgramID LIKE "*" & [ProgramID] & "*"

But, and it's a big BUT, storing two values at one column position in a row
in a table is not good design. It means the table is not in First Normal
Form (1NF), the definition of which is:

'A relvar is in 1NF if and only if, in every legal value of that relvar,
every tuple contains exactly one value for each attribute.'

Loosely speaking, in the language of the relational model, a relvar (relation
variable) equates to a table, a tuple to a row (record) and an attribute to a
column (field). What you have is a many-to-many relationship type between
outings and programs. A many-to-many relationship type is modelled by
another table which references the primary keys of the two tables which are
related in this way. So a correct design would have Outings and Programs
tables, and an OutingPrograms table with columns OutingID and ProgramID,
there would then be no foreign key ProgramID column in the Outings table.
Diagramatically the relationship looks like this:

Outings---OutingPrograms---Programs

You'll see that the many-to-many relationship type has been resolved into two
one-to-many relationship types. The combo box on your subform would now have
a RowSource of:

SELECT MemberID, FirstName & " " & LastName
FROM Members INNER JOIN OutingPrograms
ON Members.ProgramID = OutingPrograms.ProgramID
WHERE OutingPrograms.OutingID = Forms!ParentFormName!OutingID
ORDER BY LastName, FirstName;

This query will return each member whose ProgramID value equates to the
ProgamID in any row in the OutingPrograms table whose OutingID value equates
to the primary key of the parent form's current record.

Ken Sheridan
Stafford, England

Jeffrey Marks wrote:
Ken

Thanks. That works just like I want it to.

One more question: there are cases where an outing will have two or
more ProgramIDs listed (e.g. Program1/Program2). The Members table is
limited to one. I'm a little fuzzy on the Like command. Would it be:

WHERE Forms!ParentFormName!ProgramID Like "'*" & ProgramID & "*'"

Thanks

jeff

--
Message posted via http://www.accessmonster.com