Set One Instance of MsgBox in DoWhile Loop 3

[larrydavid]

Hello,

I have a function that fires ok in an AutoExec macro:

Public Function CSAlert()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("dbo_CS_Input", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
If rs!Pre_Approval = False Then
'Debug.Print rs!id
MsgBox ("THERE ARE MANUAL CHECK REQUESTS AWAITING PRE-APPROVAL!")
End If
rs.MoveNext
Loop
End Function

Is there a way to keep the MsgBox from popping up again after I click "OK" the first time? I realize that for each record in the loop a MsgBox will appear but I was thinking there might be a way to suppress any subsequent appearances of the MsgBox after the first record where the condition is met. Any help with this or a better approach would be greatly appreciated.

Thanks,
Larry

 

[dhookom]

What is the purpose of the loop? Aren't you just needing to check if there are any records with Pre_Approvall = False? If so, you can try this code:

If DCount("*","dbo_SC_Input","Pre_Approval = False") > 0 then
   MsgBox ("THERE ARE MANUAL CHECK REQUESTS AWAITING PRE-APPROVAL!")
End If

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

How about:

[pre]
Public Function CSAlert()
Dim rs As DAO.Recordset[blue]
Dim blnMsgIsShown As Boolean[/blue]
Set rs = CurrentDb.OpenRecordset("dbo_CS_Input", dbOpenDynaset, dbSeeChanges)
[blue]
blnMsgIsShown = True[/blue]

Do While Not rs.EOF
If rs!Pre_Approval = False Then
'Debug.Print rs!id
[blue]If blnMsgIsShown Then[/blue]
MsgBox ("THERE ARE MANUAL CHECK REQUESTS AWAITING PRE-APPROVAL!")[blue]
blnMsgIsShown = False
End If[/blue]
End If
rs.MoveNext
Loop
End Function


[/pre]

Have fun.

---- Andy

 

[PHV]

Yet, another way:

Public Function CSAlert()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("dbo_CS_Input", dbOpenDynaset, dbSeeChanges)
Do While Not rs.EOF
    If rs!Pre_Approval = False Then
        MsgBox ("THERE ARE MANUAL CHECK REQUESTS AWAITING PRE-APPROVAL!")
        Exit Do
    End If
    rs.MoveNext
Loop
rs.Close
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I set the condition just for the message box because I assume this function does something else, something more than just displaying the message box.

Have fun.

---- Andy

 

[larrydavid]

WOW, thank you all for the quick responses and great suggestions. Much obliged. --Larry