conditional autonumber ID's

[cartemj2]

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:

Client
Title/Description
Catagory
Genre
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:

REELS
Reel_ID (PK autonumber)
Client
Description
Catagory_Name (FK)
Genre (FK)
Reel_Number

CATAGORIES
Catagory_Name
Prefix

GENRES
Genre

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??

[zzqxxq]

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

[randomDude]

Hi Sir zzqxxq, I have the same question. I really hope you can help us with the code.