Data Entry combo box not allowed to add record

[AlexaS]

Ok - I have a main form with two subforms that pull fields from a single table (Details). On the Details table is a field called "ShippedToCustomer" which is linked to the Customer Lookup table (Lookup 1:many to Details). On the subforms, this is a combobox whose control source is "ShippedToCustomer" and who's row source is:

SELECT [CUSTOMER Lookup].ID, [CUSTOMER Lookup].CusName
FROM [CUSTOMER Lookup];

When in the subform, I can see the customer I want to add in the drop down box. After I select the appropriate customer and try to save the record (either using the save button in Access or the save command button I created), I receive the message: "You cannot add or change a record because a related record is required in table 'CUSTOMER Lookup'. I thought it might be because this was a new customer that I had added, but it isn't working with another one that was there from the beginning either. I checked the table and the customer information exists. Unfortunately this is still a relatively new database, so I have used this functionality before but only once. It worked then, and I'm not sure what could have changed to make it not work now.

When I try to change the SQL statement to not include the ID (since CusName is the primary key), and test the SQL statement it returns results but the combo box is coming in blank. I also was able to input the customer name directly into the table, but it doesn't display on the subform when I look at the entry after the fact. And resaving the entry overwrites what was input on the table.

I'm out of ideas. Any help would be much appreciated, thanks!

 

[AlexaS]

So I don't know if anyone else has this problem, but just in case, here's what I finally did to try and fix the solution. I don't know if it's permanent but it seems to work for now:

• On the Customer Lookup table, I deleted the AutoNumber field. It was unnecessary as the Customer Name is the primary key.

• I deleted the combo box on the subforms, copy and pasted the plant combo box that was working correctly and changed the control and row source to be for the customer instead of the plant.

I did need to adjust the control/row source properties once again after exiting out of the form (and saving changes but apparently that bit didn't save properly). It works correctly now and writes to the Details table properly. I guess this means there was corruption on those two subforms? I just don't understand why it would be incorrect for one combo box but correct for another?