By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,540 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Creating Unique "File Number" Generation

P: 1
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?
1 Week Ago #1
Share this Question
Share on Google+
2 Replies

P: 65
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.
1 Week Ago #2

Expert Mod 15k+
P: 31,606
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" :
Expand|Select|Wrap|Line Numbers
  1. strType = "50"
  2. strNext = strType & Format(Val(Nz(DMax(Expr:="Right([IDField],4)" _
  3.                                      , 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.
6 Days Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.