Change the sub form’s record source

[myhnews]

I have a main form that has 10 sub forms
Each sub form’s record source is link to a different Query

It takes more then a minute to open the form, (because it’s running the query for all sub forms)
So I changed the sub forms source to SELECT * FROM tblTest WHERE false;

I also changed the main form
When the button on the main form is clicked, it’s adding the following
Me.SubMySub.Form.RecordSource = "select * from qMyQuery"
Me. SubMySub.Form.Requery

However, after I close the main form, the sub form’s record source stays linked to
SELECT * FROM qMyQuery;

And will take the same long time again to open the main form.

Does anyone have any solution?

Thank you very much
Joe

FYI - Just in case you need this information
The 10 queries are based on a single union query
The union query has a filter where one filed is pointing the main form

 

[RuralGuy]

Are you closing the form with a command button? If so, what code is behind button? By the way, you can leave the RecordSource empty until you are viewing the subform and the Requery should *not* be necessary.

 

[myhnews]

Yes I have a Custom close button with no special functions

If I leave the sub forms record source blank, all fields will have the “Name” when opening

Thanks
Joe

 

[RuralGuy]

Post the code behind the "custom close button" please and you are right about the #Name error, sorry.

 

[myhnews]

Private Sub Command59_Click()
On Error GoTo Err_Command59_Click

DoCmd.Close

Exit_Command59_Click:
Exit Sub

Err_Command59_Click:
MsgBox Err.Description
Resume Exit_Command59_Click

End Sub

 

[RuralGuy]

Try:
DoCmd.Close acForm, Me.Name, acSaveNo

 

[myhnews]

Will this also take care of the sub forms?

 

[RuralGuy]

You will have to try it and find out since I don't know. You could always set the RecordSource of your SubForms in the Load event of the form.