Q:

Access - Query works fine when run manually but cannot create a recordset using it

I have a table titled Test_Table such as
   ID          Code
  -----       -------
   1             A
   2             B
   3             C

I have created a simple form titled TestForm to dynamically retrieve records from the table. The form element in question is a textbox and is named TextBox2
I created the following query and named it TestQuery to retrieve records from Test_Table using user-entered value in TextBox2 :
SELECT *
FROM TestTable
WHERE (((TestTable.Code)=Forms![TestForm]![TextBox2]));

The query works fine when I manually execute it.
I created the following sub-procedure to retrieve the query results in a recordset such as -
Sub Retrieve()
    Dim db As Database
    Set db = CurrentDb
    Dim rst As Recordset
    Set rst = db.OpenRecordset("TestQuery") 'Shows run-time error on this step
    MsgBox (rst!ID)
End Sub

But when I try to run this sub-procedure, I get an error stating 'Run Time Error 3061  - Too Few Parameters. Expect 1'.
Am I missing something here?  Or is there a better way to do it?

A:

Access can evaluate Forms![TestForm]![TextBox2] and retrieve the value from that text box when you run the query from the query designer.
However, that does not happen when you open the query with OpenRecordset().  In that situation, Forms![TestForm]![TextBox2] is treated just like any other parameter ... so you must supply its value separately ...
Sub Retrieve()
    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Set db = CurrentDb
    Set qdf = db.QueryDefs("TestQuery")
    qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
    Set rst = qdf.OpenRecordset
    MsgBox rst!id
End Sub

