Loop that checks until recordset is null

Question

Working in VBA Access

I have some update queries that add data to one field in a table. After they run, there is a select query that is run to determine that there are no longer any null data points in that field.

If the rst.RecordCount > 0 then open up the select query to see results for manual review, once these items have been manually reviewed and the rst is closed I would want to continue on to future code.

I tried a Do While loop but this broke access. I tried an if statement but this does not continue to future code from what I saw.

Private Sub RunFDE_Click()

Dim rst As Recordset
    
'Turn off Access warnings
'DoCmd.SetWarnings (False)

'Run Update Queries
'DoCmd.OpenQuery "FDE - A1B Update Outbound GL Code"
'DoCmd.OpenQuery "FDE - A1C Update Inbound GL Code"

'Turn Access warnings on
'DoCmd.SetWarnings (True)

Set rst = CurrentDb.OpenRecordset("FDE - B1A Review Null GL Code")
Do While rst.RecordCount > 0
    DoCmd.OpenQuery "FDE - B1A Review Null GL Code"
Loop

'DoCmd.OpenQuery "FDE - C1A Update Remaining to 3rd Party"

'More future code

End Sub

user_77335 · Answer

If you build a form based on your SELECT query, you can open the form in dialog mode. That will pause execution of any remaining code in the calling procedure until after the user closes the form.

In this example, I'm using just a MsgBox as a stand-in for your "future code". Note the MsgBox is not displayed until the user closes the form.

Also I used a DCount expression instead of opening a recordset to check whether the SELECT query returns any rows.

Public Sub test45()
    If DCount("*", "FDE - B1A Review Null GL Code") > 0 Then
        DoCmd.OpenForm "YourForm", WindowMode:=acDialog
    End If

    ' the next line is not executed until the form is closed
    MsgBox "Hello World"
End Sub

Loop that checks until recordset is null

1 Answers