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