Autonumber assistance (Solved)

[Michael Abrams]

Issues database with RecordNum as Autonumber.
When an issue is resolved, I export it to an Archive Database (mistake number 1 – I should have a flag for resolved.)
On occasion I need to export a couple of records from the Archive database back into the Issues database. Most of the time it works fine.
But this time the Autonumber for new records is starting with the higher number of the records re-imported (Rec# 337102) plus 1 - the highest number in the database is 337731.
When a new record is initialized, the recordnum field is auto populated with (what I thought) was the highest numbered record plus 1.
Therefore, I expected the new record number to be 337732.

An error message pops up saying I am trying to use a duplicate value in the recordnum field - 337102
How do I get the new records to initialize with the highest numbered record plus 1 ?

Thank you !
Michael

[HansV]

You may be able to correct it by compacting and repairing the database (Database Tools > Compact and Repair Database).
If that doesn't help:

If the autonumber field is NOT used in relationships with other tables:

- Press Ctrl+G to activate the Immediate window in the Visual Basic Editor.
- Copy the following line into the Immediate window:

Currentdb.Execute "ALTER TABLE [TableName] ALTER COLUMN [AutoNumberField] COUNTER(337732,1)"

- Substitute the correct table name and field name.
- With the insertion point anywhere in the line, press Enter.

If the AutoNumber field is included in relationships with other tables:

- Press Ctrl+G to activate the Immediate window in the Visual Basic Editor.
- Copy the following line into the Immediate window:

Currentdb.Execute "INSERT INTO [TableName] ([AutoNumberField]) VALUES (337732)"

- Substitute the correct table name and field name.
- With the insertion point anywhere in the line, press Enter.
- Switch back to Access.
- There should already be a record in the table with AutoNumber value 337732. You can use this record as a new record.

[Michael Abrams]

Whew !! Thank you so much HansV - this is really just amazing .

Michael