Generating Unique ID

There's no built-in autogenerator for generating a "custom autonumber", so you have to write a getNextNumber() function yourself. Basically, it opens a query (or uses a DLookup() of some sort) and finds the largest autonumber for your specific category, then generates the next autonumber.

Here's a (poorly-coded) example from my distant past (please ignore nasty parts and concentrate on the good parts):

As with most of life, it is really conditional, bbut if the 'app' is multiuser, you cannot (reliably) use DLookUp (or other references to the value in the "active" recordset. This is because of the possability (PROBABILITY - Certainity) that at some point a duplicate key will be generated.

An example of one approach to generating unique values is in faq700-184. It DOES NOT follow your pattern, and should ONLY be used as a resource to study. The key elements are the fact that the 'current' value is stored in a seperate recordset (table) which is minimal in hte number of fields and records, the locking of the table when in use (to get the next value) and the provision for a user (calling procedure) to re-try the access to the table.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

I have 2 versions of my AutoNumber routine, one for DAO and another for ADO.
They work in the same way as the FAQ mentioned, but they create the required table if it does not exist and can be used to keep track of multiple counters.

Copy whichever version you need into your database and call it with:
GetNextAutoNumber("ID&quot where ID is the name of the autonumber you want to increment. You can have any number of autonumbers in the same table.

hth
Ben

DAO version:
Function GetNextAutoNumber(strAutoNumber As String) As Long
'---------------------------------------------------------------------------------------
' Procedure : GetNextAutoNumber
' DateTime : 18/11/03 16:15
' Author : 721116
' Purpose : Creates new autonumber, without bothering Access Autonumber.
' Uses DAO
'---------------------------------------------------------------------------------------
'
Dim sTable As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intRetry As Integer
Dim lngNum As Long

On Error GoTo GetNextAutoNumber_Error


sTable = "tblAutoNumber"

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM " & sTable & " WHERE AutoNumberName='" & strAutoNumber & "'", dbOpenDynaset, dbDenyRead Or dbDenyWrite, dbPessimistic) 'Open table with the specified counter
rst.MoveFirst
lngNum = rst!AutoNumber + 1
rst.Edit
rst!AutoNumber = lngNum
'move the autonumber on.
'if another user is trying to get it, then an error is called.
'The error handler tries 100 times to get a number.
rst.Update
GetNextAutoNumber = lngNum
rst.Close
Set rst = Nothing
Set db = Nothing

GetNextAutoNumber_Exit:
On Error Resume Next
Exit Function

GetNextAutoNumber_Error:
Select Case Err
Case 3188 'Table is locked, try 100 times until it is freed again.
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, &quot;Another user editing this number&quot;
Resume GetNextAutoNumber_Exit
End If
Case 3021 'No autonumbers with that name stored yet. Start count at 1
rst.AddNew
rst!Autonumbername = strAutoNumber
rst!AutoNumber = 0
rst.Update
Resume
Case 3078
db.Execute &quot;CREATE TABLE &quot; & sTable & &quot; (AutoNumberName varchar(50),AutoNumber LONG)&quot;
Resume
Case Else 'Something really bad has happened!
MsgBox &quot;Error &quot; & Err.Number & &quot; (&quot; & Err.Description & &quot in Function GetNextAutoNumber of Module mdlMisc at line &quot; & Erl(), vbOKOnly, &quot;ImpactComms&quot;
Resume GetNextAutoNumber_Exit
Resume 'useful for debugging. Right click on here and tell VBA to resume execution at that line. You will then be returned to the line which caused the error.
End Select
End Function


ADO Version:
Function GetNextAutoNumber(strAutoNumber As String) As Long
'---------------------------------------------------------------------------------------
' Procedure : GetNextAutoNumber
' DateTime : 24/09/03 16:31
' Author : 721116
' Purpose : Creates new autonumber, without bothering Access Autonumber.
'---------------------------------------------------------------------------------------
'
Dim sTable As String
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim intRetry As Integer
Dim lngNum As Long

10 On Error GoTo GetNextAutoNumber_Error


20 sTable = &quot;tblAutoNumber&quot;

30 Set conn = CurrentProject.Connection

40 rst.Open &quot;SELECT * FROM &quot; & sTable & &quot; WHERE AutoNumberName='&quot; & strAutoNumber & &quot;'&quot;, conn, adOpenDynamic, adLockPessimistic 'Open table with the specified counter

50 lngNum = rst!AutoNumber + 1
60 rst!AutoNumber = lngNum
'move the autonumber on.
'if another user is trying to get it, then an error is called.
'The error handler tries 100 times to get a number.
70 rst.Update
80 GetNextAutoNumber = lngNum
90 rst.Close
100 Set rst = Nothing
110 conn.Close
120 Set conn = Nothing

GetNextAutoNumber_Exit:
130 On Error Resume Next
140 Exit Function

GetNextAutoNumber_Error:
150 Select Case Err
Case 3188 'Table is locked, try 100 times until it is freed again.
160 intRetry = intRetry + 1
170 If intRetry < 100 Then
180 Resume
190 Else 'Time out retries
200 MsgBox Error$, 48, &quot;Another user editing this number&quot;
210 Resume GetNextAutoNumber_Exit
220 End If
230 Case 3021 'No autonumbers with that name stored yet. Start count at 1
240 rst.AddNew
250 rst!Autonumbername = strAutoNumber
260 rst!AutoNumber = 0
270 Resume
280 Case -2147217865 'Table does not exist
290 conn.Execute &quot;CREATE TABLE &quot; & sTable & &quot; (AutoNumberName varchar(50),AutoNumber LONG)&quot;
300 Resume
310 Case Else 'Something really bad has happened!
320 MsgBox &quot;Error &quot; & Err.Number & &quot; (&quot; & Err.Description & &quot in Function GetNextAutoNumber of Module mdlCSWords at line &quot; & Erl(), vbOKOnly, &quot;ImpactXP&quot;
330 Resume GetNextAutoNumber_Exit
340 Resume 'useful for debugging. Right click on here and tell VBA to resume execution at that line. You will then be returned to the line which caused the error.
350 End Select
End Function


----------------------------------------------
Ben O'Hara &quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------