Need to generate sequential number conditioned to other fields

[mahfuz_geo28]

Hi, I'm trying to build a data entry form for an alumni database. I want to generate an unique ID for each alumni. The id is 8 digit alphanumeric. The last two digits is a sequential serial number which should restart from 01 for each new class and gender group within the class. Not sure if I'm making any sense, but would be delighted if could get some idea or simple VBA code. Thanks in advance.

[ranman256]

Youre making work you don't have to do.
add an ID column as autonumber. The table will create unique IDs for everyone. No programming.
Create your 'homemade' ID later in a query.

[mahfuz_geo28]

Thanks for your reply. I already have an autonumber field for primary id. I'm actually trying to use a query to make my homemade id. The issue is - I don't know how to get the count of previous male students (for example) in a class, I would use that count + 1 as the serial no for the next entry. My level of VBA is zero.

[orange]

Why do you need a homemade id?
What is its purpose?

If you have an autonumber, and Name, Gender.... of alumni, please describe the rationale for the homemade id.

[aytee111]

Only the last 2 digits are unique (is 2 going to be enough?). You will enter class and gender on the form giving you the first 6 characters. At some point in time you want to get the new number depending on the sequence of entries on the form. On the second of those fields, in the AfterUpdate event of that field, select Event Procedure and add this VBA Code (change names as necessary):
uniquenumber=CInt(DLookUp("Max(uniquenumber)","tab lename","Class='" & me!class & "' AND Gender='" & me!gender & "'")) + 1

[mahfuz_geo28]

The homemade id contains basic info about an alumni in it. For example just looking at the id one should be able to tell the alumnis batch #, gender, degree he received from the institution etc. Also, this is going to have a fixed number of digits. Auto numbering won't do that.

[mahfuz_geo28]

Two digits should be fine, there is always a maximum of 50 students in a class. Thanks for the code, I'll try it and let you know if I have question.

[orange]

just looking at the id one should be able to tell the alumnis batch #, gender, degree he received from the institution etc. Also, this is going to have a fixed number of digits

Be very cautious with self-made codification-- it invariably comes back to bite you. Relational database works very well with a simple design approach---1 fact 1 field. My suggestion, rethink your scheme.

If you are intent on some codification --design it, and test it (on paper) before programming. Trial and error is rarely a good strategy.
Good luck with your project.