Subform with two keys

[Charles]

Hello

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

Table tblClient
Client_id
Client_name

Table tblContact
Contact_id
Contact_name
Contact_client

Table tblMeeting
Meeting_id
Meeting_client

Table tblAttendee
Attendee_id
Attendee_meeting
Attendee_contact

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
Charles

 

[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 &
Attendee_meeting

HTH

Pieter

 

[Charles]

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
parameter.

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?

Charles

 

[Pieter Wijnen]

FYI, Please leave the original post when following the thread, it is
cumbersome
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()
Me.SubForm.Form.Attendee_id.requery
End Sub

And

Private Sub Form_Current()
Client_id_AfterUpdate
End If

HTH

Pieter