I have an order entry system written in Access with several tables used for customer information, product info, order status, etc. Recently we added a new table for multiple customer email addresses (original setup allowed for one email address per customer and that field was in the customer table). The new table is setup as a one-to-many type relationship:
· Customer1 – email1
· Customer1 – email2
· Customer1 – email3
· Customer2 – email1
· Customer2 – email2
· ETC
We added a new combo box field on the main form with a row source defined:
The control source (FullName) is the field in the orders table containing the email address for that specific order. The select statement uses a field from the main customer order form as the key for the query into the new email table.
This works for the first new order entered into the system after the initial form start (or restart) however for all subsequent orders the field contains the email addresses from the first order. It is as if the query maintains the results from the first entry throughout the session. If we close the screen and re-open the order screen it will work fine once…
I have tried every permutation of query I can think of with the same results… What am I doing wrong?