Autonumber Generator

[NewCoder_31]

Hi, I am working on my project and your HELP guys is really appreciated. I have a STUDENT NUMBER textbox, and what I want is when the form is open, IT WILL AUTOMATICALLY ASSIGN student number so that the registrar will not bother anymore to encode the student number...Is this possible?...Thanks

[Reverend Jim]

I'll assume that you are using a database. You can select the current maximum student number by

SELECT MAX(StudentNo)

Therefore the next available number would be

SELECT MAX(StudentNo)+1 AS NextStudentNo

[NewCoder_31]

Will I write this in the textbox of the student number or in Form_Load?....Thank you Sir Noli

[Reverend Jim]

If you want the app to start with a new student by default then yes, but I'd have a "NEW STUDENT" button and put the code there. If NEW STUDENT displays a new form then you can put it in that form load event handler.

[Hamza Saleem]

This is actually a sql query to select max records from the database. For this you have to open a new recordset maybe. Better you can use

RS.Recordcount + 1

[NewCoder_31]

Guys im connected to the dbase created by Visual Data Manager thru Data Control...Do you think I can achieve what I want to happen using this setup?

[Hamza Saleem]

I would suggest you to use ADODB data control. Its more faster and prefect. anyway if you want to go with dao then here it is:
make a new data control for counting your records. put this query in your data controls "recordsource" property box

SELECT COUNT(*) FROM tablename;

[Reverend Jim]

@Hamza - SELECT COUNT(*) is not the same as SELECT MAX(StudentNo). StudentNo is likely not tied to the number of records.

[NewCoder_31]

Thanks to both of you, Im glad reading your suggestions...Sir Hamza, how about the other controls?...Do i have to code in the form?....

[Hamza Saleem]

@Jim: SELECT COUNT() is counting the total records in the table while SELECT Max(StudentNo) is getting the maximum value in the field "StudentNo" What If he use ST01-ST02-ST03 like this. then your query doesn't work.that's why i suggested to use count().

[Hamza Saleem]

@NewCoder: I don't know about your other controls. If you want any suggestion from us. Just post the part of your code with a screenshot if there is any error coming up. We'll try to figure it out ! But firstly you have to try it urself. Surf google, you will find lots of examples and codes there.

[Reverend Jim]

@Hamza, lacking more detail I made the assumption that Student Number was actually a number.

[NewCoder_31]

Thanks to both of you guys...you are all brilliant and helpful...im going to try this first, then ill get back here as soon as I am done..Thanks again

[Aslam Mansoor]

Are you using a running number (Such as Sequence Number) on the table to saving records? If so how do you do it?If you are using the Autonumber, it will give problems when you happened to Delete records on the said table.

[NewCoder_31]

Yes, its a running number starts at 2014-001
What I want to happen is that everytime I open the form that contains the student number field, it will automatically start at the next availble number...I didnt use the AUTONUMBER IN ACCESS..TNX

[Reverend Jim]

In that case you can't use my suggestion because StudentNo is not actually a number. Using the format ####-## you'd have to store it as a string rather than a number. Are there any special rules to be used in generating the number? For example, are the first four digits supposed to be the year that the student was registered or is it just a coincidence that it happens to be 2014 in your example? If it is the year, what do you do when you have more than 999 students register in a given year?