Need routine to generate unique number for key

[David]

Due to reasons too complex to explain I need a routine to generate a unique number for a key
I envision a table with one row 'next_number' which will be accessed with exclusive lock and updated before lock is released
Does anyone have reliable working code so I dont have to reinvent the wheel
Also what happens if two users hit this routine at the same time, do I need a short 'wait and retry' loop
I'm using ADO access in Access 2000. This is a multi-user system.

 

[Chris Nebinger]

You have just defined an Autonumber. What do you want to
do differently?


Chris Nebinger

-----Original Message-----
Due to reasons too complex to explain I need a routine to

generate a unique number for a key.

I envision a table with one row 'next_number' which will

be accessed with exclusive lock and updated before lock is
released.

Does anyone have reliable working code so I dont have to reinvent the wheel?
Also what happens if two users hit this routine at the

same time, do I need a short 'wait and retry' loop.

 

[David]

I can't use an autonumber, I have to be in control of the number generation. I must have the number BEFORE I add records. Reasons too complex to explain.

 

[tasha]

Try this. This is what I did with same problem

Field is the field that I want the unique number in
Anotherfield a unique field for this record. I use a field that gets the date and time automatically. If you have a one field primary key, you could use that

= DMax("[field]", "

") + 1 put this in the default value property of the field on the form( I have a data entry form this is connected to

Put this code in the Before Update event for the form

Private Sub Form_BeforeUpdate(Cancel As Integer
Dim x As Varian
Dim y As Varian


x = DLookup("[field]", "

", "[field] =" & Me![field]
y = DLookup("[anotherfield]", "

", "[field] =" & Me![field]
MsgBox "the value of x is " & x & " y = " & y ' use to check values of x and y for debu

'this condition is in case another user opened the same data entry form at the same time
If IsNull(x) = True The
' do nothing
Els
'this condition is will keep field from changing in case your data person goes to a new record and comes back to fix something on this recor
If y = Me![anotherfield] Then
Els
'this will increment the field if the current value already exists in the table
Me![field] = DMax("[field]", "

") +
End I
End I
MsgBox "field = " & Me![field] 'I use for debu

End Su

If you use the date time field like I do you may still have problems if two users open form in same second

Please reply back and let me know if this helped or if you have any problems