I have a DB which contains (among other things..) orders of items, and it works in a multi user environment. I'll try to be specific about my problems:
There is a list of products, and upon selecting one of them with a button, 2 things happen - a new order is opened, and the product already assigned to it.
When pressing the button, I use "INSERT... VALUES..." using db.execute to open the new order in "tblOrder". The order number is the PK, and it is an autonumbering field, so when I use the "INSERT" command, I don't insert any value to this field, it is automatically generated.
The first problem: when 2 users open an order at the same time - the 1st will instantly get the order screen while the 2nd will have a 2-3 seconds delay. why is that ?
The second and more important problem for me: After opening the order, I want to insert the selected product to "tblOrderItems", and of course I need the order number. Is there a way to "catch" the auto generated number of the order number while it is generated ? or I have to run a query on "tblOrder" searching for the proper record and pull out the number from there ? what is the most efficient way to do this ?
comment: I tried to control the order numbers manually but since it is a multi user environment and there are network delays - duplicate values were created.
Thanks to all the helpers.