Set autonumber to start from

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

Allen Browne (http://allenbrowne.com/ser-26.html) has on his website a tip
to reset the autonumber to start at a specific number. I would like to use
that, but it is not well explained how to use it. I copied the code into a
module and tried to use Call SetAutoNumber("TblClient", 7500) in the
intermediate window, but it doesn't do anything.

Does anyone know how to use this?
Anne
 
What are you expecting the result to be? His subroutine just sets the
autonumber to your desired value, so that the first record you then put into
the table has the desired autonumber value. His subroutine will not leave
any "visible" trace of its work until you then add a new record to that
table (which you need to do before you compact the database after running
his subroutine).
 
this might do it when you use plain access

table : Test
Fields : Test_ID autonumber, Test_Name (Text)

INSERT INTO Test (Test_ID,Test_Name) VALUES (7500,"Dummy")

Then you just delete the dummy record and the next insert will be at 7501

- Raoul
 
I use a pourID and there are existing pour numbers, which are consecutive
numbers, which right now are around 2500. I thought that at the time, when I
join this new part of the database with the rest, I would like the starting
number to be the next pour number.
Anne
I could set the starting pour number in the table, with a dummy pour, but I
don't know how else offer the next available number on a form when entering
a new pour.
Anne
 
You may be confusing Allen's process with a process to get the next
sequential number for a unique key value.

If you're using an autonumber field, then run Allen's process to get the
autonumber to the desired value, and then the next record will use the next
value.

However, if the field with the value is not an autonumber field, then you
must use an expression to get the next value for the new record. You can do
this in various ways, one of which is to use the DefaultValue property of a
textbox that is bound to that field:
=Nz(DMax("FieldName", "TableName"),0) + 1

Or you can use the form's BeforeInsert event, or the form's BeforeUpdate
event, to run code that uses a similar expression in order to set the value
of the control that is bound to that field.
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top