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

Is there any way I can define a starting value for an Auto-Increment Field in a table

P: 54
Hi

I have a table that has as its Primary Key the Auto-Increment Field:
Equipment_Number.
In M.S. Access is there any way to define a starting number for the field?

OR another way in which I could save the same problem would be:

I have a form frmAddNew_Equipment, once all the fields are filled in correctly and it has been verified that there is no double entry etc, and the 'SAVE' button is clicked (unbound form), another form, bound to a query pops up with the new equipment number. The person using the database then grabs a physical Label and attaches that to the new piece of equipment. However the labels start at number 1000. Is there anyway I can modify the query I use in the pop up form, so that 1000 will be added to the number the query produces, before the new number is displayed in a text box on the pop up form.

This is the query that drives the New Equipment_Number textbox field on the pop up form:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEquipment.Equipment_Number
  2. FROM tblEquipment
  3. WHERE (((tblEquipment.Serial_ID)=(Forms!frmAdd_Equipment!txtSerial_Number)));
Again thank you for any help

Mike
Nov 17 '08 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 374
Hi

I have a table that has as its Primary Key the Auto-Increment Field:
Equipment_Number.
In M.S. Access is there any way to define a starting number for the field?

OR another way in which I could save the same problem would be:

I have a form frmAddNew_Equipment, once all the fields are filled in correctly and it has been verified that there is no double entry etc, and the 'SAVE' button is clicked (unbound form), another form, bound to a query pops up with the new equipment number. The person using the database then grabs a physical Label and attaches that to the new piece of equipment. However the labels start at number 1000. Is there anyway I can modify the query I use in the pop up form, so that 1000 will be added to the number the query produces, before the new number is displayed in a text box on the pop up form.

This is the query that drives the New Equipment_Number textbox field on the pop up form:

SELECT tblEquipment.Equipment_Number
FROM tblEquipment
WHERE (((tblEquipment.Serial_ID)=(Forms!frmAdd_Equipment !txtSerial_Number)));

Again thank you for any help

Mike

Hey Mike,

This is one of those reasons that all new users of MS Access database fall into. You're not able to change the ID number of Autonumber field to a fixed number without manipulating the database a little. If you're database has a count of 10 and you want to go to 999, then you would add the appropriate number of records to the database (989), so the next number that is issued would be 1000. There is no feature to adjust this number at all if, for some reason that the numbers get out of sequence. this is why this is not always a good idea to use the Autonumber Primary Key field as a product code, because it is not changable.

As long as you're ok with my solution, that should allow you to change the number, but other than that, there isn't any other way to change the number a Autonumber field in a MS Access Database.

Now to change it back, you would have to clear the entry table, perform a compact on the database, and that will reset the number back to 1. Those are your only options for manipulating the autonumber field.


Hope that helps,

Joe P.
Nov 17 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

