problem in loop code

[BAO]

I cannot figure out the problem here. The loop either gets stuck or doesn't
loop. Can anyone help?

Private Sub Text76_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblF_InvUsedList")
If Me.Text76 = -1 Then
Do
If DLookup("[Used]", "tblF_InvUsedList", _
"[Check#ID]= " & Me![Check#ID]) = 0 Then
MsgBox "put message here."
End If
rs.MoveNext
Loop
End If
rs.Close
End Sub

 

[JimBurke via AccessMonster.com]

You don't have any criteria for the loop - how is it supposed to know when to
end? From what I can see you want to loop thru the recordset if Text76 is -1.
If I'm right, here's what I would do (changing it to a while loop, which is
what I usually use):

Private Sub Text76_BeforeUpdate(Cancel As Integer)
On Error GoTo HandleError

Dim rs As DAO.Recordset

If Me.Text76 <> -1 Then
exit sub
End If

Set rs = DBEngine(0)(0).OpenRecordset("tblF_InvUsedList")
if rs.EOF then
not sure what you would don the case where EOF is reached...
Exit sub
End IF

WHile Not rs.EOF
If DLookup("[Used]", "tblF_InvUsedList", _
"[Check#ID]= " & Me![Check#ID]) = 0 Then
MsgBox "put message here."
End If
rs.MoveNext
Wend

rs.Close
set rs = nothing
exit sub

HandleError:
do whatever you would do in case of an error here...

End Sub

I cannot figure out the problem here. The loop either gets stuck or doesn't
loop. Can anyone help?

Private Sub Text76_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblF_InvUsedList")
If Me.Text76 = -1 Then
Do
If DLookup("[Used]", "tblF_InvUsedList", _
"[Check#ID]= " & Me![Check#ID]) = 0 Then
MsgBox "put message here."
End If
rs.MoveNext
Loop
End If
rs.Close
End Sub

 

[BAO]

Thank you for the help. What you said makes sense. Based on your
suggestion, now I have the following. It is now getting stuck at the End If
statement I have in all CAPS. Any thoughts?

Private Sub Text76_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Text76_BeforeUpdate
Dim S As String
Dim R As Long
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblF_InvUsedList")
If Me.Text76 <> -1 Then
Exit Sub
End If
If rs.EOF Then
Exit Sub
End If
While Not rs.EOF
If DLookup("[Used]", "tblF_InvUsedList", _
"[Check#ID]= " & Me![Check#ID]) = 0 Then
MsgBox "There are Inventory #s unused.", vbOKOnly
Cancel = True
END IF
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Exit Sub
Exit_Text76_BeforeUpdate:
Exit Sub
Err_Text76_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Text76_BeforeUpdate
End Sub

You don't have any criteria for the loop - how is it supposed to know when to
end? From what I can see you want to loop thru the recordset if Text76 is -1.
If I'm right, here's what I would do (changing it to a while loop, which is
what I usually use):

Private Sub Text76_BeforeUpdate(Cancel As Integer)
On Error GoTo HandleError

Dim rs As DAO.Recordset

If Me.Text76 <> -1 Then
exit sub
End If

Set rs = DBEngine(0)(0).OpenRecordset("tblF_InvUsedList")
if rs.EOF then
not sure what you would don the case where EOF is reached...
Exit sub
End IF

WHile Not rs.EOF
If DLookup("[Used]", "tblF_InvUsedList", _
"[Check#ID]= " & Me![Check#ID]) = 0 Then
MsgBox "put message here."
End If
rs.MoveNext
Wend

rs.Close
set rs = nothing
exit sub

HandleError:
do whatever you would do in case of an error here...

End Sub

I cannot figure out the problem here. The loop either gets stuck or doesn't
loop. Can anyone help?

Private Sub Text76_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblF_InvUsedList")
If Me.Text76 = -1 Then
Do
If DLookup("[Used]", "tblF_InvUsedList", _
"[Check#ID]= " & Me![Check#ID]) = 0 Then
MsgBox "put message here."
End If
rs.MoveNext
Loop
End If
rs.Close
End Sub