472,961 Members | 1,558 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,961 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 4992
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

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

Similar topics

by: Ilan Sebba | last post by:
I have a 'supertype' table with only one field: autonumber. Call this table the 'parent' table. There are two subtypes, 'androids' and 'martians'. Martian have only one thing in common: they give...
by: Ed Havelaar | last post by:
Hi, Hope someone can help. Here's the scenario: I have two MSAccess tables MainTab and SubTab. MainTab has an autonumber 'id' column as primary key. Subtab has this id column as a foreign...
by: Traci | last post by:
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key...
by: jimfortune | last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore, sometimes I use those same fields in orderdetail type tables. So it's important in that case that once an autonumber key value is...
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query,...
by: HS1 | last post by:
Hello I have a table in Access Database. This table has a AutoNumber field. I use a DataGrid to show that table When I insert a new record in for this table using a DataGrid, there is a...
by: jason.teen | last post by:
Hi, I have this scenario - 2 separate db's (filename = dbOne.mdb" and "dbTwo.mdb") both with a table called tblMapped in it dbOne.tblMapped looks like:...
by: mswhiteside | last post by:
Hello All, I have a query that i need to have ID's that start over on certain records in the Table like below Field 1---Conditional AutoNumber IRM-------------1 1-----------------2...
by: Wayne | last post by:
I'm using the following SQL statement to find the next highest autonumber value in a table where "CDUGActID is the autonumber field in the "CDUGActuals" table: SELECT CDUGActuals.CDUGActID,...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

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.