I have an autonumber field which is the primary key linked to other tables. I want to set the autonumber field to a lower number i.e I started at 90000 and and now have exceeded 99999, six digits wont work for me. I want to reset to 50000. I have tried the append method, but the autonumber still increments from the highest number 100004
I'd suggest that you not use an Autonumber for this purpose.
Autonumbers have one purpose, and one purpose ONLY: to provide an
(almost, except for occasional bugs) guaranteed unique identifier.
They are not editable and (as you can see) not readily controlled.
You might want instead to use a Long Integer field and use one of the
many VBA routines available to increment it. What is the largest value
currently in your table? Do you have a gap available in which to
insert numbers? What do you want to do with record 100003?