Pass Parameter Value from Form to Query

Recently working with extracting data from Access Database and export it to an excel file. To achieve all these, I created a query that pulls up some data from a table which contains a condition that must be filled in by the user. In other words, I need to pass parameters from Form to Query.

Hence when I ran the query, there is an “Enter Parameter Value” dialog box prompted out that must be filled. This is because the query itself actually has a WHERE statement, for example SELECT * FROM table_name WHERE field_name = VAR1;

I tried to use the query and call it from a form that I have created. But it kept give me the error message “Too few parameter”. This is how my codes look like:

sSQL = "Test_Partner"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

The query just cannot get the parameter value then I referred it to. After a few hours of try out, finally the error has disappeared.

To make it work, i had amended my codes as the following:

sSQL = "Test_Partner"
Set dbs = CurrentDb
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set qdf = dbs.QueryDefs(sSQL)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()

And amended my query as well, as follow:

SELECT * FROM TABLE1 WHERE TABLE1.FIELD1 = [Forms]![Formname]![Controlname];

Yes. If you are calling a Query from a Form, this will help to solve the problem.

In my case, I called the Query from a Subform, so this makes thing more complicated, as I need to pass the parameter from the subform via its main form. Here is the query looks like on how to pass parameter value via main form:

SELECT * FROM TABLE1 WHERE TABLE1.FIELD1 = [Forms]![Main_Formname]![Sub_Formname]![Controlname];

Hope this can help you. Have a good day!