Link to home
Create AccountLog in
Avatar of newtt13_newtt13
newtt13_newtt13Flag for United States of America

asked on

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.

Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

If the last number for 2011 was md-11-0543
what would be the first number for md-12-
Avatar of newtt13_newtt13

ASKER

0001 would be the new record for the next year.so md-12-0001
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok so I dont know a thing about VB.  What if I do not have a field called txtDept?  
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.
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?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
For a 2-digit year string, use Format(Date(), "yy")
Works great! Thanks