counter/auto increment in a subform

[Guest]

How can I add a counter to a subform, not an autonumber. When the record in
the main form changes, the counter will display the appropriate number of
rows in the subform. If I add a record to the subform, the field will
increment by 1.

thanks

 

[Allen Browne]

This example assumes the main form is for entering CD albums, and the
subform for entering the tracks. For each new album, you want the subform to
start from 1, and provide the TrackNo automatically.

The code fires as soon as the user begins entering a record in the subform.
It checks that the main form is not at a new record, looks up the highest
track number used so far in the table, using zero if none found. It then
adds 1, and assigns the result to the TrackNo field.

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