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

Auto numbering in Forms

P: 1
I have two problems, but should point out I am a newbie to Access and therefore need simple language to understand.

First issue. I have designed a database (similar to say a CD database) wherein I wish to add a record then list items under that entry. I would like to autonumber those entries, but find I can't do so. If I turn on autonumber and enter 6 items under the first record entry, then start my second db record and list 8 items, the second entry autonumbers my list from 7 (the first record with six items + first of new new list on new record). How can I acheive autonumbering within the record always starting at 1 ?

Second issue. I enter the first record and it is numbered 1. I then enter but subsequently delete record two. When I now enter a new record 2 it actually numbers from 3. How can I clean this up ?

I operate on Access 2003 in Windows XP.

Any help would be appreciated.
May 17 '07 #1
Share this Question
Share on Google+
2 Replies

P: 97
Autonumber fields are designed to give a unique reference for the record and will not give you, as you discovered, what you want to achieve.

This will only work using a form.
To number automatically you will need to write some code in the AfterUpdate Events to run an UPDATE query when deleting a record to reduce the record number of all records where the number is greater than the record just deleted.
When adding a record, you will need to write the next available number directly into the control.

Hope that helps with you first issue
May 17 '07 #2

Expert Mod 15k+
P: 31,494
I think the point Kepston has just made explains well why AutoNumbering should not be used in the way that you're attempting to use it.
You will have to :
  1. Decide how important this numbering is to the way your database works.
  2. If necessary, come up with a method to work out and readjust the numbers when new ones are added and when existing ones are deleted.
I expect B will involve writing code and running update queries.
May 26 '07 #3

Post your reply

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