Tuesday, November 20, 2007

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!


aespe on August 22, 2008 at 5:14 PM said...

this sure help me, thx and yes i'm having a good day today :)


Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template