I need a routine to generate a unique number for a 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 cannot use the autonumber Key feature for this since I MUST know the number before I add records. I must also be able to jump sequences in certain circumstances.

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.
 
Create another table with just one number field, containing the highest
number assigned so far.

Your code should then open this table with an exclusive lock, read the
number, increment as desired, save your record with this new number, and
then release the lock. The lock prevents any concurrency issues. If the
table is already locked when you try it, use the random wait'n'retry for a
fixed number of attempts.

If you are doing this on several tables, each one needs its own table, since
locking one central table that manages all the numbers for all the tables
gives far too many locking conflicts.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David said:
Due to reasons too complex to explain I need a routine to
generate a unique number for a key.

I cannot use the autonumber Key feature for this since I MUST know the
Click to expand...
number before I add records. I must also be able to jump sequences in
certain circumstances.
 
Search access help or Miscrosoft Site for GUID
This will give you a unique numbe
-----Original Message-----
Due to reasons too complex to explain I need a routine to
generate a unique number for a key.

I cannot use the autonumber Key feature for this since I
Click to expand...
MUST know the number before I add records. I must also be
able to jump sequences in certain circumstances.
 
I use this routine to generate a variety of numbers used for orders,
invoices, etc

You need a table called tblSystemValues with the following fields:
ID - primary key
Type - Human readable description
Prefix - any text you want added to the front of the number
Format - string like "0000" for a 4 digit numeric
Number - the next number to be grabbed whenever you call the routine
you can use this to set also the starting number for the sequence


Public Function GetNextID(ID As Long) As String
'Pass the ID from call
'1 = Quote
'2 = Sales order
'3 = Purchase order
'4 = Debtors Invoice
'5 = Creditors Invoice

'returns the next quote/order/invoice number with correct prefix as a string
'prefix, format and current numbers held in the tblSystemValues table

Dim mySQL As String

Dim Getnum As Long
Dim Prefix As String

Dim DigFormat As String




mySQL = "SELECT tblSystemValues.ID, tblSystemValues.Type,
tblSystemValues.Prefix, tblSystemValues.Format, tblSystemValues.Number"
mySQL = mySQL & " FROM tblSystemValues "
mySQL = mySQL & " WHERE (((tblSystemValues.ID)= " & ID & "));"


With CurrentDb.OpenRecordset(mySQL, , dbDenyRead) 'lock recordset for
reading while updating
'this ensures two users cant get the same number



If IsNull(!Prefix) Then
Prefix = ""
Else

Prefix = !Prefix 'set the quote prefix
End If


DigFormat = Nz(!Format, "") 'set the format




.Edit
!Number = !Number + 1 'update to next number
.Update
Getnum = !Number 'and get it


.Close

End With



GetNextID = Prefix & Format(Getnum, DigFormat)


End Function

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top