VBA is the only way to automate custom generation of unique identifier. I can give you an example from my project. You will have to learn VBA programming to develop procedure tailored for your circumstances. The risk of using code to generate identifiers is that with multiple simultaneous users, it's possible each could generate the same value. In my procedure the new number is created and the record saved immediately. This reduces chance of duplication. Your situation is complicated because you are sequencing two fields but the technique demonstrated in my procedure is still applicable. Search the table for last values and increment. This can be done when the record is initiated as in my procedure or after all data is entered and then generate the IDs and save record.
Code:
Public Function NewSample() As String
Dim strLabNum As String
DoCmd.SetWarnings False
'search for aborted lab number and use that record, else if none then create new record
strLabNum = Nz(DLookup("LabNum", "Submit", "IsNull(DateEnter)"), "")
If strLabNum <> "" Then
DoCmd.RunSQL "UPDATE Submit SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
Else
strLabNum = Nz(DMax("LabNum", "Submit"), "")
If strLabNum = "" Then
'this accommodates very first generated number of blank database
strLabNum = Year(Date) & "A-0001"
Else
'this accommodates change in year
If Left(strLabNum, 4) = CStr(Year(Date)) Then
strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
Else
strLabNum = Year(Date) & "A-0001"
End If
End If
DoCmd.RunSQL "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.tbxUser & "')"
End If
Form_SampleManagement.ctrSampleList.Requery
NewSample = strLabNum
DoCmd.SetWarnings True
End Function