This is exactly as is in my project. The function is called from several places that can initiate a new 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
Then one example of calling the function from a button Click event:
Dim strLabNum As String
strLabNum = NewSample
Me.tbxLABNUM = strLabNum
DoCmd.OpenForm "Sample", acNormal, , "LabNum='" & strLabNum & "'", , , strLabNum & "," & "Login"