Administrator.
Nov 17 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
The short answer to your question (I try to be uncomplicated but it doesn't come naturally to me) is that the AutoNumber type, though it looks as if it may be the appropriate choice in such a situation, is not.

The standard approach is to find an alternative method of determining what a new index value should be. That way you have an almost unlimited flexibility to which values are used.
Nov 17 '08 #4

DonRayner
Expert 100+
P: 489
You can use an append query to append a record that is one less than the number you want to start numbering at.
Nov 17 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
I'm sorry Ron, but that can only work if you append records UNTIL that point is reached. It will not allow you to specify a value for an AutoNumber field. See Joe's post above.
Nov 17 '08 #6

DonRayner
Expert 100+
P: 489
I'm sorry Ron, but that can only work if you append records UNTIL that point is reached. It will not allow you to specify a value for an AutoNumber field. See Joe's post above.
I do remember using that method before I learned the error of my ways with autonumbering. I just went and check to see if it works and it does.

I copied the origional table to a new table and deleted all the records in it. I then changed the autonumber field in the copy to a number field and placed one record with a number of 999 in it.

I created an append query to append the record from the new table into the origional tabel and then ran the query. It append the record with 999 as a number and then started numbering the table from 1000 after that.
Nov 17 '08 #7

P: 54
Hi everyone

Been away the last couple of days so have not seen all the replies.

(Hi NeoPa I'll have a look at the posting rules..as I say I have not really been on here since it was the scripts website 2 years ago, but I'll see how you like code displayed and do it in future)

Though by far probably not the most elegant(?) way out, what I ended up doing (on a Sat morning after a reasonably hard night) was to rename the Equipment_Number field to Equipment_Number_Real. Then I added another column(Field) called Equipment_Number. I wrote a bit of code so that when an operator adds a piece of equipment to the database, and clicks the save record button, the code looks to the present Equipment_Number_Real value, adds 1000 to it, then displays a little pop-up form with the new equipment number that has been allocated to the new piece of equipment, then saves that new number back to the table as the Equipment_Number.

The only reason I asked the question, was that someone told me that you can start AutoNumber fields from a given value in the Alpha 5 database software and I wondered if Access would be the same.

Thanks for the responses
Mike
Nov 19 '08 #8

missinglinq
Expert 2.5K+
P: 3,532
If I followed this correctly, you're now adding 1000 to your AutoNumber field to get your number for your label. If that's so, you've still got a problem! Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.
As NeoPa said earlier, for the kind of field you're using, you need to use an auto-incrementing number hack such as one of these.

The first code here would be for an IDNumber that is defined in the table as Text:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.NewRecord Then
  3.   If RecordsetClone.RecordCount = 0 Then
  4.    Me.IDNumber = "1000"
  5.   Else
  6.    Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  7.   End If
  8. End If
  9. End Sub
If your "number" is defined as a Numerical field:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.NewRecord Then
  3.   If RecordsetClone.RecordCount = 0 Then
  4.    Me.IDNumber = 1000
  5.   Else
  6.    Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  7.   End If
  8. End If
  9. End Sub
As John explained, AutoNumbers can leave gaps, so basing an equipment number on an AutoNumber can leave gaps. In certain circumstances, they can become random, so your equipment number can become random, with a number generated a month from now coming before a number generated next week.

Linq ;0)>
Nov 19 '08 #9

NeoPa
Expert Mod 15k+
P: 31,186
(Hi NeoPa I'll have a look at the posting rules..as I say I have not really been on here since it was the scripts website 2 years ago, but I'll see how you like code displayed and do it in future)
Hi Mike.

I thought I hadn't seen you around for a while, and that would explain the rustines ;) In truth it's a standard response I use whenever I find a member not using the tags.

@Don
Fair point (about the AutoNumber start position).
I can't say that I've tried that specific sequence of events, but I do know that other attempts to add values to an AutoNumber field have failed for me (probably using a form I guess).
Nov 20 '08 #10

P: 54
O.K. thanks NeoPa and Linq

I ended up ditching a compuilsory number in the end..it was going to be too tricky if equipment got deleted etc..even if there are ways around it.

In the end I still use the +1000 but the actual Equipment Number (affixed to the equipment) is now in a normal Integer Field of the table..and when it pops up saying what the next number should be the user is given the option to modify that number and enter the number they have used. It then resumes the count from the number they have entered. It just gives them an idea if they have used labels and perhaps forgotten to enter the equipment onto the system. As long as there are no repeats they can use any number, and all searching and verification (as there could imaginably be identical Serial Numbers) is done on the Model Type and Serial Number combined...same as when entering new pieces of equipment.

So many thanks for the advise as always and all the best to all for Xmas if you live in a part of the world where it is fast approaching!!

Mike
Nov 24 '08 #11

NeoPa
Expert Mod 15k+
P: 31,186
Merry Xmas to you Mike.

As to your solution, it sounds like you have implemented it as well as can be in the circumstances.
Nov 25 '08 #12

Post your reply

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