Daniel,
Some people use
ID = Dmax("IDField","tableName") + 1
I suppose you could also generate a very large random number.
Another approach is use a table with one row, storing the last used ID.
Open the table using a pessimistic lock (locking the record) and grab the
ID (put the recordset in edit mode), add one to it and then write it back
to the table (having moved the Last Used ID + 1 into a variable of
course).
This will stop 2 id's being created at exactly the same time (a small
possiblity).
You'll need some code to allow a second user's code to 'wait' until the
first user has finish, and then retry the number generation.
Yet another approach I've heard of is to use a table with a list of
usable numbers in it. As they are used, they are removed from the table
or marked as used (use a boolean or somesuch).
Also you could just use an autonumber if you are not worried about
potential gaps in your sequence, and concatenate the number in the field
that you display to the users.
Anyways here's a function I wrote to do this.
'*********** START CODE ***************
Const ERR_CANT_UPDATE = 3027
Const MIN_SECONDS_TO_ATTEMPT_UPDATE = 2
Public Function GetWorksNumber() As Integer
' Returns the next available number to use for a works project
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intReturn As Integer
Dim dteStartErrLoop As Date
On Error GoTo err_Handler
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLastWorksNumberUsed", dbOpenDynaset,
dbPessimistic)
With rs
If Not .EOF Then
.Edit 'lock the table
'if it's the first project for the year reset the counter to 1
If IsFirstProjectOfYear Then
intReturn = 1
Else
intReturn = Nz(!LastNumberUsed, 0) + 1
End If
!LastNumberUsed = intReturn
.Update
End If
End With
GetWorksNumber = intReturn
exit_Handler:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
On Error GoTo 0
Exit Function
err_Handler:
If Err.Number = ERR_CANT_UPDATE Then
'initialize counter if first instance
If CLng(dteStartErrLoop) = 0 Then
dteStartErrLoop = Now
End If
If DateDiff("s", dteStartErrLoop, Now) < MIN_SECONDS_TO_ATTEMPT_UPDATE
Then
Resume
Else
Err.Raise Err.Number, , "Error Generating Works Number"
End If
Else
Err.Raise Err.Number, , Err.Description
End If
Resume exit_Handler
End Function
'*********** END CODE ***************
HTH,
Peter
Quote:
> I have a question about generating ID's for my records.
> The ID has to due with patients in a health system. I
> would like the ID to be more or less like this:
> P03-0002
> 'P' + 'YY' + Incremental Number
> In VBA how do I access the last number so that I can
> increase the next one by one? What are the steps to
> generate your own ID's?
> Thanks
> Dan
Generating ID