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.

Put an autonumber field named ID in your table. Put a text field named ID2 in your table. Create a form bound to the table. Lock & disable the textbox bound to ID2. Add a BeforeUpdate event to the form with this code:

This will automatically populate ID2 field with what you need when a new record is entered.

Another point: Rather than have another field based on the ID, it's probably more correct to display the ID value in the format you want (using the formula above). That way you only save the ID field in your table.

Thank you for your reply. I tried following your advise but unfortunately I can't get it to work. I'd highly appreciate if you could kindly make an access database with a table with your commands in it, so that I could have a look and see what I'm doing wrong.

access 2007 constraint

constraint default value in access 2007 (which is the one i am proposing) is something i cannot see because currently i dont have access installed.
however, it is close to constraint default value of ansi sql.

Then use A09, B01, etc - then they will sort they way you want them to.

However, A10 will ALWAYS come before A9 because it will sort on the A1 through A9 and disregard the second digit. Thats why you need to make all number two digit numbers and then they will sort properly.