Changing Text Field to Autonumber Field

[JWeaver]

I have been asked to help get something changed on a database (someone else
created it). The database has 4 tables, all of which have a Primary Key
field named WorkerID set as a Text field for 3 characters. The user must
enter a number into the field when entering new employees which is a problem
since they do not know what the last number used and will get an error if
they enter one that has already been used.

How can I fix this so that the tables will automatically assign a number to
new entries in the tables? The tables are set up so that relationships exist
between all of them on this WorkerID field.

I have gotten help here in the past with other problems and know that you
folks provide excellent guidance. Looking forward to seeing what you suggest
for me to do!

Thanks!!!

 

[Klatuu]

You say it is a 3 character text field and you enter numbers, So I am
assuming it has values like 001, 002, 003, etc.
If this is correct, then you can find the next available number using the
DMax function when you want to add a new record:

Function NextWorkerID() As String
Dim strNextNum As String

strNextNum = Nz(DMax("WorkerID", "SomeTable"), "000")
NextWorkderID = Fomat(Clng(strNextNum) + 1, "000")
End Function