Both can be done, but you would probably prefer option ‘b’.
In summary you could do it modifying your entry form to add a BeforeInsert event and put some code to get the last code for the current date to generate and assign the new code. But you might want to store the last incident number in a table so you don’t have to look for the ‘max’ number every time you insert a new record.
I ran a small test, and this is what I did. I’m not an Access expert so there is probably a better or more efficient way to do it, but I think this should at least get you started.
Assumming that your incidents are being stored in a table called yourTable1 and you are going to store the last error number for the current day in a table called yourTable2, this code should work:
(A reference to the Microsoft DAO Object library is needed to run this code)
<pre>Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim no As Integer
Set rs = CurrentDb.OpenRecordset(“SELECT [date],errno FROM yourTable2”, dbOpenDynaset, dbSeeChanges)
If rs.BOF And rs.EOF Then
MsgBox “yourTable2 table not found”
If rs![date] <> Date Then
no = 1
no = rs![errno] + 1
= Format(Date, “yyyy/mm/dd”) & “-” & no
And after inserting the record you need to update the number in yourTable2, so this is the code for the AfterInsert event:
<pre>Private Sub Form_AfterInsert()
CurrentDb.Execute “UPDATE yourTable2 set [date] = #” & Format(Date, “dd-mmm-yyyy”) & “#, errno = ” & Val(Mid(code, InStr(code, “-“) + 1))
Feel free to ask for clarification if needed.