Managing Sequence numbers between 3 sub forms on 3 tabs populated bythe same table

[DaveJJ]

I have the following code that adds sequence numbers to a single table
that populates 3 sub-forms that have been placed on three separate
tabs on the main form. This code runs in the current event of each
sub-form but the first record in each sub-form always starts with
'one' even when there are more then one related records in the table.
Usually the second record that is entered in either sub-form then
picks up the correct sequence number. How can I get the correct
sequence number when I move from tab to tab. I don't want the
sequence numbers to start over when switch from sub-form to sub-form
(tab to tab). I want the sequence numbers to be continuous.

Private Sub Form_Current()

On Error Resume Next
Dim x As Integer

x = DCount("*", "tblRouting", "[JobEnvelopeNo] = " & Forms!
frmCustomMain!txtID)

If IsNull(x) Then
Me!txtSeqNo.DefaultValue = 1
Else
Me!txtSeqNo.DefaultValue = x + 1
End If

End Sub

 

[Marshall Barton]

I have the following code that adds sequence numbers to a single table
that populates 3 sub-forms that have been placed on three separate
tabs on the main form. This code runs in the current event of each
sub-form but the first record in each sub-form always starts with
'one' even when there are more then one related records in the table.
Usually the second record that is entered in either sub-form then
picks up the correct sequence number. How can I get the correct
sequence number when I move from tab to tab. I don't want the
sequence numbers to start over when switch from sub-form to sub-form
(tab to tab). I want the sequence numbers to be continuous.

Private Sub Form_Current()

On Error Resume Next
Dim x As Integer

x = DCount("*", "tblRouting", "[JobEnvelopeNo] = " & Forms!
frmCustomMain!txtID)

If IsNull(x) Then
Me!txtSeqNo.DefaultValue = 1
Else
Me!txtSeqNo.DefaultValue = x + 1
End If

End Sub

I suspect that there may be a timing issue of when the
Current event is triggered. Try placing a line like:
Debug.Print Me.Name & " Current " & Parent.txtID, x
at the end of each subform's and the main form's Current
event procedure.

Also add a line like:
Debug.Print "Tab page " & Me.thetabcontrol
to the tab control's Change event procedure.

Then go through your test and look at the Immediate window
to see if things are happening in the order you expect.