Changing Text Field to Autonumber Field

  • Thread starter Thread starter JWeaver
  • Start date Start date
J

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!!!
 
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
 
Back
Top