If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Changing Text Field to Autonumber Field
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!!! -- JWeaver |
#2
|
|||
|
|||
Changing Text Field to Autonumber Field
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 -- Dave Hargis, Microsoft Access MVP "JWeaver" wrote: 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!!! -- JWeaver |
Thread Tools | |
Display Modes | |
|
|