Where does this code go wrong?

[Guest]

I have a main form with a subform. Each has its own table. They are not
linked. My aim is to stop at each subform record and run MakeChanges(),
which processes all the records in the main form table.

My immediate problem is that when intTick = 2 and MakeChanges() starts its
second run, the final GoToRecord command begins to advance the SUBFORM
pointer rather than the main form pointer. Then intTick2, which should go to
950, quickly exceeds intCount, which = 12.

I am self-taught in Access, so my overall coding may be clumsy. If you want
to offer a superior strategy, I'd welcome that, too.

==========================
Private Sub Label22_Click()
intCount = CurrentDb.OpenRecordset("AddrFixTbl").RecordCount '= 12
intCount2 = CurrentDb.OpenRecordset("ANDIAddrTbl").RecordCount '= 950
intTick = 1
While intTick <= intCount
With Forms!UpdateAddrsPt2.UpdateAddrsSubform.Form
strTxt2Fix = !AddrBadTxt
strFix = !AddrTxtFix
strLoc = !AddrBadTxtLoc
End With
MakeChanges
Me.UpdateAddrsSubform.SetFocus 'Focus on AddrFixTbl (subform)
DoCmd.GoToRecord , , acNext
intTick = intTick + 1
Wend
End Sub
----------------------------------
Private Sub MakeChanges()
Me.Form.SetFocus 'Focus on ANDIAddrTbl (main form)
intTick2 = 1 'Now on Record 1 in ANDIAddrTbl
While intTick2 <= intCount2
' If strLoc... Then
' DO STUFF IN HERE...
' End If
If intTick2 < intCount2 Then
DoCmd.GoToRecord , , acNext
End If
intTick2 = intTick2 + 1
Wend
DoCmd.GoToRecord , , acFirst 'CRASHES HERE WHEN intTick = 2 & intTick2
= 12
End Sub
==================================

Thanks.

David

 

[Guest]

Without the ability to test your code, I cannot be absolutely sure this is
the problem, but there is an error here. Record numbers start at 0, not a 1.
So, if you have a table with 12 records, the first is 0 and the last is 11.
The other will be 0 through 949.

 

[Guest]

Thanks, Klatuu, but I think that still doesn't solve my problem that
DoCmd.GoToRecord,,Next begins to advance the wrong pointer after the first
cycle.

 

[Guest]

I'm thinking maybe your SetFocus methods are not behaving as you expect.
Here is some info from VBA Editor Help that may give you useful info.

If a form contains controls for which the Enabled property is set to True,
you can't move the focus to the form itself. You can only move the focus to
controls on the form. In this case, if you try to use SetFocus to move the
focus to a form, the focus is set to the control on the form that last
received the focus.


Tip

You can use the SetFocus method to move the focus to a subform, which is a
type of control. You can also move the focus to a control on a subform by
using the SetFocus method twice, moving the focus first to the subform and
then to the control on the subform.