matching Auto-number (primary key) to Record number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NMP - 12 Jun 2007 17:04 GMT

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!

Signature

NMP

Reply to this Message

Douglas J. Steele - 12 Jun 2007 17:15 GMT

No, nor is there any reason to do so.

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 3 lines]
>
> Thanks ahead of time!

Reply to this Message

NMP - 12 Jun 2007 17:41 GMT

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.

Signature

NMP

> No, nor is there any reason to do so.
>
[quoted text clipped - 7 lines]
> >
> > Thanks ahead of time!

Reply to this Message

Douglas J. Steele - 12 Jun 2007 17:51 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 24 lines]
>> >
>> > Thanks ahead of time!

Reply to this Message

NMP - 12 Jun 2007 18:58 GMT

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.

Signature

NMP

> 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
[quoted text clipped - 33 lines]
> >> >
> >> > Thanks ahead of time!

Reply to this Message

Rick Brandt - 12 Jun 2007 19:05 GMT

> 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).

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Wayne-I-M - 12 Jun 2007 17:19 GMT

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

Signature

Wayne
Manchester, England.

> 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!

Reply to this Message