Creating Unique "File Number" Generation

[claneypro]

Hi,
I am looking to finish up a database that I have created by adding a feature to customize a "file number" or ID assigned for each new customer being added based on input from user at time of creation(ie the user selects the category and db assigns it a file# with specified prefix); account type/prefixes are "50" or "30" followed by a four digit running number "####". My questions are, will it be possible for me to have two different types account numbers being automatically generated from the same back end? How do I ensure that no "File Numbers" are skipped? Any tips for reducing the occurrence of duplicates?

[cactusdata]

Let your form create the number.
To prevent dupes, either apply a unique index on the field in the table and catch the error if a dupe is attempted to be saved, or, before saving it, verify that it doesn't already exist.

[NeoPa]

Yes it's possible to have a single sequential set of numbers split across two different Type codes. EG. 300001, 300002, 500003, 300004, 500005, 500006, 300007, 500008, etc.

Some typical VBA code for setting the next required value, in this case assuming the Type code to be "50" :

strType = "50"
strNext = strType & Format(Val(Nz(DMax(Expr:="Right([IDField],4)" _
                                     , Domain:="[YourTable]"), "0")) + 1, "0000")

Numbers should never be skipped unless records are deleted later on.

If you have a scenario where multiple users can add records at the same time then you can either put code in to check for duplicates after an item has been added or you can set a separate field just for the sequential number and set that as a unique index. Obviously the main ID field would be a unique index too.