Error 3341: The current field must match the join key (1 Viewer)

[AndrewS]

I'm coming up against this error, and really struggling to work it out. Googling reveals very little for this error, so it must be something really simple and stupid that I'm missing. Can anyone help?

The error: "The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value and then make the entry with the desired join key in the 'many-only' table."

I understand what that means, but can't find what's causing it.

For debugging (or trying to!), I have recreated a much simplified version of my database with just the relevant tables, query and forms (see attached). I've removed all VBA from this, so there are no distractions in that. The problem still occurs.

It is a main form and a subform. The main form shows the organisation, the subform shows people from that organisation who have registered for an event. The subform is based on a query

SELECT tbl_PeopleRegistrations.*, tbl_People.OrganisationID
FROM tbl_PeopleRegistrations INNER JOIN tbl_People ON tbl_PeopleRegistrations.PeopleID = tbl_People.PeopleID;

The forms are linked by ID on the main form and OrganisationID on the subform.


The error arises when I try to select a person attending. The only event that traps the error is the On Error event. When I debug using a breakpoint in that, [ID] on the master and [OrganisationID] on the child match.

Rather than me rambling on further, see the attached database (I'm using Access 2016 and the file is in *.accdb format)

 

[arnelgp]

you use Left Join on your query. Using Inner Join will not let you add new record, if either of the tables joined don't have the field value you are entering.

 

[CJ_London]

think you have your thinking a bit mixed up - if you want to only add people from the specified organisation, you need to modify the rowsource for the people combo to only include people from that organisation.

SELECT [tbl_People].[PeopleID], [tbl_People].[FullName] 
FROM tbl_People 
WHERE OrganisationID=[Parent].[ID] 
ORDER BY [FullName];

you also need a control for OrganisationID on your subform for the subform link to work (which is causing the error)

 

[arnelgp]

you move the OrganisationID to tbl_PeopleRegistrations.
then add the new OrganisationID to your query and remove the old one.
remove the tbl_people table from the query, not needed.



your organization textbox now changed to combobox.

 

[AndrewS]

you move the OrganisationID to tbl_PeopleRegistrations.
then add the new OrganisationID to your query and remove the old one.
remove the tbl_people table from the query, not needed.

Thanks arnelgp. Yes!!! That's got it!

Adding Organisation to tbl_PeopleRegistrations and using that to link parent & child forms has fixed it.

Thanks also CJ London: you're quite right about the people combo rowsource - I set that using VBA in the parent form's On Current, and in the people combo On Change. I do it that way as I use the flivesearch function for search as you type on the combo (as there are thousands of records in tbl_people.
You're right about needing the linked field on the child form when the child is in datasheet view. Though it's not necessary if it's displayed as continuous forms.

FWIW, my original thinking was that as OrganisationID was already part of tbl_People, having it in tbl_PeopleRegistrations as well, would have been redundant. But that was obviously a step too far for Access.