Incremental field, last record +1, i.e., 2456. 2457, 2458; How?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lynn Hartwig - 15 Jun 2006 20:28 GMT

Need to have a number field that increases 1 each record. Access 2002 will
allow only 1 Autonumber, and since I already have 1000+ records, don't want
to start from scratch. The book mentions Last(), which if it worked, could
be like Date()+5, which works well, but Last() doesn't work in Form or Tables.
I have an employee number, increasing 1 each time but starting at a
four-digit number. I have no missing numbers. Just want it to start filling
it in when I make a new record instead of typing it in. Tried Key ID + ##,
makes sense since that grows 1 each record, but won't let me. Ex: In
Properties, Default Value field, =ID+999, if the very first record was 1000
and grows from there. Any advice on how to number existing records starting
at other-than-1? Appending leaves me with 2 fields occupying the same place
on forms.

Reply to this Message

KARL DEWEY - 15 Jun 2006 20:54 GMT

Build a separate query to use in a form as record source of an unbound
textbox. Set the default for your textbox for the employee number using the
unbound textbox.

> Need to have a number field that increases 1 each record. Access 2002 will
> allow only 1 Autonumber, and since I already have 1000+ records, don't want
[quoted text clipped - 8 lines]
> at other-than-1? Appending leaves me with 2 fields occupying the same place
> on forms.

Reply to this Message

Tim Ferguson - 15 Jun 2006 23:27 GMT

> Need to have a number field that increases 1 each record. Access 2002
> will allow only 1 Autonumber, and since I already have 1000+ records,
> don't want to start from scratch.

If you already have an autonumber field in the table, why do you need
another identifier?

If you don't need to identify the records, but just to number each line
in a query, then you can use a ranking query to put the numbers in.

The default value wouldn't help with this: at the time of creating a new
record (which is when the default values are evaluated) there is no ID
field to add 1 to. You can do this kind of thing in a form control, but
of course that's not robust if people have other kinds of access to the
tables.

It's not a very specific answer, but I am having difficulty imagining
what your problem might be.

B Wishes

Tim F

Reply to this Message