Parameter query based subform showing all records onload

Hi,

I have a simple form with one unbound text box. It passes it's value
to a subform via a parameter query.

Forms:
frmPayroll
subfrmPayroll (based on qryPayroll)

Queries:
qryPayroll
Criteria: Like [Forms]![frmPayroll]![txtLoanNumber] & "*"

QUESTION:
Everything works fine, but when the form and subform load, it runs the
subform query, loads the subform,shows all records, and takes a fair
amount of time. What I would like is to prevent the subform from
loading or running the query until a search button is pressed.

Can anyone help?

Could you name txtLoanNumber in the LinkMasterFields, and the matching
subform control in LinkChildFields? That would bring the form up with no
records in the subform until a value is added in txtLoanNumber.

If you can't do it that way, change the criteria to a condition that always
evaluates to False, e.g.:
SELECT * FROM Table1 WHERE (False);

Then in the Click event of your search button, you can assign the desired
RecordSource, e.g.:
Dim strSql As String
If Not IsNull(Me.txtLoadNumber) Then
strSql = "SELECT * FROM Table1 WHERE LoanNumber = " &
Me.txtLoanNumber & ";"
Me.RecordSource = strSql
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
oups.com...

I have a simple form with one unbound text box. It passes it's value
to a subform via a parameter query.

Forms:
frmPayroll
subfrmPayroll (based on qryPayroll)

Queries:
qryPayroll
Criteria: Like [Forms]![frmPayroll]![txtLoanNumber] & "*"

QUESTION:
Everything works fine, but when the form and subform load, it runs the
subform query, loads the subform,shows all records, and takes a fair
amount of time. What I would like is to prevent the subform from
loading or running the query until a search button is pressed.

Can anyone help?