New Record - AutoNumber

[backfromthebush]

Hi all,

I am wondering if there is a way to autopopulate a number field in Access in the following manner...
The field is called InvoiceNumber, and it is a simple integer (no letters or anything else tacked on), and for every new record it increments by 1 according to what the current max value is within the table.
I don't want to rely on the AutoNumber option as I want to be able to control and change these numbers if I wish so.
Any ideas?

 

[jackd]

Anyone interested may want to read this article by Allen Browne on fixing autonumbers
http://allenbrowne.com/ser-40.html

 

[xenou]

To force it to start at a particular number N, you'd have to 1) delete and re-create the table object, 2) have a make-table query which copies only the top N-1 records into a new table, and then use that new table, or 3) copy only the table's structure into a new table, and then use an append query which has all the fields except the auto-number field. Obviously these are all pretty impractical.

Or Compact and Repair (Where N-1 is the current max ID) ...

It's somewhat disconcerting ... I guess I could say I don't usually rely on Autonumber as a meaningful ID number (such as an invoice number). But sometimes I do. However, in all such cases the table that holds this ID number is a permanent table and the records, once given an ID, are never deleted nor otherwise removed from the table. I don't use replication with Access so that's not a concern to me. My Autonumber field types are always Number/Long Integer with New Values set to Increment (by default ... I wasn't aware that there was any other option but now I've learned something new today!)

ξ

 

[boblarson]

My experiments have concluded that Access always retains the last number generated,

If the table is cleared and the database compacted, or one of your other three options you listed, it will reset to start at zero if set to Increment).

I've never see an autonumber less than 1 before...

A quick example is to replicate your database. Or just change the autonumber field's default INCREMENT to RANDOM and you'll see some.

Autonumbers to me are like allocating memory in C/C++/C#: it doesn't matter what the actual address of the memory is, as long as you have the address, you can read and release the memory when you need to.

And that's the major point of it all - Autonumbers should only be used to generate a UNIQUE number (a surrogate key is the main usage) and let the system handle it all. One shouldn't care what number the autonumber is, as long as it does its job.