Error 3022 dupplicate record

[din2005]

Hi all,

For some apparent reason when i go to a new record it says that there is a duplicate record. When i check my autonumber field theres about 7000 records. now every time i click on a new record the autonumber has started at

1156 and so on and these records have been already done. its not continuing from the very last record which was 7003 and should increment 7004 and 7005 etc

But instead its gone to 1156 and increments to 1157 and so on which the records r already filled in.


Could somebody tell me how to set the autonumber towards the end. I have tried compacting the table and it still hasn't work.

I'm stuck

many thanks

 

[Crowley16]

in the autonumber properties, you can set the behaviour of new numbers to be incremental or random, choose incremental...

--------------------
Procrastinate Now!

 

[din2005]

its already on increment.... but the increment for some reason has started 1200 when it should continue from 7000
anyway to set increment back to 7000 and goes on 7001, 7002 etc

 

[Crowley16]

well, I suppose you can force the numbers to start from 7000+ by setting a format in there...

but the standard behaviour is to add from the end, irrespective of any missing records in between.

just how are these new records added? Do you have a function which adds records and that is setting the start numbers?

--------------------
Procrastinate Now!

 

[din2005]

i use this code to add a new record using a button

DoCmd.GoToRecord , , acNewRec

 

[Crowley16]

normally, I would use a sql string to insert records, this has always given me the highest autonumers...

--------------------
Procrastinate Now!

 

[earthandfire]

To force an Autonumber to start from a certain point run an Append Query followed by a Delete Query.

In table3 the last number in Field1 (autonumber field) was 800, but then needs to continue from 1001

INSERT INTO Table3 ( Field1 )
SELECT 1000 AS Expr1;


DELETE Table3.Field1
FROM Table3
WHERE (((Table3.Field1)=1000));


The next record entered will have 1001 in Field1


Hope this helps.

 

[TheAceMan1]

How are ya din2005 . . .

Perform a [blue]Compact & Repair[/blue] and then let us know what happens . . .

See Ya! . . . . . .

 

[PHV]

You may try something like this in the immediate window:
DoCmd.RunSQL "ALTER TABLE yourTable ALTER COLUMN yourautoNumber COUNTER(7004,1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886