Serial number creation - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Serial number creation

James Whitney

5 years ago

The part number of items on an order (or quote) are often assigned at the time of entry and can be an ambiguous alphanumeric string that may or may not follow any particular convention. We would like that part number to be added to a ProductT table if the number created has never been used before, but not duplicated if it has been.
Certain items are assemblies that require a serial number while other line items on the same order do not get a serial number. There may be multiple line items that require serial numbers and multiple that do not on the same order.
We need a method to indicate whether the item being entered does or does not require a serial number and, if it does, then Access should assign a unique serial number to that line item with no gaps in sequence from previously assigned serial numbers, but for sure no repeats. We would also like to decide what the first serial number in our database will be. (e.g. 36001)

EXAMPLE:
Sales Order Number: 1287
1 pc. RC1000 Rod Clevis   (no SN required)
1 pc. AKB-MP1-2 x 6 x 1-K  (assigned SN 36754)
12 pcs.AKN-MX1-5 x 34 x 1.75-K (assigned SN 36755)
1 pc.SKA500  (no SN required)
1 pc.GSK1000  (no SN required)

Sales Order Number: 1288
1 pc.HKN-ME5-6 x 42 x 2.5-k (assigned SN 36756)
1 pc.RC2000 Rod Clevis  (no SN required)
2 pcs.EB1000 Eye Bracket (no SN required)

Any suggestions are appreciated!

Scott Axton @Reply

5 years ago

For the serial number I suggest watching the Custom Sequential Number video.

Regarding the serial no. vs none what is the 'logic' behind what does and does not need the number?
Sounds like it's pretty arbitrary. Unfortunately computers (and Access) are pretty Black or White - True or False. It's almost impossible to code for the Gray or Maybe or Some Times.
You as the developer need to find that out.
Questions or statements can go along way to determining what you need.
Example:
Any time it is xxx we assign a serial number.
We always stock the standard part xyz and it is available from multiple vendors.
Any time we assemble standard parts to an assembly we assign a serial no.

If it's all done "manually on the fly" as you enter the part a simple yes/no check box on your entry form "SerialRequired" could activate or not a serial field.
To much 'what if' here. You need to nail down the criteria for us to be able to help further.

James Whitney OP @Reply

5 years ago

Scott, thanks for your reply.
Imagine you ordered a washer & dryer. Each of them are assigned a serial number at some point.
An example order entry might be for 1) washer and 1) dryer, but the customer also wants 2) hoses, 1) vent duct, and 1) lint screen. Those last three line items have part numbers, but are not serialized. The washer and dryer, on the other hand, must get assigned unique serial numbers. We want that to happen at the time we enter the model number into the quote form. The model number is developed independent of Access by the person doing the quote. It can be a repeated model number or it may be unique. (That is just the nature of our product!)
One idea I had was a yes/no box and if the answer is yes, create a new record in a separate table adding today's date & the model number, then retrieve the record ID number, add a starting value to it (e.g. 36000 + ID#), then populate the SN field on the quote with that as the serial number. 36001 & 36002 in this example.

Scott Axton @Reply

5 years ago

Something to consider - maybe - just thinking out loud.
Create a "category" for your parts.  Default to "Standard" or whatever name you choose.  The second choice would be SN.  If your part category is SN then you would activate your SN fields.  ID's, SerialNumber, and so on.

For inventory sake you would have to bring in the parts - 3 hoses, 5 vent ducts, etc  That part is easy.
Where you have serial numbered items the would have to always be brought in individually even though make and model might be the same. 1 washer model: x SN: x-0001.  Qty could only ever be 1.

If you are a reseller it would be different than if you are a manufacturer.
For quoting purposes, I would not quote specific serial numbers.  Only make/model.  The  specific SN would be assigned at the order fulfillment stage.  If it's in the quote that serial number would be tied up and couldn't ever be sold to someone else.  What happens if the quote isn't converted to an order?  What if the item was sold to another?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View