neuroman9999
Member
- Local time
- Today, 07:27
- Joined
- Aug 17, 2020
- Messages
- 827
can anyone tell me if what I've done in this code (on a DIRTY event behind a form) can be transformed into a field mask in an actual table? I don't see it.
KapilaraID ends up being the following if it's the first record in the table:
C2020-0001
KapilaraID ends up being the following if it's the 10TH record in the table:
C2020-0010
and KapilaraID ends up being the following if it's the 100TH record in the table:
C2020-0100
and so on....until it reaches 9999.
PHP:
If Me.NewRecord Then
Dim strLastNum As String
Dim strYear As String
Dim strNewNum As String
Dim strLastIdent As String
Dim strNewIdent As String
Dim strOldNum As String
If DCount("KapilaraID", "tblKapilare") = 0 Then
Me.KapilaraID = "C" & CStr(Year(Date)) & "-0001"
Else
'check to see if the last record in the parent table has a year that is different than today's year.
'if it is, we need to reset the identifier's mask format
strLastIdent = DLookup("KapilaraID", "qryMaxID")
strLastNum = Right(strLastIdent, 4)
strOldNum = IIf(Left(strLastNum, 3) = "000", Right(strLastNum, 1), _
IIf(Left(strLastNum, 2) = "00", Right(strLastNum, 2), _
IIf(Left(strLastNum, 1) = "0", Right(strLastNum, 3), _
Right(strLastNum, 4))))
strNewNum = IIf(Len(strOldNum) = 1, IIf(strOldNum = "9", "10", CStr(CLng(strOldNum) + 1)), _
IIf(Len(strOldNum) = 2, IIf(strOldNum = "99", "100", CStr(CLng(strOldNum) + 1)), _
IIf(Len(strOldNum) = 3, IIf(strOldNum = "999", "1000", CStr(CLng(strOldNum) + 1)), _
CStr(CLng(strOldNum) + 1))))
strNewNum = Format(strNewNum, "0000")
strNewIdent = Left(strLastIdent, 6) & strNewNum
strYear = Mid(strNewIdent, 2, 4)
strNewIdent = IIf(strYear = CStr(Year(Date)), strNewIdent, Replace(strNewIdent, strYear, CStr(CLng(strYear) + 1)))
strNewIdent = IIf(strYear = CStr(Year(Date)), strNewIdent, Replace(strNewIdent, Right(strNewIdent, 5), "-0001"))
Me.KapilaraID = strNewIdent
End If
End If
C2020-0001
KapilaraID ends up being the following if it's the 10TH record in the table:
C2020-0010
and KapilaraID ends up being the following if it's the 100TH record in the table:
C2020-0100
and so on....until it reaches 9999.