Force Autonumber to produce a bigger number

Execute the following from SQL text view


Insert into <table>
( <autonumber_col> , any other fields that are not null/validate and the default value not allowed)
VALUES
(<starting number -1> , remaining fields)

OR using the QBE grid

If you have 1 record, copy all the fields, alter the update of the autonumber with 1 less than starting number and add a where cluase matching a value already in the database.

To be a little more verbose

Open the QBE

Select your table

Change the query type to an update.

In the prompt for what table, reselect the same table again.

select all the fields in the table except the *, drag these to the field list.

Switch to table view, use the first autonumber value in the table.

Switch back to QBE.

In the autonumber field, remove the append to.

Immediatly below, in the where part, enter the number you got from the table.

In the QBE where the table is displayed, select the autonumber field and drag it to the field grid.

Change the field line to the number you want to start from minus 1.

Execute.

Delete the record you have created.

are you sure you are talking about  microsoft access 2000 (office premium editon) because I cant find the QBE or the sql view...

The autonumber is there to automatically give a unique key. You shouldn't bother what the contents of the key are.
(Especially as you don't have control about his contents!)

When you want a "controlled" key, you should:
1) change the fields from autonumber to numeric or
2) Add your own customer number.

However all related problems for keeping keynumbers are yours.

A shortcut solution could be to add an extra own customer number and store the autonumber +1000 (or something like that) in it. However when adding new row's you will have to make sure that this field is added.

For me it is just important that the customer doesn't get a bill saying invoice nr "3"

and how do I make that setting autonumber +1000 as a new row???

Hello BRAUN,

Another solution would be using Format() function (can be used to add leading zeroes) or Expression field (calculate new value) to show customer number like you want. The customer number is actually stored in the table 1,2,3.. but in forms/reports etc. it's shown as 1000, 1002, 1003 or 001000, 001001, 001002 or what you like.

You could make a query in your order form, which has a new field fCustomerNumber, which you can use in your form as a Control source.

Here's an example (customer form):
SELECT *,
       [Customer_ID]+1000 As fCustomerNumber
FROM tblCustomers;

(Customer_ID is your autonumber field)

And same with order form's query:
SELECT *,
       [Customer_ID]+1000 As fCustomerNumber
FROM tblOrders AS O,
     tblCustomers AS C
Where O.Customer_ID = C.Customer_ID;

If you update the customer number foreign key in order table you lose the data integrity - because autonumber field in customer table cannot be updated.

Hope this helps,
Paasky

Also you can change the ControlSource of your customer number form textbox field:

=[Customer_ID]+1000

will show 1001, if the [Customer_ID] field actual value is 1.

Paasky

Paasky, the proposal for a seperate +1000 customer number was intended to be an initial fill of the customer number field, after which it should be maintained by the application, as (to my experience) the autonumber can cause serious problems when used as a key that you would like to communicate with with your customers!
(See my replication remark and the fact that building the table from scratch resets autonumber, thus corrupting the invoice_id/customer_id !)

OK. Sorry I read the above comments poorly before sending my suggestion.

I've also used own Sequence table in (Access) applications which has the fields "SEQUENCE_NAME", "CURRENT_VALUE", "INCREMENT" where I can maintain starting value and increment and get there new values with a function.

Paasky

I do the same, even with a special form for the user to correct the startvalue when he wants to. (Ofcourse lower value's will not be accepted to prevent duplicates)

My user codes his ordernumbers like YYYY### and thus he can decide when to start numbering orders for the coming year when he wants)

I have that form too, and also check value must be > MAX() !! :-)

Check Help:
Change the starting value of an incrementing AutoNumber field

With new value AS Long Integer field in a table, Append this value to the AutoNumber field in original table.

QBE is query by design.  OK I haven't used Access 2000, but I would be suprised if this had been remove.

When you have the query design view, there is a drop down icon on the toolbar, or use the menu|view options to switch between design, datasheet and sql view.

I happened to recall that it is possible to set/change the value of an autonumber field thru code, so I did some experimenting.  I started with Table1 which contains two fields cntPrimaryID (autonumber) and strValue (a string).

