Auto increment text field Access 2007
I need code to auto increment a text field. The field is alpha numeric. The structure of my field is as follows:
MD-11-0001
MD-11-0002
MD-11-0003
and so on.
The first part is the office, the second part is the 2 digit year and the third is the number that I need to increment. I created a form with an add new record button. what I need that button to do is create a new record and find the highest numbered control number and add 1. Based on the above structure. Also, if possible it would be nice if the coding could add the 2digit year without my intervention. This is a multi user database, but the access is minimal. 3-4 users.
MD-11-0001
MD-11-0002
MD-11-0003
and so on.
The first part is the office, the second part is the 2 digit year and the third is the number that I need to increment. I created a form with an add new record button. what I need that button to do is create a new record and find the highest numbered control number and add 1. Based on the above structure. Also, if possible it would be nice if the coding could add the 2digit year without my intervention. This is a multi user database, but the access is minimal. 3-4 users.
ASKER
0001 would be the new record for the next year.so md-12-0001
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok so I dont know a thing about VB. What if I do not have a field called txtDept?
ASKER
Let me clarify that, the dept id field does not exist in my database. The dept name just gets typed within my control number field that I am trying to auto increment.
You don't need a Dept field in the table but you DO need a separate textbox on the form where the user enters the dept so that it can be used by the code to find the next number.
If there is a known list of dept codes you should create a table of these and use a combobox instead of a textbox on the form to allow the user to select from the list. That will ensure consistency and reduce errors.
If there is a known list of dept codes you should create a table of these and use a combobox instead of a textbox on the form to allow the user to select from the list. That will ensure consistency and reduce errors.
ASKER
peter57r,
you are a rockstar. I finally got the event procedure to create a new record and auto input the control number. However, the new record is coming up as MD-2005-0001. Any idea why the date is coming up as 2005? Also how can I get that condensed to a 2 digit year?
you are a rockstar. I finally got the event procedure to create a new record and auto input the control number. However, the new record is coming up as MD-2005-0001. Any idea why the date is coming up as 2005? Also how can I get that condensed to a 2 digit year?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For a 2-digit year string, use Format(Date(), "yy")
ASKER
Works great! Thanks
what would be the first number for md-12-