Error 3061 Query “loses” form text value

I looked at a number of 3061 posts but they all have the query in VB. I am trying to run an already saved query in Access, that has a filter using a text field on a form. So all I am trying to do is just get a recordset from an existing query.

Not sure quite how to explain what's going on. But I have a Master form which holds the current selected date in a text object. I have a query that filters results based on the text object value:

  SELECT DISTINCT EmployeeName
    FROM dbo_Audits
   WHERE dbo_Audits.AuditDate = [Forms]![MasterForm]![ReportDate]

Running the query is fine and it pulls for the selected date except in a specific circumstance.

If I open a subform, and keep the master form still open but not in focus, it still works i.e. I can run the query and it pulls the list of employees that had an audit that day.

But if I click a button on the subform to perform an action and put a breakpoint on the OnClick event, then try to run the query, it doesn't return any results. Its because it doesn't "recognize" or it's lost the value of "[Forms]![MasterForm]![ReportDate]" and therefore no results are returned.

Odd thing is, at the breakpoint, I query the text box value in the intermediate window and it still returns the date.

That is one way I have tested it. But what I am really trying to do is get the recordset from this query, in the back end coding, but when it encounters this coding:

 strSQL = "SELECT * FROM " & strQueryName & " "
 Set rstNames = CurrentDb.OpenRecordset(strSQL)

The OpenRecordSet returns the error message:

      3061 - Too Few Parameters.  Expected 1.

I put a breakpoint on the OpenRecordSet and do a DCount on the strQueryName and get a result of the number of records. So the query is kind of working. But not when I run the query through access (while on the breakpoint) and not when it tries to open the recordset.

Any ideas what's going on and how to fix this?

Since OpenRecordset does not dereference [Forms]![MasterForm]![ReportDate], and thinks it's a parameter, open the the saved query as a QueryDef object and give it the parameter value Access wants. Then you can use OpenRecordset from the QueryDef.

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(strQueryName)
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
Set rstNames = qdf.OpenRecordset()