how to navigate through records on a form based on a value in a text box?

[schniggeldorf]

I have a form that contains two subforms. On the main form, there's a textbox 'txtnInSubforms' that displays the total number of records in the two subforms. This calculation works fine.

What I want to do is create vba code that will automatically skip any records where there are no data in the subforms. That is, when the user opens the main form, if the subforms are empty (i.e. txtnInSubforms = 0), the code should automatically advance to the next record, repeating this until it finds a record where txtnInSubform <>0, or until reaching the end of all the records.

My code is as follows:
Private Sub Form_Current()
Dim skip As Boolean
Dim NotLast As Boolean

If txtnInSubforms.Value = 0 Then
skip = True
Else
skip = False
End If

If Me.Recordset.AbsolutePosition + 1 = Me.Recordset.RecordCount Then
NotLast = False
Else
NotLast = True
End If

If skip Then
If NotLast Then
DoCmd.GoToRecord , , acNext
End If
End If

End Sub

I've used message boxes to make certain everything was calculating properly, and that execution branches as it should. Everything seems fine. Nonetheless, when vba gets to the last record (i.e. NotLast = False) I get a "run time error 2105, can't go to the specified record" triggered by the DoCmd. This happens even though vba never reaches the DoCmd statement in this instance! How can it throw an error when my procedure to avoid the offending DoCmd seems to be working?

BTW, the final record in the recordset has txtnInSubforms=1, and skip=false, as it should.

Any ideas what's wrong, or a better way to accomplish this?

Thanks.

 

[schniggeldorf]

I found a solution, but I have no idea why it actually mattered. When I changed the DoCmd statement in the original to

Me.Recordset.MoveNext

then it all ran without errors. Posting this just in case somebody else has a similar problem someday and could benefit from the answer.

 

[schniggeldorf]

OK, so I was wrong. The solution I posted above didn't really work. Any ideas?