Parameter query based subform showing all records onload

[NickLoganPayneFiles]

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?

 

[Allen Browne]

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