G
Guest
I have a form for entering Lot Book entries with a text box that
automatically generates the next consecutive lot number. This is bound to a
table with the LotNum field as the primary key, and includes a sorted date
field also. Here is an example of the lot number and the criteria for making
it.
Example: 1080-999
1A80-000
1A06-001
First character being the type of material and specific to the table and
bound form, this number will always stay the same.
1=Angle Iron
2=Dome
Second character is for every 1000 entries, it incriments by 1, and goes
from 0-9 and then A-Z. This is based on the last three numbers.
Character 3 and 4 are the last 2 digits of the current year.
The last three incriment by 1 for every entry. 000-999
My problem is that if I have a lot number in my table with an old date like
1080-000, and then a second lot number of current date 1006-001, my form is
supposed to incriment to the next lot number upon opening, but will not
because of my Dmax setup. My LotNum field is currently set at text. Could
someone provide me with the appropriate code to get around this issue
Code I currently have, but does not work correctly.
Private Sub Form_Current()
Dim strLastLN As String
Dim strYear As String
Dim strFirstChar As String
Dim strLastInt As String
Dim strNewLN As String
Dim strTbl As String
'Examples:
' 1905-998
' 1905-999
' 1A05-001
' 1A05-002
If Me.NewRecord = True Then
strLastLN = DMax("LotNum", "tblAngle")
'Get table number
strTbl = left(strLastLN, 1)
'Set the year.
strYear = Format(VBA.Date, "yy")
'Set alphanumeric character. Can be 0 to Z
strFirstChar = Mid(strLastLN, 2, 1)
'check if we can still increment.
If CLng(right(strLastLN, 3)) < 999 Then
strNewLN = strTbl & strFirstChar & strYear & "-" _
& Format(CLng(right(strLastLN, 3)) + 1, "000")
Else
'increment the first number;
'A-Z ANSI chars 65-90
'If existing alphanumeric is between 65 and 89, increment it.
'Or if it is numeric and <9 increment it to next number.
Select Case strFirstChar
Case "0" To "8"
strFirstChar = Chr(CInt(strFirstChar) + 1)
Case "9"
strFirstChar = "A"
Case "A" To "Y"
strFirstChar = Chr(Asc(strFirstChar) + 1)
Case "Z"
strFirstChar = "0"
End Select
'reset the last 3 to 001.
strNewLN = strTbl & strFirstChar & strYear & "-" & "001"
End If
Me.txtLotNum = strNewLN
End If
Debug.Print strNewLN
End Sub
automatically generates the next consecutive lot number. This is bound to a
table with the LotNum field as the primary key, and includes a sorted date
field also. Here is an example of the lot number and the criteria for making
it.
Example: 1080-999
1A80-000
1A06-001
First character being the type of material and specific to the table and
bound form, this number will always stay the same.
1=Angle Iron
2=Dome
Second character is for every 1000 entries, it incriments by 1, and goes
from 0-9 and then A-Z. This is based on the last three numbers.
Character 3 and 4 are the last 2 digits of the current year.
The last three incriment by 1 for every entry. 000-999
My problem is that if I have a lot number in my table with an old date like
1080-000, and then a second lot number of current date 1006-001, my form is
supposed to incriment to the next lot number upon opening, but will not
because of my Dmax setup. My LotNum field is currently set at text. Could
someone provide me with the appropriate code to get around this issue
Code I currently have, but does not work correctly.
Private Sub Form_Current()
Dim strLastLN As String
Dim strYear As String
Dim strFirstChar As String
Dim strLastInt As String
Dim strNewLN As String
Dim strTbl As String
'Examples:
' 1905-998
' 1905-999
' 1A05-001
' 1A05-002
If Me.NewRecord = True Then
strLastLN = DMax("LotNum", "tblAngle")
'Get table number
strTbl = left(strLastLN, 1)
'Set the year.
strYear = Format(VBA.Date, "yy")
'Set alphanumeric character. Can be 0 to Z
strFirstChar = Mid(strLastLN, 2, 1)
'check if we can still increment.
If CLng(right(strLastLN, 3)) < 999 Then
strNewLN = strTbl & strFirstChar & strYear & "-" _
& Format(CLng(right(strLastLN, 3)) + 1, "000")
Else
'increment the first number;
'A-Z ANSI chars 65-90
'If existing alphanumeric is between 65 and 89, increment it.
'Or if it is numeric and <9 increment it to next number.
Select Case strFirstChar
Case "0" To "8"
strFirstChar = Chr(CInt(strFirstChar) + 1)
Case "9"
strFirstChar = "A"
Case "A" To "Y"
strFirstChar = Chr(Asc(strFirstChar) + 1)
Case "Z"
strFirstChar = "0"
End Select
'reset the last 3 to 001.
strNewLN = strTbl & strFirstChar & strYear & "-" & "001"
End If
Me.txtLotNum = strNewLN
End If
Debug.Print strNewLN
End Sub