Subform from a junction table

OtterMouse used Ask the Experts™
I am developing a contacts database and need to have a method of inviting people to events.
I have a 2 tables
and have made a many to many relationship between them by creating a new table
'event_person junction'

I have a main form
'person details' whose record source is persontbl
and then a subform within the main form called events whose record source is event_person junctiontbl where the events are listed and there are yes/no tick boxes to invite. This works well with the existing records, however, when i add a new person on the main form the junction table does not add events records and therefore the subform doesn't work

How do i get round this? Have i done this in the best way to start with?

All help gratefully received
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm guessing that event_person has a Yes/No field to indicate invitation status and that when you set up the tables you inserted rows for each event/person combination into it.
Although it's possible to do it this way, it's not the standard design - If there were thousands of people and hundreds of events the design wouldn't scale well. And what happens to events that are no longer current? Or when an event is added?
The usual way to approach this is to use the presence of a row in the juntion table to indicate a person is invited. Bind a combo box in the subform to the junction table's EventID and use the Events table as the source for the combobox list. The subform then lists only the events for which an invite exists, and a new invite is added by selecting the event from the combo box in a new row.
Alternatively, you can continue in your original design by running an Insert query in the AfterInsert event of your main form:

Private Sub Form_AfterInsert()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Insert INTO PersonEvent(PersonID, EventID, Invited) Select " & Me.PersonID & ", EventID, False From Event"
    DoCmd.SetWarnings True
End Sub

For completeness you should also run a delete query in the AfterDelete event if you will ever delete a Person record
Hamed NasrRetired IT Professional

Compare with this idea:

persons (pID, ...)
events (eID,...)
person_event (pID, eID, ...)

Form: persons (pID, ...)
SubForm events: Record source a EventQuery (pId, eID, eName, ...) using events and person_event tables
Linked fields: pID


Thanks for your replies, i don't have the opportunity to try the solutions yet but will do tomorrow and let you know how i get on.


Thanks for your solution, you picked up exactly what i was after and gave me an easy to understand and implement solution. I went with the first solution - much cleaner in the long term i think.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial