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
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.
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.
