How to add 1 (one) to the default value

[Guest]

I have a sub form where I enter records. It has a number field where I number
each record. Usually starting of 1 and then in ascending order. Is there a
way of defaulting this number?
If the field is null or 0, then the first number defaulted would be 1 and
then the next number would default for the next record. However if I started
with say 6 then 7 would default next. So the default value would always be
one more than the last entry

I hope someone can help

Nick

 

[Allen Browne]

Instead of Default Value, use the BeforeInsert (or BeforeUpdate) event of
the form to supply the next number.

This example assumes you have your CD album in the main form, the tracks in
the subform, and you want to automatically insert the next track number for
the album:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter an album in the main form first."
Else
strWhere = "AlbumID = " & Me.Parent!AlbumID
Me.TrackID = Nz(DMax("TrackID", "tblTrack", strWhere), 0) + 1
End If
End Sub

 

[Allen Browne]

The approach will work for a main form.

You can use DMax() to get the highest value used so far, and add 1.

You still use Form_BeforeInsert.