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

[ZaphodBBB]

Hi

I have a table that has as its Primary Key the Auto-Increment Field:
Equipment_Numbe r.
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_Equip ment, 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_Numbe r 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

[PianoMan64]

Hi

I have a table that has as its Primary Key the Auto-Increment Field:
Equipment_Numbe r.
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_Equip ment, 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_Numbe r textbox field on the pop up form:

SELECT tblEquipment.Eq uipment_Number
FROM tblEquipment
WHERE (((tblEquipment .Serial_ID)=(Fo rms!frmAdd_Equi pment!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.

[NeoPa]

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.

[NeoPa]

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.

[DonRayner]

You can use an append query to append a record that is one less than the number you want to start numbering at.

[NeoPa]

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.

[DonRayner]

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.

[ZaphodBBB]

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_Numbe r field to Equipment_Numbe r_Real. Then I added another column(Field) called Equipment_Numbe r. 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_Numbe r_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_Numbe r.

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

[missinglinq]

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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1000"
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

If your "number" is defined as a Numerical field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = 1000
  Else
   Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  End If
End If
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)>

[NeoPa]

(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).