How to not run query on subform load

[MrBologna]

I have a form with some part search fields and a subform that shows part info. this subform already has a linked subdatasheet.

From this form, after searching for and selecting a part, a function button then opens a popup window which shows every job that part has been used on.
As this is a table of every part from every job ever done, it is quite large and takes a while to query (about 30-60s).

The issue I'm having right now is that the function button opens the popup form and loads the subform/query.
I then set the search field of this popup form to the part I want to look at, and requery.



this results in a nearly 2 minute load time for this form.

Is there any way I can keep the subform from loading with the popup form?
Or can I put a VBA variable into the query for the subform from within the function button's code, so it is querying the correct info from the start?

Function button code:

Private Sub btnShow_Where_Used_Click()

    Dim tempPartNo As String
    tempPartNo = [Parts_Search_Query_Advanced subform].Form.NEC_Part_No
    
    DoCmd.OpenForm "Where_Used_Popup_Form"
    
    [Forms]![Where_Used_Popup_Form].[input_NEC_PN] = tempPartNo
    [Forms]![Where_Used_Popup_Form].[Where_Used_Popup_Query_subform].Requery


End Sub

[Micron]

You can move the query sql into a more conducive event and set a form's record source there. AKAIK, when set, a requery won't be required.
Maybe don't open the main form loaded with every record as well - it will only get slower?

EDIT - you can also set a subform control (the subform container) linked object in code instead of in the property sheet.

[MrBologna]

I don't quite get what you mean by this.
Sorry, I developed this system from scratch over the past year and kind of just made it work. I'm far from an expert in Access.

Are you saying I could have nothing for the subform's record source on load, and then change the record source property with an event like after I've set the input box to the part I need?

I would love to open all of my forms with no records loaded until a search is performed, but that is kind of my original question: I haven't been able to figure out how to do this.

[Micron]

Are you saying I could have nothing for the subform's record source on load, and then change the record source property with an event like after I've set the input box to the part I need?

Yes. You do such assignments in code rather than the property sheet. Also saying you can open a form that has a subform control on it but have no source object specified on property sheet (for the subfrom control) until some event occurs. This is often done when you want to have subforms on tab control pages - you load them when page tab is clicked on rather than load a bunch of subforms right away. Speeds things up, especially if it may be that several of the tabs wouldn't even be looked at.

I would love to open all of my forms with no records loaded until a search is performed,

Basic answer is that you use unbound forms. This may help...

https://www.google.com/search?client...ne+search+form

[MrBologna]

Awesome, I got the recordsource change working with some kinks, but I should be able to figure them out.

Thank you very much!

[Micron]

U R welcome. Glad to help.