First off to explain my situation, i am working on my computer project where i analyze a paper based system and computerize it. I have made my project in Access 2003. For every table primary key is an auto assigned ID but since i am not willing to use the Autonumber type but rather a complicated ID, i have used the text field. For instance in the table Student, student id is a text field to make the user follow the format SID????? as the student id value i set it as the validation rule.

The first value in the ID would be SID00001

second is SID00002 and so on......Now this is a bit annoying to type and i would like it to be auto generated. Now what i would like is when the user is adding a student through a form, access could grab the number of the last field in the table add 1 to it, and use that to generate the student id automatically. How can i achieve that? for example if there are 82 records in the students table the the new record id would be SID00083 or if there are 172 records then new value SID00173. I know i might as well just use autonumber and get the current record number and keep the field hidden or something like that but i would rather prefer the above method. Please do keep note of the zeros before the number and the length of the ID which is 8. Anyhelp would be really appreciated

Suggestion: Use an AutoNumber and just prefix it with SID everywhere you display it. I know this isn't really a solution to your problem. But I can't see why you would need the primary key to begin SID, after all, its not really used for anything other than in the background of the database. You could have a primary key AutoNumber, and a student ID key, that is a random number with SID prefixed to it, perhaps. I'm not really sure how though.

Simply, I wouldn't do it the way your trying to do it. The primary key should just be an AutoNumber.

thanks man found it

http://www.databasedev.co.uk/add_prefix.html

the prefix method all the way, thanks again