Create A Sequential Number In A SubForm For Each New Header Record
Occasionall you will need to provide a number from 1 to "n", where "n" is the number of detail lines in a sub-form. For example, if you have an application that has a header record for an order, and the subform detail contains each line item ordered, you may want to number the detail lines sequentially starting at 1 for each new order.
Surprisingly, this requires a little code to accomplish this.
To summarize the steps:
(1) If the user inserts a new line, the line number becomes the number of existing sub-form records + 1
(2) If the user edits an existing line, don't change the line number
(3) If the user deletes a line, then renumber the associated records in the source table sequentially and refresh the form.
(4)
There are only two events that need to be processed to accomplish the above goals
Here is a code example behind a sub-form to accomplish this goal:
Program Code
Private Sub Form_AfterDelConfirm(Status As Integer)
' *********************************************************
' If the user deletes a subform line, renumber all records
' in the source table starting at 1 so there will not
' be any missing sequence numbers
' *********************************************************
Dim db As Database
Dim recIn As Recordset
Dim strSQL As String
Dim intLineNumber As Integer
intLineNumber = 0
strSQL = "SELECT tblKilnDetail.KilnDetailID, tblKilnDetail.KilnHeaderID, tblKilnDetail.BatchLineNumber" & _
" FROM tblKilnDetail WHERE tblKilnDetail.KilnHeaderID=" & Me.KilnHeaderID & _
" ORDER BY tblKilnDetail.KilnDetailID;"
If Status <> acDeleteOK Then
Exit Sub
End If
Set db = CurrentDb()
Set recIn = db.OpenRecordset(strSQL)
If recIn.EOF Then
Exit Sub
End If
Do
intLineNumber = intLineNumber + 1
recIn.Edit
recIn!BatchLineNumber = intLineNumber
recIn.Update
recIn.MoveNext
Loop Until recIn.EOF
recIn.Close
Set recIn = Nothing
Set db = Nothing
Me.Requery
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
' *********************************************************
' If the user inserts a new detail line, assign the next
' sequential number. If the user was updating an
' existing line item, don't change the sequence number
' *********************************************************
Dim recIn As Recordset
Dim lngHighestBatchNumberOnDetail As Long
If Not Me.NewRecord Then
Exit Sub
End If
lngHighestBatchNumberOnDetail = 0
Set recIn = Me.Recordset
lngHighestBatchNumberOnDetail = recIn.RecordCount + 1
Me.Line = lngHighestBatchNumberOnDetail
Set recIn = Nothing
End Sub