pull out an autonumber

hi
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.

Comments

  • : hi
    : 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.
    :
    :
    Why not let the forms and sub-forms you build control the records that are added automatically? Access has already figured this stuff out for you if you design your tables and forms correctly. If I am understanding you correctly, it doesn't sound like you need any code at all to do what you're describing.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion