matching Auto-number (primary key) to Record number

[NMP]

I recently created a db which begins the auto-number at 125. Unfortunately
for my intention, the record number for 125 starts at number 1. Is there a
way to begin the record number at 125 to match the auto-number? If so,
please advise.

Thanks ahead of time!

 

[Douglas J. Steele]

No, nor is there any reason to do so.

Seriously, the value of the Autonumber field is totally meaningless.

 

[Wayne-I-M]

Hi

Don't worry about the autonumber. You should not refer to it other than to
define the record. In general autonumber are only used to produce a unique
reference (field) for a record - this can be used for the developer to create
relationships and other links plus as a method of stipulating a distinct
record. The users normally don't even know the autonumber exists.

If you really want a number that follows on from the last record use
something like on a form

If Me.NewRecord Then
Me.PrimaryField = Nz(DMax("PrimaryField", "TableName"), 0) + 1
End If

Good luck

 

[NMP]

I have novice users who are entering information in a form template that I
created. The feedback received is that it would be much easier for them to
be able to enter speficic form numbers (auto-numbers) in the record number
field to pull the correct forms so that info can be updated. (e.g. We have
300 auto-numbered records/forms and the user wants to find form 256 in the
form view. Ideally, they would enter 256 in the record field located at the
bottom of the form to pull up the correct form.) Currently, they must scroll
through various forms to get to the ones that they want to update.

Feedback/help is greatly appreciated.

 

[Douglas J. Steele]

Well, there's nothing you can do to renumber Autonumber fields. If it
matters (it's actually normal not to let the users see the value of
Autonumber fields), perhaps you need to use something other than an
Autonumber.

Can you not provide them with an alternate method of navigation? For
example, you could create a combo box that shows them the name of the forms,
and take them to that form, rather than making them enter a number.

 

[NMP]

Thanks for the feedback. I should not have deleted the initial 124 records
so that the record counts would match the auto-numbers. I guess there's no
way to re-insert those records.

Again, thanks for the help. I'll go off on my merry way.

 

[Rick Brandt]

Thanks for the feedback. I should not have deleted the initial 124
records so that the record counts would match the auto-numbers. I
guess there's no way to re-insert those records.

Again, thanks for the help. I'll go off on my merry way.

Even if you hadn't done that there is no reliable way to keep the numbers in
synch.

If you start the creation of a new record and then change your mind and
press Escape twice to cancel it you have used an AutoNumber position.

If you run an append query and cancel it at the prompt "You are about to
insert 5000 records. Do you want to continue?" you will still consume all
5000 of those AutoNumber positions.

Trust what you have read here. You CANNOT do what you are asking to do with
AutoNumbers. You could do so with a code-driven assignment of numbers in an
application that either prohibited deletions or else re-used numbers on
deleted records (which is another very questionable thing to do).