Many to Many on a form

[Jeff]

I have a many-to-many relationship which I want to show on 2 forms.

*Member table*
Member ID
Member First name
Member Last Name

*tblAttendance*
Member ID
Event ID
Comments

*Events table*
Event ID
Event description

I've started my first form to display a member and the associated
events. One member can attend more than 1 event and 1 event can have
more than 1 member attending, so I believe I've set up the tables and
relationships correctly.

I realize that I need to use a subform to display the information on
the event and comments and that I'll need a query most likely to get
the data I need. But I'm not exactly sure how to do that. For the
member form, I'd like 1 row per event.

I am also writing a second form which shows information by event. On
that particular form, I'd like to show the attending members are part
of a list box. Can I do that?

Thanks

Jeff

 

[tina]

comments inline.

I have a many-to-many relationship which I want to show on 2 forms.

*Member table*
Member ID
Member First name
Member Last Name

*tblAttendance*
Member ID
Event ID
Comments

*Events table*
Event ID
Event description

I've started my first form to display a member and the associated
events. One member can attend more than 1 event and 1 event can have
more than 1 member attending, so I believe I've set up the tables and
relationships correctly.

yes, from the information posted, i believe you have, too. my only
suggestion here is that if you posted "real" table/field names, that you
remove the spaces, as

tblMembers
MemberID
FirstName
LastName

I realize that I need to use a subform to display the information on
the event and comments and that I'll need a query most likely to get
the data I need. But I'm not exactly sure how to do that. For the
member form, I'd like 1 row per event.

yes, you'll need a subform. yes, you'll need a query. create a mainform,
bound to tblMembers (frmMembers). create a subform, bound to a query or SQL
statement, as

SELECT MemberID, EventDescription
FROM tblAttendance INNER JOIN tblEvents ON tblAttendance.EventID =
tblEvents.EventID

open the mainform in Design view, and add a subform control
(ChildAttendance). set the control's properties as

SourceObject: sfrmAttendance
LinkChildFields: MemberID
LinkMasterFields: MemberID

the child link refers to the foreign key field MemberID in tblAttendance.
the master link refers to the primary key MemberID in tblMembers.

open frmMembers. as you move from record to record, the subform attendance
records will automatically change to match the current member record. no
coding needed.

I am also writing a second form which shows information by event. On
that particular form, I'd like to show the attending members are part
of a list box. Can I do that?

yes. you *could* set up a form/subform just like the scenario i described
above, only with the mainform bound to tblEvents instead of tblMembers, the
subform bound to a query as

SELECT EventID, FirstName, LastName
FROM tblAttendance INNER JOIN tblMembers ON tblAttendance.MemberID =
tblMembers.MemberID

and the link properties of the subform control set to EventID rather than
MemberID.

to do a listbox instead: create a form bound to tblEvents (frmEvents). add
a listbox control (lstMembers), and set the RowSource property to a query or
SQL statement, as

SELECT FirstName, LastName
FROM tblMembers INNER JOIN tblAttendance ON tblMembers.MemberID =
tblAttendance.MemberID
WHERE EventID=[Forms]![frmEvents]![EventID]

add code to frmEvents' Current event procedure, as

Me!lstMembers.Requery

as you move from record to record in frmEvents, the listbox will change to
show the members who attended each event.

hth