Subform with two keys




I have a problem that is apparently simple but which I struggle to
solve. I have the following tables (simplified):

Table tblClient

Table tblContact

Table tblMeeting

Table tblAttendee

So basically I have contacts which are all linked (through a
relationship) to a specific client. I also have meetings links to a
specific client. And I have attendees, with a double link to
1) the contact
2) the meeting

so that I can have an unlimited number of attendees (contacts) from
the same client to one specific meeting.

I struggle to create a form that will allow to add these attendees to
a meeting. I have a form meeting, to which I have added a subform
attendees. The subform is linked to meeting through two links: the
meeting id and the client id.

The subform works absolutely fine to display data, but it doesn't seem
to work when I am trying to add another attendee, as access doesn't
seem to understand that the new attendee must match both the meeting
and the client id, and therefore does not automatically fills these
fields (it expects me to do it, and since I didn't create a text box
to do it, it is never filled). I could do it manually in the subform,
but is there a way to get access to automatically fill the new entries
in a subform based on the 2 ids of the form?

Thanks in advance

Arvin Meyer [MVP]

If you set the linkmaster/linkchild properties to BOTH of the fields, you
should have no problem. You may want to add the clientID to some of the
other tables (it may already be, but I can't tell from your field names).
There is an example of cascading subform in the Orders/OrderDetails forms in
the Northwind sample database that comes with Access that may help.

Pieter Wijnen

One possible solution is to use a ComboBox with this as rowsource (bound
column 1, RowSource: Attendee_contact)

Select A.ContactID, A.Contact_Name
From tblContact A
Where A.Contact_Client=Forms!MainForm!Client_id

you should then only need to link the subform via meeting_id &




Thanks for your answer. I thought my links were already set correctly:

Attendee_meeting <-linked-> Meeting_id
Attendee_contact <-linked-> Contact_id
Contact_client <-linked-> Client_id
Meeting_client <-linked-> Client_id

In the query I use in the subform, I join the tables attendee and
contact (so that I have access to all fields in attendees and
contacts), and in the link between the form and the subform I have:

Link Child Fields: Attendee_meeting;Contact_client
Link Master Fields: Meeting_id;Meeting_client

But it looks like even if the double link works when selecting the
data to display (it only shows the attendees for this meeting and for
this client), it is not enforced when adding new data. For instance,
in the combobox that select the "Contact_name", it shows the
Contact_name of other clients. I am sure I must have missed a simple

Arvin: I guess I could add a client fields in the attendee, but
normally the client is already implied by the fact that all attendees
are linked to a unique contact, that is linked to a unique client.
Wouldn't that be both redondant and create a triple key to enforce?


Pieter Wijnen

FYI, Please leave the original post when following the thread, it is
to go back and forth between the Q&A.

Select A.ContactID, A.Contact_Name
From tblContact A
Where A.Contact_Client=Forms!MainForm!Client_id

should work, but must be requeried when changing between clients as the
source in effect changes

ie something like

Private Sub Client_id_AfterUpdate()
End Sub


Private Sub Form_Current()
End If



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question