471,088 Members | 1,282 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,088 software developers and data experts.

conditional autonumber ID's

Does anyone know how to create autonumbers dependent on a chosen catagory?

I'll explain the scenario.....

I am working on simple a reel database for a post-production company.

The required fields are:

Reel No.

Catagories & Genres will be updated from time to time & the 'Reel No.' requires a prefix dependant on catagory.. thus I have created the folowing tables:

Reel_ID (PK autonumber)
Catagory_Name (FK)
Genre (FK)



Now here is where I get stuck... the 'Reel No.' needs to have a unique number by catagory not by reel.... ie: Music Videos would be 'MV001 - MV999' and seperatly Adverts would be 'AD001-999'.... I have tried some workarounds in VB on the forms, but these create as many problems as they solve!..... also has to be in daily use by it none literate... any clues??
Jul 4 '06 #1
2 4806
Unfortunately, AUTOnumbers are just that automatically created numbers - ideally these numbers were never intended to be used for any purpose other than to provide a unique identifier for a particular record so there is no simple mechanism to alter their format. If you need a particular format you therefore must create it and put it into a field that is not defined as an autonumber field but as a text field (your codes are alphanumeric).

This involves creating a global static variable (for each category) that has to initially set to 1 and increased by one each time a new record is added to the category in question. As the record is created you will have to prefix the number with the category abbreviation. In addition, because your format requires a 3 digit number, the first 99 records will have to be formatted to include one or two (for the 1st 10 records) leading zeros. This number would be created / formatted when the user selects the category therefore the code goes in the exit function of the combo box (I'm guessing) you have for the genre field. There really is no other way I know of to do this.

Write back if you need help with the code. Of course the easy to cheat at this would be to keep a paper record of the last number used in each category add one to it in your head, write it on the paper then just enter it into a text field - but that sort of defeats the purpose of the db dosen't it
Jul 13 '06 #2
1 Bit
Hi Sir zzqxxq, I have the same question. I really hope you can help us with the code.
Jul 7 '21 #3

Post your reply

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

Similar topics

3 posts views Thread by Ilan Sebba | last post: by
2 posts views Thread by Ed Havelaar | last post: by
35 posts views Thread by Traci | last post: by
1 post views Thread by jimfortune | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.