Firstly I'd suggest that you have separate columns for the date and serial
number, the latter as the primary key. It’s a trivial task to concatenate
them into one computed column in a query, or a computed control in form or
report with an expression such as:
Format([DateEntered],"mmddyy") & Format([CustomerNumber],"000")
To automatically insert the current date into the DateEntered column when a
row is inserted into the table set its DefaultValue property to Date(). To
compute the next CustomerNumber in sequence data must be entered via a form.
In a single user environment simply put:
Me.[CustomerNumber] = Nz(DMax(""CustomerNumber", "Customers"),0)+1
in the form's BeforeInsert event procedure. In a multi-user environment,
however, this can cause conflicts if two or more users are adding a new
customer simultaneously. Roger Carlson has a simple solution to this at:
http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb
or there's a slightly more complex one of mine at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps
which also allows for the next number to be used in the sequence to be
'seeded'.
I've assumed above that the CustomerNumber values will be distinct. If,
however, you want a separate sequence per DateEntered value, starting at 1
for each date, then you'd need to make the primary key a composite one of
both columns, and compute the next number for the date in question. In a
single user environment the code would be:
Dim strCriteria As String
srCriteria = "DateEntered = #" & Format(Me.DateEntered,"yyyy-mm-dd") & "#"
Me.[CustomerNumber] = _
Nz(DMax(""CustomerNumber", "Customers", strCriteria),0)+1
In a multi user environment, using my method the function to get the next
number would be:
Public Function GetNextNumberForDate(strCounterDb As String, dtmDate As Date)
As Long
' Returns next number in sequence for specified date
' if external database can be opened and number obtained.
' Returns zero if unable to get next number.
Const NOCURRENTRECORD As Integer = 3021
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim n As Integer, I As Integer, intInterval As Integer
Dim strSQL As String
strSQL = "SELECT * FROM tblCounter WHERE DateEntered = #" & _
Format(dtmDate,"yyyy-mm-dd") & #""
' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
Err.Clear
Set dbs = OpenDatabase(strCounterDb, True)
If Err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False
If Err <> 0 Then
GetNextNumberForDate = 0
Exit Function
End If
Err.Clear
Set rst = dbs.OpenRecordset(strSQL)
With rst
.Edit
' insert new row if no existing record for this date
If Err = NOCURRENTRECORD Then
.AddNew
!DateEntered = dtmDate
!NextNumber = 1
.Update
GetNextNumberForDate = 1
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNumberForDate = rst!NextNumber
End If
.Close
End With
End Function
You'd then call it in the form's BeforeInsert event procedure with:
Dim strCounterDb As String, lngID As Long
strCounterDb = <get path to external counter database form somewhere>
lngID = GetNextNumberForDate(strCounterDb, Me!DateEntered)
If lngID > 0 Then
Me!CustomerNumber = lngID
Else
MsgBox "Unable to get customer number at present.", vbInformation,
"Error"
Me.Undo
End If
Ken Sheridan
Stafford, England
Rather than use Auto Number I would like Access to automatically generate
unique customer ID's as the primary field in a customer table I'm designing.
The format for these customer ID's is: the date they were entered as a
customer + a sequentially numbered three digit extension (001, 002...999).
The final format is: MMDDYYXXX.
How is this accomplished?
