How best to implement forms for many-to-many relationships 1

[jonstheone]

There may be no one answer to this but I'm asking for advice on how best to implement a form which allows entry of a many to many relationship

In the attached database I have 2 tables, Individuals and Schemes - this is used to register people (Individuals) on a sports course (Scheme), the pk for each being an ID. Linking the 2 is a IndividualAttendance table, the pk being a composite of the IDs and the date a person attended (since a scheme occurence cannot run twice in one day)

When each scheme occurence runs registers of attendees are collected. What the form needs to do is to allow the scheme and the individual to be selected by name and the date the individual attended to be entered - the individual and scheme details are changed on other forms

 

[MajP]

I can not download your examples on my network, but in general lets say you have something like

tblPeople
peopleID_pk
other people fields

tblSchemes
schemeID_pk
other scheme fields

juncTblSchemesPeople
peopleID_fk
schemeID_fk

1) main form: is bound to tblSchemes
2) subform is bound to a query that links tblPeople to the junction table. The subform is linked to the mainform by schemeID_pk, schemeID_fk.
3) since the subform is linked any new record will automatically get a schemeID_fk in the junction table. Do not even have to show this field.
4) The personID_fk in the subform is controlled by a combobox. The combobox row source returns personID_pk, and other person fields. Show the descriptive fields, but save the ID. Example
return personID_pk, personName from tblPersons
number columns = 2
column widths = 0;1"
bound column = 1

 

[jonstheone]

Thanks for that - I'll try it
The attachment didn't work cos it had spaces in the name

 

[PHV]

The attachment didn't work cos it had spaces in the name
In fact because the path is local to your disk instead of an URL.