Access database engine cannot find a record

[jerem]

Hi,



I am using the Goods sample database from Microsoft. I don't know what I did but I suddenly get the message:

"The Microsoft Access database engine cannot find a record in the table "Customers" with key field(s) "CustomerID".

This only happens when I try to create a new Customer Order. The error message shows when the OrderDetail form opens. If I choose an existing order, the problem doesn't arise.

I have checked many threads on various forums but nothing seems to help.

Thanks in advance for your help!

Cross post: http://www.utteraccess.com/forum/Acc...-t1995964.html
and http://www.access-programmers.co.uk/...d.php?t=236619

[June7]

Need to review code. If you want to provide db, follow instructions at bottom of my post.

Step debug. Review link at bottom of my post for guidance on debugging techniques.

[jerem]

Need to review code. If you want to provide db, follow instructions at bottom of my post.

Step debug. Review link at bottom of my post for guidance on debugging techniques.

Here you are...

Goods.zip

[June7]

Problem appears to be the INNER join of RecordSource for OrderDetail form. INNER join requires existing records in both tables. Change the query to:

SELECT Orders.*, Customers.PayTerms FROM Customers RIGHT JOIN Orders ON Customers.ID = Orders.CustomerID;

[jerem]

Problem appears to be the INNER join of RecordSource for OrderDetail form. INNER join requires existing records in both tables. Change the query to:

SELECT Orders.*, Customers.PayTerms FROM Customers RIGHT JOIN Orders ON Customers.ID = Orders.CustomerID;

It's working! Thanks a lot! I will revise the difference between INNER JOIN and RIGHT JOINT.

In the meantime, I have another question regarding that database. Not sure if I can go on with this thread or should start a new one... For the moment, the tool is invoicing per order, but I would like to build the structure so that per item invoicing is possible. For example one order would have 3 invoices. The user should be able to select items to invoice and change quantities (from an invoicing form that would link to the order). The way I was contemplating that is with a new query or a buffer table but I am not yet sure on the way to build the whole thing. Any advice?

Thank you!

[June7]

Possibly control this in report set up as invoice document. Design report to force new page after each item. Good topic for a new thread when you have specific issues with report design.

Discretionary selection of items to invoice requires applying filter criteria. Maybe a Yes/No field in table. Invoice items checked yes?

[jerem]

Ok thanks! I will start a new thread with that then.