Access -- Creating a formula or expression for auto numbering

[Guest]

I created a new table by importing from Excel. I choose my on primary key by
selecting the field that holds Patron ID numbers. I need to know how I can
generate a new ID number based on the already exiting ID numbers that came in
from Excel. At this moment, Access will not automatically give me the next
number in sequence. What do I do? HELP!!!

 

[Joseph Meehan]

I created a new table by importing from Excel. I choose my on primary key
by selecting the field that holds Patron ID numbers. I need to know how I
can generate a new ID number based on the already exiting ID numbers that
came in from Excel. At this moment, Access will not automatically give me
the next number in sequence. What do I do? HELP!!!

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[James Hahn]

What do you mean 'based on'? If you want new numbers to be automatically
calculated as the last number used plus 1 (for instance) then you need to
maintain a data record somewhere that contains the last used number. When
you add a new record, access the stored last used number, add 1 to it and
store it back.

There's no feature in Access that does this automatically.

 

[Jamie Collins]

I created a new table by importing from Excel. I choose my on primary key by
selecting the field that holds Patron ID numbers. I need to know how I can
generate a new ID number based on the already exiting ID numbers that came in
from Excel.

If you want to do this properly, see the following KB article:

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1
http://support.microsoft.com/default.aspx?scid=kb;en-us;240317

Jamie.

--