Create a lot # and store it in an indexed – no duplicates field ?

[Kerry]

Access 2003. I have created 2 linked databases for our receiving dept
records. The Raw Materials db has p/n, description, fixed location, and shelf
life (days). The Lot Number db has p/n, receipt date, vendor lot #, and our
lot #. The issue is the way our company creates lot #’s. We use the p/n,
year (single digit), and the last 3 digits incremented (i.e.: 32754-9001,
32754-9002, 1193225-9001, and 1193225-9002). The lot # is used for FIFO when
pulling for the plant thus the incremental last digits. Question: Can Access
create the lot # and store it in an indexed – no duplicates field in the Lot
Number db? I have a form for initial entry of the p/n, description is
imported, vendor lot #, receipt date, and need our lot # to either Access
generate or show existing lot #’s for the p/n so that we type in the next
available lot #. I would also like to print Avery labels for the receiving
personnel to apply to the material with this info.

 

[BruceM]

Best would be to increment the number separately. In a command button Click
event, or the form's Before Update event, or wherever makes sense for your
situation:

Me.SeqNum.DefaultValue = Nz(DMax("[SeqNum]","[tblRawMat]","[PN] = " & Me.PN
& _
" AND Year([DateField]) = " &
Year(Date())),0) + 1

This tells Access to find the largest value in the SeqNum field (the one
represented by the final three digits) in a record in which PN is the same
as PN in the current record and the year in DateField (a date field in the
record) is the same as the current year. To use DateField in the current
record instead, use this:
Year(Me.DateField) in place of:
Year(Date())

Use of DefaultValue assumes the record has not been saved. There are
various ways to approach this, depending on the details.

To put it back together you could use a calculated query field:

FullNumber: [PN] & "-" & Right(Year([DateField]),1) & Format(SeqNum,"000")

I don't think you can create a unique index on the combination of PN,
SeqNum, and Year unless you either store Year separately or include it in
SeqNum. To include it in SeqNum you could try something like this:

Me.SeqNum.DefaultValue = Nz(DMax("[SeqNum]","[tblRawMat]","[PN] = " & Me.PN
& _
" AND Year([DateField]) = " & Year(Date())),Right(Year(Date()) *
1000) + 1

You could then create a unique index on SeqNum and PN

For display:
FullNumber: [PN] & "-" & Right([SeqNum],4)

 

[BruceM]

On another point, with a one-digit year you will end up with duplicate lot
numbers in ten years if any of the part numbers are the same.