Trying to find highest ID number +1 (1 Viewer)

Hi

I am trying to find the following when I am trying to add a new record to my database.

The first letter of student surname, the first letter of student forename - this part I have working correctly. What I am stuggling with is trying to find the highest StudentID and increasing the number by one.

I have copied what I have below....


Private Sub StudentID_Click()
If IsNull(Me.StudentID.Value) Then
Me.StudentID.Value = UCase(Left(Me.StudentSurname, 1)) & UCase(Left(Me.StudentForename, 1))
End If
End Sub


Any ideas?

you could try DMax

DMax("StudentID","MyTable","Instr(StudentID),Left( " & Me.StudentSurname & ", 1) & Left(" & Me.StudentForename & ",1))>0")

Where mytable is the name of your student table

Hi

Thanks very much, we have part of the code working - the only issue we are now having is getting the database to collect the max StudentID - but ignoring the first two digits as these are letters.

For example a student code is "DG1115" so we only want to collect the max numbers which are the last four digits.

Thanks again

Mid(DMax("StudentID","MyTable","Instr(StudentID),Left( " & Me.StudentSurname & ", 1) & Left(" & Me.StudentForename & ",1))>0"),3)

will give you just the number - i.e. ignore first two characters

Don't forget that this is finding the maximum as a string so AA21 is greater than AA123

You might find it easier to keep the two parts in separate fields, and concat them together to display