Autonumber Field Issue

[rhubarb]

Hello.

I have a field which was of type autonumber and is the PK in my table. The database crashed and I was able to recover an older version but now my PK field is a Number instead of AutoNumber. I suppose because my back-end has data in it. When my front-end form is loaded with the textbox that relies on this autonumber field it does not auto populate anymore with the next available number which is an issue. The user entering information will not be able to know the next unique number.

Any ideas how to resolve?

Thanks,

[rhubarb]

I've removed PK constraint and deleted field in my table. Then I re-created as autonumber and assigned the same field name. Still sorting out but this is a good start.

[Rawb]

If there are no "missing" values for the autonumber field, then you can get things back without too much trouble. If you are missing any numbers, however, things get complex pretty quickly..

If, you have no missing/deleted Records, you can do the following (for clarity, I will be calling your original Table Table1 and the new Table Table2):

1. Right-click Table1 in the Navigation Pane and choose "Copy"
2. Right-click in a blank area of the Navigation Pane and choose "Paste".
3. Name your Table Table2 and make sure "Structure Only" is selected.
4. Open Table2 in Design View and change your PK from "Number" to "Autonumber".
5. Save the changes and close the Table2.
6. Create an Query in Design View and select Table1 as the only source.
7. Change the Query to an Append Query and select Table2 as destination.
8. Add all the Columns from Table1 to the Column list at the bottom of your Query.
9. Set the Sort on the PK of Table1 to "Ascending".
10. Clear (if necessary) anything from Append To on the PK of Table1.
11. Click Run.
12. Close the Query, you shouldn't need to save it.
13. Break any Relationships between Table1 and the rest of your Tables.
14. Delete Table1.
15. Rename Table2 to Table1.
16. Recreate your Relationships using the new Table1.

Of course, as always back up your database before starting this process!