Hi all,
The code below is coming from the solution previously given, fair enough that was excellent, but last night when I was now doing an extensive testing, I discovered that the VBA Code below sometimes will start numbering rows in the subform from 0,1,2,3 ,4 etc instead of always starting from 1,2,3,4 etc depending to the number of rows in a subform.
Here is the original code
Now I'm trying to amend it to ALWAYS start numbering rows no matter the situation is from 1,2,3, 4 etc depending to the number of rows that are in the subform.
VBA amended version, but not sure whether it will achieve the above requirements
It should never start from 0 but from 1 incrementing up to the end of the last row
The code below is coming from the solution previously given, fair enough that was excellent, but last night when I was now doing an extensive testing, I discovered that the VBA Code below sometimes will start numbering rows in the subform from 0,1,2,3 ,4 etc instead of always starting from 1,2,3,4 etc depending to the number of rows in a subform.
Here is the original code
Code:
Option Compare Database
Option Explicit
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long
Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then
LastSequenceNumber = 0
Else
rst.MoveFirst
Do While Not rst.EOF
If Nz(rst!SequenceNumber, 0) > LastSequenceNumber Then
LastSequenceNumber = Nz(rst!SequenceNumber, 0)
End If
rst.MoveNext
Loop
End If
Me!SequenceNumber = LastSequenceNumber + 1
Exit_Error_Handler:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
End Sub
Now I'm trying to amend it to ALWAYS start numbering rows no matter the situation is from 1,2,3, 4 etc depending to the number of rows that are in the subform.
VBA amended version, but not sure whether it will achieve the above requirements
Code:
Option Compare Database
Option Explicit
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long
Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then
LastSequenceNumber = 0
ElseIf rst.RecordCount <> 0 Then
LastSequenceNumber = 1
rst.MoveFirst
Do While Not rst.EOF
If Nz(rst!SequenceNumber, 0) > LastSequenceNumber Then
LastSequenceNumber = Nz(rst!SequenceNumber, 0)
End If
rst.MoveNext
Loop
End If
Me!SequenceNumber = LastSequenceNumber + 1
Exit_Error_Handler:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
End Sub
It should never start from 0 but from 1 incrementing up to the end of the last row