AutoNumber

[FLGuy]

I am converting a database from 2002 to 2007. The old databases first record
started out at 700. I would like to renumber these records starting at 1.
Then I would like to set AutoNumber to start with the number of the last
record. Is this possible to do? If so how would I go about doing this?

 

[Rod Plastow]

Hi,

Is the existing (v2002) data type also Autonumber? Is this a primary key
(or a foreign key come to that)? Are there any relationships from this
number to other tables? If so, is Cascade Update set for these relationships?

The answers will help me or others suggest a course of action.

 

[Keith Wilby]

I am converting a database from 2002 to 2007. The old databases first
record
started out at 700. I would like to renumber these records starting at 1.
Then I would like to set AutoNumber to start with the number of the last
record. Is this possible to do? If so how would I go about doing this?

An AutoNumber field is for internal indexing purposes and should be
transparent and meaningless to the developer and user alike. If you want to
number your records consecutively then consider using the DMax function as
the default value of a field:

nz(DMax("MyField","tblMyTable")+1,1)

Keith.
www.keithwilby.co.uk

 

[FLGuy]

The existing data type in (v2002) is an Autonumber field. This feild is not a
primary key or a foreign key. It is used to generate a ticket number for each
issue loged into the database. No, there are no relationships from this
number to any other table in the database.

 

[Keith Wilby]

The existing data type in (v2002) is an Autonumber field. This feild is
not a
primary key or a foreign key. It is used to generate a ticket number for
each
issue loged into the database. No, there are no relationships from this
number to any other table in the database.

You do realise that you can't rely on AutoNumber to be sequential don't you?
Do you care that there might be gaps in the numbering? How would you tell
the difference between a gap in the numbering and a record that did exist
and has been deleted? Do none of these issues concern you?

Keith.

 

[FLGuy]

Yes, I realise that the numbering my not be sequential. I am just using it to
create a unique number for each record. As for gaps because of records being
deleted. This is not a concern for me because nobody has access to delete
records except for me.

 

[Keith Wilby]

Yes, I realise that the numbering my not be sequential. I am just using it
to
create a unique number for each record. As for gaps because of records
being
deleted. This is not a concern for me because nobody has access to delete
records except for me.

All I can do is re-state the case for using DMax plus 1 in an ordinary
number field. It's *exactly* what you need based on your OP. You can
renumber your existing records using an update query and then DMax will
provide the next available number sequentially.

Keith.

 

[BruceM]

There will almost surely be gaps, regardless of whether records are deleted.
For instance, if you go to a new record, then navigate away from it, Access
regards the number as having been used, even though there is no record with
that number. That may not be a problem, but it is worth pointing out, I
think.