My database includes each company sales order in TblSalesHeader and TblSalesDetails. This is in 2 tables as 1 sales order may contain 10 sales lines. (10 products).
When I copy a previous order, to save on inputting time, I run a query which:
1. Copys and appends the old TblSalesHeader.OrderNumber to the new order number.
2. Runs a query to find the total amount of orders (which is the last sales order number used)
3. Copys and appends all the TblSalesDetails to the new order number.
My problems is that when the COPY query is running, someone else using the application sometimes starts a new order in between points 1 and 3. This means that when point 3 runs, we have 1 extra order number and so appends the 10 x OrderDetails to the wrong order number. Does this make sense?
What I need to do is stop anyone using a order number during the slit second that the COPY query is run.
Instead of appending the live tables immediately, try making temp tables, and appending all information to the temp tables. When you're completely finished with the order details, click on a command button that will append the info to the live tables.