Error 3061 Query “loses” form text value

Question

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?

Answer 1

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()