How do I customize a job number?

[Anthony Moore]

I am trying to autopopulate a field (JobNumber) in a table (JobDatabase). This field is a primary key for the table and I want to force it to be numbered per our job numbering system. Also I would like it to automatically assign sequentially when new records are created.

Our job numbering system is a hybrid which is in the following format "MMYY###".

For example:
New record is created in January 2011 and is the 4th one created for the month. The job # would be 0111004.

The next record created in January of 2011 would be numbered 0111005.

The first record for the following month (February 2011) would be 0211001.

How would I do this in MS Access/VBA?

[colintis]

Hi Anthony,

The below part of code should work for you

Dim strDate As String
Dim RecCnt As Integer
Dim RecNbr As String
Dim strRec As String
 
'Get current month & year
strDate = Month(Date) & Year(Date)
 
'Check if there's any other current month records
RecCnt = DCount("<Your Record ID>" _
                , "<Your Table>" _
                , "<Your Record ID> LIKE '" & strDate & "*'")
 
If RecCnt > 0 Then
    'Get the highest record# of the month
    RecNbr = DMax("<Your Record ID>" _
                  , "<Your Table>" _
                  , "<Your Record ID> LIKE '" & strDate & "*'")
Else
    'First record of the month
    RecNbr = strDate & "000"
End If
 
'Now we have the new record number
strRec = CInt(RecNbr) + 1

I would recommend to have that record number in the format of YYMM### instead of MMYY## for better searching purpose.