Code needed for Null records

[jinman1883]

I have created a query "on the fly". Where a user would select the criteria from some list boxes, press a button and the results are shown in a form. But if a user choose's criteria that returns 0 records I get a blank form. Is there a piece of code that returns the user to the previous screen if this happens?

Thanks

 

[Mile-O]

You'll need DAO enabled but this shoud do...

Private Sub MyButton_Click

    On Error Goto Err_ErrorHandler

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Dim strSQL As String

    Set db = CurrentDb

    ' < insert query building code here >

    On Error Resume Next
    db.QueryDefs.Delete "MyQuery"
    On Error Goto Err_ErrorHandler

    Set qdf = db.CreateQueryDef("MyQuery", strSQL)

    qdf.Close
    db.Close

    If DCount("MyField", "MyQuery") > 0 Then
        DoCmd.OpenForm "MyForm", acNormal
    End If
   
Exit_ErrorHandler:
    strSQL = vbNullString
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

Err_ErrorHandler:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler

End Sub