Unique Number Generation

D

Dave

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
 
D

Dave

I also tried this, but because the date can be in duplicate entries in the
date field, this did not work either.
strLastLN = DLookup("lotNum", "tblAngle", " [date] = #" & DMax("[date]",
"tblAngle") & "#")
 
R

Roger Carlson

Just off-hand, I'd say this is a Y2K thing. The simplist solution would be
to have all 4 digits of the year in the lot number. Then it will
automatically sort correctly, even as text.

If that is not possible, you'll have to add a Where clause to your DMax that
only limits it to records with less than, say 35 in the 3&4 character slots:

strLastLN = DMax("LotNum", "tblAngle", "Mid([LotNum],3,2) < '35'")

This presupposes, you don't have any records that go back to 1935. And, of
course, this will only work until 2035.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
D

Dave

Roger,
Is it possible to reference the date field to make this work, I currently
have a date field with a short date populated automatically from the form
upon opening.
Dave

Roger Carlson said:
Just off-hand, I'd say this is a Y2K thing. The simplist solution would be
to have all 4 digits of the year in the lot number. Then it will
automatically sort correctly, even as text.

If that is not possible, you'll have to add a Where clause to your DMax that
only limits it to records with less than, say 35 in the 3&4 character slots:

strLastLN = DMax("LotNum", "tblAngle", "Mid([LotNum],3,2) < '35'")

This presupposes, you don't have any records that go back to 1935. And, of
course, this will only work until 2035.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Dave said:
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
Click to expand...
Click to expand...
 
D

Dave

I can't change the way the lot number appears because of the time it was
designed, and the fact that it is still in use today. Probably the earliest
record would be from 1980.

Dave said:
Roger,
Is it possible to reference the date field to make this work, I currently
have a date field with a short date populated automatically from the form
upon opening.
Dave

Roger Carlson said:
Just off-hand, I'd say this is a Y2K thing. The simplist solution would be
to have all 4 digits of the year in the lot number. Then it will
automatically sort correctly, even as text.

If that is not possible, you'll have to add a Where clause to your DMax that
only limits it to records with less than, say 35 in the 3&4 character slots:

strLastLN = DMax("LotNum", "tblAngle", "Mid([LotNum],3,2) < '35'")

This presupposes, you don't have any records that go back to 1935. And, of
course, this will only work until 2035.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Dave said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Carlson

Probably. What's the name of your date field?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dave said:
Roger,
Is it possible to reference the date field to make this work, I currently
have a date field with a short date populated automatically from the form
upon opening.
Dave

Roger Carlson said:
Just off-hand, I'd say this is a Y2K thing. The simplist solution would be
to have all 4 digits of the year in the lot number. Then it will
automatically sort correctly, even as text.

If that is not possible, you'll have to add a Where clause to your DMax that
only limits it to records with less than, say 35 in the 3&4 character slots:

strLastLN = DMax("LotNum", "tblAngle", "Mid([LotNum],3,2) < '35'")

This presupposes, you don't have any records that go back to 1935. And, of
course, this will only work until 2035.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Dave said:
I have a form for entering Lot Book entries with a text box that
automatically generates the next consecutive lot number. This is bound
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave

Date

Roger Carlson said:
Probably. What's the name of your date field?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dave said:
Roger,
Is it possible to reference the date field to make this work, I currently
have a date field with a short date populated automatically from the form
upon opening.
Dave

Roger Carlson said:
Just off-hand, I'd say this is a Y2K thing. The simplist solution would be
to have all 4 digits of the year in the lot number. Then it will
automatically sort correctly, even as text.

If that is not possible, you'll have to add a Where clause to your DMax that
only limits it to records with less than, say 35 in the 3&4 character slots:

strLastLN = DMax("LotNum", "tblAngle", "Mid([LotNum],3,2) < '35'")

This presupposes, you don't have any records that go back to 1935. And, of
course, this will only work until 2035.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




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
Click to expand...
Click to expand...
Click to expand...
 
D

Duncan Bachen

Dave said:
Date
Click to expand...

if that's the case, make sure you enclose it in [], such as [Date]
whenever you reference it in code (especially DLookups).

Date is a reserved word in Access and normally shouldn't be used as a
field name. Enclosing it in brackets ensures that it references the field.
 
R

Roger Carlson

Sorry, I've tried, but I can't figure a way to use the [date] with the DMax
function. You'd have to create your own DMax function and I don't think
it's worth it.

BTW, Duncan is correct. "Date" is a poor name for a field as it is a
reserved word. This confuses Access sometimes, even if you remember to
always add the brackets.

Sorry I could be more help.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dave said:
Date

Roger Carlson said:
Probably. What's the name of your date field?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dave said:
Roger,
Is it possible to reference the date field to make this work, I currently
have a date field with a short date populated automatically from the form
upon opening.
Dave

:

Just off-hand, I'd say this is a Y2K thing. The simplist solution
Click to expand...
would
be
to have all 4 digits of the year in the lot number. Then it will
automatically sort correctly, even as text.

If that is not possible, you'll have to add a Where clause to your
Click to expand...
DMax
that
only limits it to records with less than, say 35 in the 3&4
Click to expand...
character
slots:
strLastLN = DMax("LotNum", "tblAngle", "Mid([LotNum],3,2) < '35'")

This presupposes, you don't have any records that go back to 1935.
Click to expand...
And,
of
course, this will only work until 2035.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




I have a form for entering Lot Book entries with a text box that
automatically generates the next consecutive lot number. This is
Click to expand...
bound
to
a
table with the LotNum field as the primary key, and includes a
Click to expand...
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
Click to expand...
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,
Click to expand...
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,
Click to expand...
my
form
is
supposed to incriment to the next lot number upon opening, but
Click to expand...
will
not
because of my Dmax setup. My LotNum field is currently set at
Click to expand...
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,
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with field problem 3
Autonumber help in form 7
DblClick Event 8
random number generation 2
Conversion from Number to Word!! Aki 1
type mismatch error in macro in excel 0
Numaric to Inword in excel by VB 1
Help with creating a model serial number 2

Top