Error message on form - issue with junction table

[Montez659]

I keep getting an error message in a subform that the data is stored into a junction table. Here is the error message, and I will explain further below:

The Microsoft Office Access database engine cannot find a record in the table 'Contacts' with key matching field(s) 'whoAdded'.

Contacts is my list of, well, Contacts and whoAdded is an expression in a query contained within the form that concatenates the first and last names.

This error comes about when adding a new record to the junction table in the subform. The subform is in datasheet mode and the user inputs a few fairly simple fields, basically the date, routing info (i.e., Received, Sent, Emailed), routing where (i.e., to SR, from SR, to PR, from PR, etc.) and the person routing it (which is a combobox for whoAdded).

So it basically seems that the inputs will not allow a null value in the whoAdded field (and a few others). If the user clicks off of that record (that particular line in the datasheet mode) without having selected their name from the combobox, it triggers the above error message.

So why is it forcing me to make selection? I understand the query not finding a NULL in the table, but I can't seem to ever remember that dissallowing a NULL before.

As always, any help/wisdom is most appreciated!

 

[boblarson]

What field/fields do you have set for the master/child properties? Also, are you sure you don't have the junction table in the record source for the main form? It should not be there at all.

 

[Montez659]

I have trackingID, which is the PK autonumber from the main table (tblTracking) and is a FK for the junction table (JOINEDtblRoutedTracking). Both the Link Master and Link Child have trackingID listed.

tblTracking is the Record Source for the main form.

 

[Montez659]

I also wrote incorrectly that whoAdded was an expression. FullName is the actual expression contained in the query, whoAdded is the field name.

 

[Montez659]

Just a bit more Information. This is Error 3101 and the help screen states the following:

In a one-to-many relationship, you entered data on the "many" side for which there is no matching record on the "one" side. For example, this error occurs if you join a Customers table and Orders table on a CustomerID field, and then add an order using a CustomerID that does not exist in the Customers table.

Yet, this description is not entirley accurate, because the matching record on the "one" side has been created and saved (this is ensured in the before insert which dissallows edits to the subform until after the main record has been inserted).

Just can't figure it out.

 

[boblarson]

It would be easiest for me to be able to look at a copy of the actual database. Being able to do that should yield much quicker results.

 

[Montez659]

I may be able to do that, but it will be Monday before I get back to work. Course, I will have to clean it up too - too many tax id's in there!

 

[Montez659]

Bob, I figured it out.

I had two extra tables with Inner Joins set as part of the record source for the Sub Form. There was no reason why they had to be there (but don't ask me why I put them there!), so as soon as I removed them the error message went away.

Thanks for the help!