MS Access VBA – Query-by-Form Not Running with New Data

ms accessvba

I have created a query-by-form in MS Access. The form has a text field for each field in my table. I'm only using one starting out to test. The query criteria for that field is 

Like "*" & [Forms]![MasterSearchForm]![KeyWords] & "*" Or [Forms]![MasterSearchForm]![KeyWords] is Null

The form has a button with the onclick event to open the query.

If the query is closed when I click the button, it opens the query with the appropriate criteria. However, if I change the value in the form and then click the button again, only the old results appear. If I execute the query manually, then it does suck the data from the form. Why do I need to close the query each time? How do I make the button run the query, rather than just focusing the query tab?

Private Sub Run_Click()
On Error GoTo Run_Click_Err

    DoCmd.OpenQuery "QueryFromMasterSearch", acViewNormal, acReadOnly

    Run_Click_Exit:
    Exit Sub

    Run_Click_Err:
    MsgBox Error$
    Resume Run_Click_Exit

End Sub

Best Answer

You need to add:

On Error Resume Next
DoCmd.Close AcQuery, "QueryFromMasterSearch"
On Error GoTo Run_Click_Err

just before you open the query.

So, your code becomes:

Private Sub Run_Click()
On Error GoTo Run_Click_Err

    On Error Resume Next
    DoCmd.Close AcQuery, "QueryFromMasterSearch"
    On Error GoTo Run_Click_Err
    DoCmd.OpenQuery "QueryFromMasterSearch", acViewNormal, acReadOnly

    Run_Click_Exit:
    Exit Sub

    Run_Click_Err:
    MsgBox Error$
    Resume Run_Click_Exit

End Sub

The On Error Resume Next bit tells Access to not worry if the Query Window is not actually open. If you left that out, and didn't have the Query Window open the code would spit out an error.

Related Question