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

 

[Uncle Gizmo]

If I understand your question correctly then you have a main form with seven combo boxes on it in which you select the criteria for a query.

On this main form you also have a sub form window which displays a sub form in datasheet view, this subform displays the results of the query mentioned above.

If I’ve got this about right, then I think I would approach the problem like this:

Your sub form has a record source property which I assume is currently pointing at the above query. Remove this from your subform, in other words set the sub forms record source to nothing by deleting everything in the box provided for this purpose, the “record source” box.

Now on your main form you need to provide a command button which sets the record source of the sub form to the query and then refresh the sub form display.

This is not quite as simple as it sounds because your sub form is not actually present on your main form, your main form has a control called a sub form window, and within the sub form window you will find your sub form.

The code to update the sub forms record source needs to reflect this, and it would need to read something like:

Me.Form.SubformWindow.Form.RecordSource = qryMyQuery

Me.Form is your main form

.SubformWindow refers to the sub form window on your main form. I usually name the sub form windows with the following format. sfrmWinMySubfrmName

.SubformWindow.Form refers to the form housed within the SubformWindow.

.SubformWindow.Form.RecordSource refers to the property of the form housed within this subform window that you want to affect.

The above is “Air Code” in other words I have written it from memory, so I may have made a mistake in the writing of it.

 

[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

 

[Uncle Gizmo]

Your approach is most definitely a good option and thank you for the courtesy of posting your code.

I would add further information to this thread for the benefit of future readers.

There are two advantages to the method I have shown which may be applicable to your database and to other people reading this answer.

1) If the number of records returned by the underlying query becomes significant, then this can have an effect on the speed that your form loads. It is common practice to remove the record sources of sub forms to increase the loading speed.

2) Once you start constructing your SQL statements in the pure “text format” you can do quite sophisticated things with VBA, and then pass this text format directly into the record source of your sub form. Ignore my earlier comment (Now deleted) please I was having a moment of self doubt

As always there are generally three ways to do something and usually many more! No way is better or worse, the main thing is that get the job gets done.

 

[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.