My guess would be that the presence of a parameter prevents the query from being optimized.
I cannot guarantee that it will help, but you could try the following:
1) Open the query in design view.
Click Parameters in the Show/Hide group of the Design tab of the ribbon.
If you haven't done so already, enter [Forms]![frm_Reports]![text65] in the Parameter column, and select the appropriate type in the Data Type column (presumably Long Integer).
Click OK, then save and close the query.
Does that improve performance?
If not, remove the parameter again.
2) Make a copy of the query for if you accidentally mess up the query. That way, you have a backup.
Open the query in design view, then switch to SQL View. You'll see something like
SELECT ...
FROM ...
WHERE [ID_Seen_By_Staff]=[Forms]![frm_Reports]![text65]
Replace the line breaks with spaces, so that the SQL statement is a single line (it might still be displayed across multiple lines):
SELECT ... FROM ... WHERE [ID_Seen_By_Staff]=[Forms]![frm_Reports]![text65]
Also replace all double quotes in the SQL statement, if present, with single quotes (apostrophes).
Copy the modified SQL statement to the clipboard.
Close the query without saving it.
In the On Click event of a command button on the form frm_Reports, change the SQL of the query:
Code: Select all
Private Sub cmdReport_Click()
Dim strSQL As String
strSQL = "SELECT ... FROM ... WHERE [ID_Seen_By_Staff]=" & Me.text65
CurrentDb.QueryDefs("qrySomething").SQL = strSQL
' Code that uses the query directly or indirectly goes below
' For example, opening a report that uses the query as record source
...
End Sub
Substitute the name of the query.
As you can see, the value of the text box has been placed outside the quotes. When you click the button, the SQL of the query uses the value of text65 as a fixed value.
See if this improves performance.
If not, you can use the backup copy to restore the original query.
