Auto-generate lot# to Table on new entry.

[dkotula]

Hi

I am trying to build a DB for our LotBooks and was wondering if
could get some assistance. Currently I hava a table with thes
fields: Lot#, Date, Vendor, Mfg., Spec, Grade, Size, Length, PO#
Qty., and Heat#. What I would like is to be able to automaticall
generate a new lot# everytime the form to create a new record i
opened. The challenging part is the way the Lot# is designed
"Example: 3A05-987
3 - Is the type of item it is pipe, angle, ect
A - Is in reference to the last three numbers, when they hit 999, th
A will change to a B. This digit starts at 0 to 9 and then A,B,C
ect
05 - Is the last two digits of the year, which I would like t
automatically be entered also
987 - the next number would be 989. Every time this hits 999, the nex
character for "A" goes to "B", which represents 1000.

Any ideas on how I should achieve this? :? :? :?

Thank
Dav

 

[Dirk Goldgar]

Hi,

I am trying to build a DB for our LotBooks and was wondering if I
could get some assistance. Currently I hava a table with these
fields: Lot#, Date, Vendor, Mfg., Spec, Grade, Size, Length, PO#,
Qty., and Heat#. What I would like is to be able to automatically
generate a new lot# everytime the form to create a new record is
opened. The challenging part is the way the Lot# is designed.
"Example: 3A05-987"
3 - Is the type of item it is pipe, angle, ect.
A - Is in reference to the last three numbers, when they hit 999, the
A will change to a B. This digit starts at 0 to 9 and then A,B,C,
ect.
05 - Is the last two digits of the year, which I would like to
automatically be entered also.
987 - the next number would be 989. Every time this hits 999, the next
character for "A" goes to "B", which represents 1000.

Any ideas on how I should achieve this? :? :? :?

Thanks
Dave

This is an example of why this sort of so-called "intelligent key" tends
to be frowned on. However, such a lot# can be generated automatically,
but not immediately when "the form to create a new record is opened",
unless the type of item (which determines the first character of the
lot#) is known at that time. Also, if this database will be used by
more than one user at once, there's always the possibility that one user
could open the form to create a new record, thus generating a particular
lot#, and then another user could open the form and generate the same
lot#, before the first record is saved. That would lead to a
duplicate-key error when both users attempt to save the record. You
will want to eliminate or at least minimize the chance of that
happening.

The safest approach is to defer the generation of the lot# until right
before the record is saved. To that end, you could have code in the
form's BeforeUpdate event like this:

'----- start of code -----
'*** WARNING: AIR CODE ***
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varLastLot As Variant
Dim strItemType As String
Dim strYear As String
Dim strSeqCode As String
Dim strSeqNum As String

If Me.NewRecord Then

strItemType = Me!ItemType
strYear = Format(Date, "yy")

varLastLot = _
DMax("LotNo", "tblLots", _
"LotNo Like '" & strItemType & _
"?" & strYear & "-###")

If IsNull(varLastLot) Then
strSeqCode = "0"
strSeqNum = "001"
Else
strSeqCode = Mid(varLastNo, 2, 1)
strSeqNum = Right(varLastNo, 3)

If strSeqNum = "999" Then

strSeqNum = "001"

Select Case strSeqCode
Case < "9"
strSeqCode = CStr(CInt(strSeqCode) + 1)
Case "9"
strSeqCode = "A"
Case Else
strSeqCode = Chr$(Asc(strSeqCode) + 1)
End Select

Else
strSeqNum = Format(CInt(strSeqNum) + 1, "000")
End If

End If

Me!LotNo = strItemType & strSeqCode & strYear & "-" & strSeqNum

End If

End Sub
'----- end of code -----

If there are two users who happen to be saving new records for the same
item type at the same time, so that one must get a duplicate-key error,
that user can just try again to save and a new, non-conflicting LotNo
will be generated.

 

[dkotula]

Thank you for the imput
Sorry abouit the design, this is something that has been in use fo
several years as a spread sheet, and I am trying to take advantage o
the benefits of Access
Currently, there is only one person that enters any data, everyon
else just views the information stored. If anything, there could b
one or two other people, but it is unlikely that it would happen a
the same time

 

[Dirk Goldgar]

Thank you for the imput.
Sorry abouit the design, this is something that has been in use for
several years as a spread sheet, and I am trying to take advantage of
the benefits of Access.
Currently, there is only one person that enters any data, everyone
else just views the information stored. If anything, there could be
one or two other people, but it is unlikely that it would happen at
the same time.

Did you see my reply to your message with the subject "Auto-generate
lot# to Table on new entry" ? It lays out one possible approach in
detail.

 

[dkotula]

Dirk

with your code added to the form, I get a blank text boxes in for
without the generation of a Lot#

Can I use this in conjuction with Macro "On Open" GoToRecord, Objec
type-form, Object Name [form name], and Record-New

Dav

 

[dkotula]

I am currently trying to work with this code, but I can't get the yea
to display in the lot number

Private Sub Form_Current(
Dim strLastLN As Strin
Dim strYear As Strin
Dim strFirstChar As Strin
Dim strLastInt As Strin
Dim strNewLN As Strin
Dim strTbl As Strin
'Examples
' 1905-99
' 1905-99
' 1A05-00
' 1A05-00

If Me.NewRecord = True The
strLastLN = DMax("LotNum", "tblAngle"

'Get table numbe
strTbl = Left(strLastLN, 1

'Set the year
strYear = Format(Date, "yy"

'Set alphanumeric character. Can be 0 to
strFirstChar = Mid(strLastLN, 2, 1

'check if we can still increment
If CLng(right(strLastLN, 3)) < 999 The
strNewLN = strTbl & strFirstChar & strYear
"-"
& Format(CLng(right(strLastLN, 3)) + 1, "000"
Els
'increment the first number
'A-Z ANSI chars 65-9
'If existing alphanumeric is between 65 and 89, incremen
it
'Or if it is numeric and <9 increment it to nex
number
Select Case strFirstCha
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 Selec

'reset the last 3 to 001
strNewLN = strTbl & strFirstChar & strYear
"-" & "001

End I
Me.txtLotNum = strNewL
End I
Debug.Print strNewL
End Su

 

[Dirk Goldgar]

I am currently trying to work with this code, but I can't get the year
to display in the lot number.

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(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

Did you get this resolved? I lost track of this discussion thread.