If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create a lot # and store it in an indexed – no duplicates field ?
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. -- Thanks, Kerry |
#2
|
|||
|
|||
Create a lot # and store it in an indexed – no duplicates field ?
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) "Kerry" wrote in message ... 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. -- Thanks, Kerry |
#3
|
|||
|
|||
Create a lot # and store it in an indexed – no duplicates field ?
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. "Kerry" wrote in message ... 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. -- Thanks, Kerry |
Thread Tools | |
Display Modes | |
|
|