Creating an incremental field not an "autonumber field"

[A kinason]

I have a small database which is use for my small school. On my admission form, each student is assigned a "StudentID" which is of this format (VIP001, VIP002, VIP003 etc).



The problem is that at any time i have to type in the student Id and i have discovered that this causes ommissions like entering VIP012, when the next students comes, the last ID is forgotten and one might enter VIP014.

Is ther a way of having the next student id appear on the form automatically when the current entry is validated?

I will be vey grateful receiving help from you.

[Missinglinq]

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
 
  If RecordsetClone.RecordCount = 0 Then
     Me.StudentID = "VIP001"
  Else
     Me.StudentID = "VIP" & Format(DMax("Mid([StudentID],4)", "tblStudents") + 1, "000")
  End If

End If

End Sub

Replace tblStudents with the actual name of the Table that holds the data. Ditto with StudentID, unless that is the actual Field Name.

Being in the Form_BeforeUpdate, the number will increment at the last possible second before the Record is saved. This sounds like a single-user app, from your description, and so it doesn't really matter, but in a multi-user app you want the number to be assigned at the very last moment, to decrease the possibility of two users getting the same number; it's probably good to get in the habit of doing it like this.

Linq ;0)>

[A kinason]

i have entered the code but when i validate, it stores only VIP in the StudentId field of the table Student.

[A kinason]

i have entered the code in the AfterUpdate Property of the form but when i validate, it stores only VIP in the StudentId field of the table Student.

[Missinglinq]

Perhaps you should try putting it in the Form_BeforeUpdate event, as you were shown, in the example!

Also, remember to replace tblStudents, in the code, with the actual name of your Table that holds this data.

Linq ;0)>

[A kinason]

HERE IS THE CODE I INSERTED IN THE BEFORE UPDATE EVENT OF THE FORM. THE TABLE NAME IS "Student" AND THE StudentId is in column 2 of this table

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

If RecordsetClone.RecordCount = 0 Then
Me.StudentId = "VIP001"
Else
Me.StudentId = "VIP" & Format(DMax("Mid([StudentId],4)", "Student") + 1, "000")
End If

End If

End Sub
End Sub

[Missinglinq]

And...what happened?

First off, you need to remove the second Sub End statement. With that in place, I don't understand how your code ran at all, without an error being popped.

Then go into the Table and either delete the Records that only showed 'VIP" in the StudentID or edit them to show proper data, and trying running the code again. This is tested code, and shouldn't be a problem.

Linq ;0)>

[A kinason]

Thanks Missinglinq its working. Am so happy