Flex Search Form/Subform Running Query On Open

[froggiebeckie]

Hey, all.
I've done a load of searching and with the help of a slew of posts on this forum and others, was able to construct a flexible search form to run a query with up to 7 criteria selected from individual combo boxes. The query is run from a button on the main form.

Criteria in the query is like this for each of the 7 fields:

Like (IIf(IsNull([Forms]![EngSearchFrm]![Requestor]),"*",([Forms]![EngSearchFrm]![Requestor])))

If none of the criteria is selected, the query will return ALL the records.
I was also able to get the query results to show in a sub form.

I'm really quite happy with the (almost) end product, but have one stumbling block that I haven't been able to work through or around.

When the form/sub form is opened, the query runs. Since there were no criteria selected, the query returns all the records, which is going to confuse the crap out of my end users.

Is there a simple way to either run the query only on the button click, or only show the subform on requery?

Thanks in advance for looking.

BeckieO

 

[froggiebeckie]

Thanks so much, Uncle Gizmo.
I've spent so much brain power on getting the 7 different criteria (along with possible null values) to work, that I just couldn't see how to solve this.
Your response got me back looking at how the form/sub form/query were actually setup.

While I was working with your suggestion, I realized I hadn't tried working with the "Visible" property for the sub form window.

I set the sub form "Visible" property to "NO", then added a line to the command button code to reset that property to "YES" after the requery, and the thing's humming like a top.

Here's a copy of the button code, in case it may help someone else.

Private Sub Fetchbut_Click()
On Error GoTo Err_Fetchbut_Click

    Dim stDocName As String

    stDocName = "EngSearchQry"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.Close

    Me.EngSearchQrysub.Requery
    Forms![EngSearchFrm]![EngSearchQrysub].Form.Visible = True
Exit_Fetchbut_Click:
    Exit Sub

Err_Fetchbut_Click:
    MsgBox Err.Description
    Resume Exit_Fetchbut_Click

End Sub

Again, thanks so much for taking the time to try to understand my problem, and to make suggestions on how to deal with it. It sure does help to have a different perspective.

BeckieO

 

[boblarson]

BeckieO:

Also, just an FYI for you - Subforms load BEFORE the main form. This can cause unintended side effects if you are not aware of that and mitigate it.