How can I have consecutive numbers in Access

[Guest]

I have used this Event Procedure in Before Insert:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!textboxname = NZ(DMax("TraineeID", "Trainees")) + 1
End Sub

But it doesn't work and I don't understand why?

I also tried:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Trainee ID = NZ(DMax("TraineeID", "Trainees")) + 1
End Sub

What am I doing wrong?

 

[Rick Brandt]

I have used this Event Procedure in Before Insert:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!textboxname = NZ(DMax("TraineeID", "Trainees")) + 1
End Sub

But it doesn't work and I don't understand why?

Is textboxname just and example here? Is that the actual name of the TextBox?

I also tried:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Trainee ID = NZ(DMax("TraineeID", "Trainees")) + 1
End Sub

What am I doing wrong?

If a field name has a space in it then you need square brackets around it.

Me![Trainee ID] = NZ(DMax("TraineeID", "Trainees")) + 1

....but since you don't show a space in the name in the DMax() function then I
have to ask which spelling is correct? They should agree.

 

[Jamie Collins]

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Trainee ID = NZ(DMax("TraineeID", "Trainees")) + 1
End Sub

What am I doing wrong?

This is the 'design' group and your subject line is of more relevance
than the body text <g>.

SQL has but one data structure -- the table -- so the required
solution usually involves putting data in a table. A standard trick to
having a consecutive numbers is to create a permanent auxiliary
Sequence table of integers. Since this table is controlled by you, the
designer, you can be reasonably assured that you will have consecutive
numbers, so finding the next available number is the simple matter of
a table join e.g.

SELECT MIN(S1.seq)
FROM Sequence AS S1
LEFT JOIN Trainees AS T1
ON S1.seq = T1.TraineeID
WHERE T1.TraineeID IS NULL;

This approach is more robust than your DMax + 1 approach e.g. consider
what would happen if someone inserted the value 2147483647 into the
TraineeID column ;-)

Jamie.

--