AutoNumbers

[tarena]

I imported data from an excel spreadsheet into an access table, it contains clock numbers, employee names and their shifts. I made the clock numbers field the primary key and I want this field to automatically populate with the next number. Can you indicate the number that you want the AutoNumber to begin with? It is not taking the next number from the numbers that were imported from excel.

Thank you
Tarena

 

[Remou]

It is not wise to use autonumber for any field that means anything - you can create your own number. If there are only a few people using the database, a simple look-up table should suit, for example:

Set rs=CurrentDB.OpenRecordset("tblCounters")

rs.Edit
rs!ClockNumber=rs!ClockNumber+1
rs.Update

Me.ClockNumber=rs!ClockNumber

 

[rac2]

Its gonna be tricky but it may be possible.

I find that a column which already has data in it cannot be redefined to be an Autonumber column. That can be done on a column in an empty table, but not after values have been added in the column. Whether that also means the table is empty of rows, I havent checked. This seems to imply that you must specify that the column with the clock numbers must be defined as an Autonumber column before you import the data from the spreadsheet.

Then using Access Help I searched on

autonumber intial value

and found a nice item titled "Change the starting value of an AutoNumber field". That article led me to conclude that it might be possible to do what you wish to do.

So. Go to File->Get external data->Import .
Select the spreadsheet file (.xls) to import.
The Import Wizard will show an option to store the imported data in a new table or in an existing table.

At this point you can test whether the clock number can be imported into an existing table which has the column for clock number defined as an Autonumber column. If you can, then I think the Help article can be applied to your problem.

If you cant do that, if you get an error message saying that you cannot store data in an Autonumber column then try the new table approach.

With a new table the next step in the Wizard has Field Options. The Indexed: item should be Yes(No Duplicates).

And so forth.

Or maybe you have solved the problem already. Let us know.