Auto Generate field only when needed

[mrmims]

Hello,



I have an inventory database, and part of the database tracks Lot Numbers of materials purchased. Not all manufacturers supply or have Lot Numbers.

I need a field ("Lot_Number") in a table ("TBL_Inventory") which the user either has the option to fill in with the provided manufacturer's lot number, or, if no Lot Number is provided, the user can click a button to generate a unique Lot Number for tracking purpose.

This Lot Number IS NOT the Primary Key, "InventoryID" is.

Any ideas?

Thank you

[June7]

How should the generated lot number be determined? What structure should this number have? Is this a text field? Generating unique identifier is a very common topic.
https://www.accessforums.net/program...ple-46469.html
https://www.accessforums.net/forms/c...ble-41863.html

[mrmims]

Hello,

I want the first 6 digits to be the date, Format "mmddyy", and the last 4 digits to be "0001" and incremental from there. The field will be a Text Field

I am getting stuck on how to increment the "0001" as the other Lot Numbers in the Table will not follow the autogenerated lot number pattern.

Thank you for the links. I will begin looking through them now for a solution hopefully!

[June7]

I recommend you not use date as component of this value. How many characters do manufacturer lot numbers use?

[mrmims]

Ok to not using date.

It varies quite a lot. I have some Lot Numbers with 3 digits all numbers, and some 12 digits with numbers and letters.

[June7]

Well, what I was really trying to convey is you need a consistent prefix easily recognized as yours that will help pull up the last assigned value, like:

ABCD0001

or

ZZZZ0001

or

OurLot0001

Only problem is that 4 number places might not be enough for the life of the db. How many would be enough? One of the links I referenced shows how I generate a unique ID in the structure:

YYYYA-####

The 4 number places is enough for us because the sequence starts over every year. We've never gone over 4500. You could start over every year with a structure like:

OurLotYYYY-####