If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query on a subform
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 |
#2
|
|||
|
|||
Query on a subform
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 |
#3
|
|||
|
|||
Query on a subform
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 |
#4
|
|||
|
|||
Query on a subform
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 |
Thread Tools | |
Display Modes | |
|
|