Problem when I change the starting value of an autonumber field

[Guest]

Hi,

I'm using Access 2002.
I tried to change the starting value of an autonumber field. To do that, I
created a temporary table with the started value I want (and same fieldname)
and a appen query.
But I have one problem, the query doesn't want to change the value. A
messagebox says that 1 record is not added because of validation rule
violations.

How can I change this value?

Remy

 

[Guest]

Actually you have two problems. The biggest one is that you are misusing the
autonumber field. An autonumber field that is also the primary key is
gaurenteed to create a unique number when a new record is added to the table.
The number does not have to be sequential and can even be a negative number!

If you empty out the table then compact and repair, you can probably start
back from zero. You can also insert a record reusing a previously deleted
autonumber. Are you sure that the number you are attempting to insert is not
already in the table? Also there is more than one validation rule that can be
broken. For example the following works:

INSERT INTO tblAutonumbers ( Auto_test )
SELECT 1 AS Expr1;

But this one doesn't:
INSERT INTO tblAutonumbers ( Auto_test )
SELECT 1.1 AS Expr1;

This works:
INSERT INTO tblAutonumbers ( Auto_test )
SELECT "-2" AS Expr1;

You say "change the value" which is more like an update than append
statement. Please show us the SQL for your query so we can see what you are
attempting.

 

[Guest]

Well, thanks for your answer,

It is the first time i'm using access. The problem is that I want to create
some Purchase orders. The Order ID, (which is my purchase order number) is
the prymary key of my table and it is autonumber. I want it to start from
99549 (which is the number of the last purchase order of the company plus 1)
and I got no previous records.

Is it impossible to change the starting value of the primary key? If
it'spossible how?

Remy