Unique ID - either user entered or if not then use autonumber.

[leonv8]

Using ASP web pages with MS Access 2000.
I have an Order table that requires a unique ID. Currently the insert uses (MAX + 1) to obtain next value but I recognise that this may fail occasionally because of multiple users. I can't use autonumber as the ID datatype because the user may choose to enter a specific value depending on their need (validated as unique of course).

How can I use the number entered into a textfield, or an autonumber in a different field or table (if user does not enter a value) to populate the ID field.

My main problem is how to take value from a field or table, increment it for next time and then insert it along with the other fields to the Order table.

Any ideas will be much appreciated.

leonv8

 

[BNPMike]

Firstly this has been discussed before so you may want to view previous material.

The general idea is that you need to obtain a number which nobody else can also obtain. You therefore need to lock that number.

You can do that two ways. Option 1 is to lock the table concerned when you start the transaction. You can then add your new record and ensure you will succeed when you save it. Most likely however this will cause multi-user problems so Option 2 is to create a short-term lock. People generally have a number table. You lock that, take your number then use that number for your main transaction. If everyone is doing that then they only come into contention if they are also trying to add a new number.

Typically you take your new number then release the lock. In the event your main transaction does not complete you have some process which deletes the number from the number table so that it can be potentially re-used but in any event doesn't get clogged up with erroneous entries.

Note your other option is change-verify where you generate a next number (max(id) + 1) and if when you save it Access rejects it as a duplicate (somebody beat you to it) you have to try again with a new (max(id) + 1). mike.stephens@bnpparibas.com