Here's a quick routine I wrote:
   Dim rst As Recordset
   Set rst = DBEngine(0)(0).OpenRecordset("Table1")
   With rst
      .AddNew
            !cntPrimaryID = 500
      .Update
   End With 'rst

Ran it against an empty table and it succesfully added a record to the table with cntPrimaryID = 500.
Changed 500 to 450 (in the code), ran it again and it added 450 to the table.
Went into the table, began keying a new record and noticed the new autonumber value was 502 (I think), and then I cancelled creating the new record.
Changed 450 (in the code) to 425, ran it, and it added 425 to the table.
Keyed another new record and noticed the new autonumber value was 504, and cancelled creating the new record again.
Compacted the database, went in to create another new record and Access used 501.

Conclusions:
1. You can change/set an autonumber value thru code (as long as it doesn't violate any key constraints).
2. Access keeps track, internally, of the next autonumber field.  If the new record is greater than the internal number, Access will update the internal counter.
3. If the new record is *less* than the next internal number, Access still increments the internal counter.
4. As most of you probably already knew, Access reset the "next autonumber to use" when you compact the database.

(Whew... it's easier to look at the tests I ran that read that concusion!  Oh well.)

'Here is a way:
' to make 1001 as a new number
' this is similar to my previous comment but execute the code in an button_click event.

CurrentDb.Execute "INSERT INTO theTable( theAutoField)  values (1000);"

' Here is a more general way
' In a form you need:
'      text field: newAutoNumber
'      A button  : new_auto
' here is the new_auto_click event
'>>>

Private Sub new_auto_Click()
   If DMax("an", "a1") < newAutoNumber - 1 Then
      CurrentDb.Execute "INSERT INTO a1 ( an )  values ( " & newAutoNumber - 1 & ");"
   else
    MsgBox ("Not a valid new autonumber")
   End If
End Sub
'<<<

' What I found through experimenting
' You can re-enter missing autonumbers



'>>>
Private Sub new_auto_Click()  
      CurrentDb.Execute "INSERT INTO a1 ( an )  values ( " & newAutoNumber - 1 & ");"
End Sub
'<<<

'where newAutoNumber  is a text field on a form
'      new_auto is a button to click


' if the new value exisits, nothing happens.

' my table : a1
' autofield: an
' cut&paste :)

hnasr:

Just make a .mdb replicate.
Then inspect the value's of the autonumber field when adding new entries!

Done, then you will see why I advise no to use autonumber for codes you sent to the outside world.

As last remark:
Just try to copy two autonumber related tables with referential integrety to another .mdb . . . .  (feel the troubles?!)

nico5038,

"The probelm is that I want to force the autonumber ..." (BRAUN)

Acutally we are trying to open doors for the questioner.  We don't claim the ultimate solution to his problem.  Your comments are highly appreciated.

But do you really mean it to have referential integrety with autonumber related tables?.  I feel that the autonumber is just for the sake of having a key to a table with no other keys.

I always use it as a (for the user) hidden unique key. Normally you will find that users like the possibility to change a once registered key. In that case my internal key (an autonumber) is static and used for the relations, the userkey on the other hand may be  assigned any value by the user. I just check whether it's unique, but that's all. All my references in the database are still valid, as I use the internal key!

I'm in agreement with nico here (except for the replication thingy...lol)
I have long been in the habit of using autonumber for primary keys and parent/child relationships.  That brings up an interesting point relative to this question... I also turn on cascading updates!  Since the autonumber field could theoretically be changed thru code, I decided it's better to be safe than sorry.

Another advantage of the autonumber parent/child is storage space.  A long integer uses a fixed 4 bytes per record.  A text field uses 1 byte per character (?I think).  If you happen to have a multi-field key relating the parent to the child, it can get very expensive.  I'll never forget the 50mb data-only (no forms, etc.) database that I converted for a "power user".  The original used all text linking fields; the first step of my re-design replaced the text keys with autonumber keys and reduced it to 36mb.

Believer is correct about the length of the difference.
However, it's not once, the difference is also counting when there is an index for the field. This also consumes space extra !

Look.

I agree woth most of the above technical comments. However, I do understand that you want the invoice numbers to start at like 1000. This should be your primary key. Want to know the easy way?

Create 10 records.
Copy them
Paste them 9 times
Copy 100 records.
PAste them 9 times
Delete all records

Now the counting will start at way over 1000. there you go. EASY.

MVietri,

you could do the same with the following code:

Public Function AddRecords(HowMany As Long, TableName As String)
Dim l As Long
Dim rst As Recordset

     Set rst = CurrentDb.OpenRecordset(TableName)
     With rst
         For l = 1 To HowMany
            .AddNew
            .Update
         Next
     End With
     Set rst = Nothing

     ' remove records
     CurrentDb.Execute ("DELETE FROM " & TableName)
End Function

Usage example:
? Addrecords(999,"table1")

increases autonumber to 999 so the next value will be 1000.

hope this helps,
Paasky

mvietri: That's *easy*?  I like cut & paste code once & run it better... take your pick of all the code that's been posted already...

mvietri (and BRAUN ofcourse) to

This append-SQL does the same:
INSERT INTO tblAutonum ( autonum )
SELECT 1000 AS Expr1;

New records will get 1001, etc

It's even possible to "append" afterwards a lower value:
INSERT INTO tblAutonum ( autonum )
SELECT 100 AS Expr1;

After this and the autonumber will continue with 101 etc. However when an existing value is found, a message is displayed that the record can't be added due to a duplicate key value.....

Again proof that it's dangerous to fool around with this mechanism !

Just a selfcontrolled number as mentioned by paaky, believer and me is the safest way. The FORMAT and or query with adding +1000 is the quickest way.

nico5038,

so it's possible in Access to insert particular value to autonum field and make it start from new position. Thanks for valuable information!

What happens if the next number 9999 and I try to insert value 10?

I am hoping M$ would add starting value and increment to properties of autonumber because so many people would like to change them. Or perhaps a customized sequence which could be used as field source (like in Oracle).

paasky

Paasky,

The above mentioned sequence is what I did by using the append-query out of curiosity I tried a "lower" value.
And to my surprise Access had resetted the increment value too !

(This is under A2000, maybe your A97 acts different)

Your question:
So your 10 will insert if it's not there ad adding continues with 11!

(I'm still working with A97)

so it's possible to "restart" autonumber - and cause key conflict when it reaches the 9999, if that number already exists, huh?

correct, doesn't it under 97?

This does *not* appear to be the behavior under 97! (re-read my comments dated Sunday, March 19 2000 - 11:47AM EST)
And I'm glad... it's scary to think that you could have autonumbers ranging from 100-1000, insert a 10, and Access starts adding new records with 11... what happens when it gets to 100?

As stated when an existing value is reached it will hangup on the insert of a duplicate key!

I tried the same sequence with the update-query under 97 and found out that it will insert lower values but the next new table row gets an autonum with the highest value + 2.
So somewhere it counts the INSERT into and a gap is created in autonumber with the number of records inserted!

So once again: Working with changes to an autonum field is tricky business!

BTW: A2000 also refuses to accept 2 autonum fields in one table!


BRAUN still able to catch up ?

nico:  Who??? Oh yeah, the guy/gal that "aksed" the question...lol!  Haven't seen him for a couple of days now...

BRAUN, any news here? :o)

(paasky's on a clean-up mission!)

See how much information this thread contains, unfortunately it will be lost soon.

Braun: Where the heck are you at?

<echo>

<echo

<ech

<ec

<e

<

"Stayin' alive, stayin' alive, woo hoo hoo hoo, stayin' ali-i-i-i-v-e...."

Hi!

I'm a little tired of unanswered Q's that have been getting deleted.  Just the ones I'm involved in account for several hundred points...

Hi braun,

Long time no see !

Still need an arrangement for the autonumber? If so, I've a sample DB with a function to assign autonumbers, both "single" or combined with a year, year/month or so.
Drop me a mail if interested (click my name for my profile with e-mail address)

Nic;o)