Increment ID# MS Access VBA

Question

I need to be able to create a unique ID# which uses the date, selected in a textbox. And when there is more than one record with that month/year I need it to count the number of records with that month and year and create an ID incremented by one. So if there are 4 cases for the month of October 2013, I need it to create the id's as follows:

• CEF-1013-1
• CEF-1013-2
• CEF-1013-3
• CEF-1013-4

If have the following code:

Private Sub Text0_AfterUpdate()
    'Creates a custom case number based on date and how many have been entered in that month/year
    Dim caseNum As String
    Dim caseCount As Integer
    caseNum = Format(Forms!frmEnterCase!Text0, "mmyy")
    caseCount = DCount("CaseID", "case", Format(Table!Case!CaseID, "mmyy") = caseNum)
    caseNum = "CEF-" & caseNum & "-" & (caseCount + 1)
    Forms!frmEnterCase!Text31 = caseNum

End Sub

Answer 1

For what it's worth, I would recommend that you keep the sequence number you derive in a separate numeric field, something like this:

CasePK  CaseDate    CaseMonthSeq
------  ----------  ------------
   101  2013-10-01             1

That way you can streamline the process to derive new sequence numbers, and you can easily build the "CaseID" string from its constituent parts. (That is, it's always easier to glue strings together than it is to split them apart.)

The following test code illustrates how you could derive the sequence number when you insert the record into the table. Because the derivation is done inside the INSERT statement it should be done within an implicit transaction and therefore be suitable for a multi-user environment

Sub CaseSeqTest()
Dim cdb As DAO.Database, SQL As String
Dim dtCaseDate As Date
dtCaseDate = DateSerial(2013, 10, 3)  ' sample date for testing
Set cdb = CurrentDb
SQL = _
        "INSERT INTO Cases (" & _
                "CaseDate, " & _
                "CaseMonthSeq " & _
            ") VALUES (" & _
                "#" & Format(dtCaseDate, "yyyy-mm-dd") & "#, " & _
                "Nz(DMax(""CaseMonthSeq"", ""Cases"", ""Format(CaseDate, """"yymm"""") = """"" & Format(dtCaseDate, "yymm") & """""""), 0) + 1 " & _
            ")"
Debug.Print SQL
cdb.Execute SQL, dbFailOnError
Set cdb = Nothing
End Sub

The Debug.Print statement simply prints the SQL command to be executed, in this case...

INSERT INTO Cases (CaseDate, CaseMonthSeq ) VALUES (#2013-10-03#, Nz(DMax("CaseMonthSeq", "Cases", "Format(CaseDate, ""yymm"") = ""1310"""), 0) + 1 )

...and the record is inserted into the table as follows:

CasePK  CaseDate    CaseMonthSeq
------  ----------  ------------
   101  2013-10-01             1
   102  2013-10-03             2

If you want to display the CaseID then you can always piece it together "on the fly", like this...

SELECT
    CasePK,
    CaseDate,
    "CEF-" & Format(CaseDate, "yymm") & "-" & CaseMonthSeq AS CaseID
FROM Cases

...returning

CasePK  CaseDate    CaseID    
------  ----------  ----------
   101  2013-10-01  CEF-1310-1
   102  2013-10-03  CEF-1310-2

...or, since you are using Access 2010 (or later) you could alter the [Cases] table to create a [CaseID] as a Calculated field using the expression

"CEF-" & Right(Year([CaseDate]),2) & IIf(Month([CaseDate])>9,"","0") & Month([CaseDate]) & "-" & [CaseMonthSeq]

so you could just retrieve [CaseID] directly and wouldn't keep having to re-create the logic to assemble the CaseID every time you wanted to use it in a query or report.

Answer 2

You should be able to do something like this:

Private Sub Text0_AfterUpdate()
    Forms!frmEnterCase!Text31 = GetNextCaseNum()
End Sub

Public Function GetNextCaseNum() As String
    Dim rs as DAO.Recordset, sSQL as String
    sSQL = "SELECT TOP 1 CaseID FROM case WHERE CaseID LIKE 'CEF-" & Format(Date, "mmyy") & "-*' ORDER BY CaseID DESC"
    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    If Not (rs.EOF and rs.BOF) Then
        GetNextCaseNum = "CEF-" & Format(Date, "mmyy") & Format(Cstr(Clng(Right(rs("CaseID"), 2))+1), "00")
    Else
        GetNextCaseNum = "CEF-" & Format(Date, "mmyy") & "-01"
    End If
    rs.close
    Set rs = Nothing
End Function

Note that I didn't test the code. It might need some tweaking/debugging. The basic concept still stands.

It's best to make functions for fetching/creating ID's in scenarios like this.

Also, I might mention that I would normally assign a meaningless Autonumber field as the Primary Key for a table like this, and then possible still use a meaningful CaseCode field similar to what you're doing here. Numeric Primary Keys perform better. While you do see it a lot, it's often considered a bad design practice to use meaningful Primary Key fields.