Custom ID Field

[Larry06Green]

I found this function code in a response from Arvin Meyer. As suggested, I
created the table called tblNextNumber and the text field called JobNumber. I
created the module called GetNextJobNumber (see below). How do I get this
calculated ID value into my new record form?

Option Compare Database

Public Function GetNextJobNumber() As Long
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select JobNumber From tblNextNumber"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
GetNextJobNumber = !JobNumber
GetNextJobNumber = "MK-" & Format(GetNextJobNumber, "00000")
..Edit
!JobNumber = !JobNumber + 1
..Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

 

[Marshall Barton]

I found this function code in a response from Arvin Meyer. As suggested, I
created the table called tblNextNumber and the text field called JobNumber. I
created the module called GetNextJobNumber (see below). How do I get this
calculated ID value into my new record form?

Option Compare Database

Public Function GetNextJobNumber() As Long
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select JobNumber From tblNextNumber"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
GetNextJobNumber = !JobNumber
GetNextJobNumber = "MK-" & Format(GetNextJobNumber, "00000")
.Edit
!JobNumber = !JobNumber + 1
.Update
End With

Us a line of code in the form's BeforeUpdate event:

Me.jobnumberfield = GetNextJobNumber

 

[Larry06Green]

I added the line of code you suggested and things are working fine. With this
form of autonumbering work in a multi-user environment? If I open the form to
enter a project and another users opens the form to enter a project, will
different job numbers be assigned?

 

[Ron2006]

Yes.

The described function reaches out, grabs a number, increments the
table, and then releases the table for the next user to do the same.

I had to implement the same type of logic for an application with
approximately 20 concurrent users, and have not had any problem with
duplicate numbers since I installed that code. In my case I had to
absolutely disallow duplicate numbers since I was creating a directory
that had that number as part of the name to enable storing documents
related to that record/number. This processed has stopped the
occurance of duplicated order numbers and the mixing of related
documents.

Ron

 

[Marshall Barton]

Using a recordset that way in the form's BeforeUpdate event
is somewhere around 99.9999% safe in a heavily used multi
user scenario. Of course the % will vary a little depending
on how often and how many users are adding new records at
exactly the same time. Note that using any other event can
practically guarantee that two users will get the same
number often enough to be a serious problem.

To be 100% safe, you would have to open the recordset
exclusively, add a bunch of code to trap the situation where
the table was open by another user, add a small random code
sleep interval and retry the whole process. Very few people
find the miniscule risk worth the additional code
complexity.