Prev: Please help with access expression
Next: Combo Box Query Help
From: Jeffrey Marks on 29 May 2010 12:02
I have a form that displays an outing for members to sign up. There is
a subform where I display the members of the organization (via a combo
box), using a query to pull the current members.

Now the users would like to only show members by program. So I was
hoping to use the ProgramID field from the form to match against the
ProgramID on the member records in the subform's query. Is this
possible to talk back and forth from the form to the subform's query?
How would I do this?

Thanks

Jeff
From: KenSheridan via AccessMonster.com on 29 May 2010 14:31
You can reference the parent form's ProgramID control as a parameter in the
combo box's RowSource property, e.g.

SELECT MemberID, FirstName & " " & LastName
FROM Members
WHERE ProgamID = Forms!ParentFormName!ProgramID
ORDER BY LastName, FirstName;

In the subform's Current event procedure requery the combo box with:

Me.YourComboBoxName.Requery

Ken Sheridan
Stafford, England

Jeffrey Marks wrote:
>I have a form that displays an outing for members to sign up. There is
>a subform where I display the members of the organization (via a combo
>box), using a query to pull the current members.
>
>Now the users would like to only show members by program. So I was
>hoping to use the ProgramID field from the form to match against the
>ProgramID on the member records in the subform's query. Is this
>possible to talk back and forth from the form to the subform's query?
>How would I do this?
>
>Thanks
>
>Jeff

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

From: Jeffrey Marks on 30 May 2010 07:36
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
From: KenSheridan via AccessMonster.com on 30 May 2010 13:23
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

 | 
Pages: 1
Prev: Please help with access expression
Next: Combo Box Query Help