Need routine to generate unique number for key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
Click to expand...
generate a unique number for a key.
I envision a table with one row 'next_number' which will
Click to expand...
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
Click to expand...
same time, do I need a short 'wait and retry' loop.
 
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.
 
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
 
Back
Top