I was given the following code to enable me to prefix my primary key with "ACS-" and to start the numbering at "1500" but now I'm discovering that I cannot advance beyond the record number "9999" (ie: "ACS-9999"). Any ideas why?
Private Sub Form_BeforeInsert(Cancel As Integer)
End Sub
Dim stemp As Variant
stemp = Nz(DMax("strAcsFileNo", "tblClaim"), 0)
If stemp = 0 Then
Me.strAcsFileNo = stemp
stemp = Nz(DMax("strAcsFileNo", "tblClaim"), 0)
If stemp = 0 Then
stemp = "ACS-1500"
Else
stemp = Mid(stemp, 5)
stemp = stemp + 1
stemp = "ACS-" & stemp
End Ifstemp = stemp + 1
stemp = "ACS-" & stemp
Me.strAcsFileNo = stemp
End Sub