Displaying the results of a SELECT that accepts parameters

[thick_guy_9]

Dear All

I inherited an ACCESS application that has a SELECT query with hard-coded parameters.

i.e where (((TABLE.YEAR=2007)))

By going to the Query Designer, I have added a criteria [What YEAR] that prompts for year when the query is opened.

To do this in VBA, I did the following to pass the parameters:
-----------------------------------------
Private Sub cmdQuery_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim parm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb()

Set qdf = db.QueryDefs("myqryrel")
qdf.Parameters("WHICH YEAR") = "2009"
qdf.Parameters("WHICH COLOUR") = "YELLOW"
Set rs = qdf.OpenRecordset
'DoCmd.OpenQuery "MYQRYREL"

End Sub
----------------------------------------------

NOW how do I display the results of the Query?
DOcmd.openquery.... again prompts for the values, so it is not an option.

I want to pass the parameters to the query and view the filtered results.
Please note that constructing the SQL is not an option as the user has indicated that they want to be able to add some columns using QUERY designer.

Thanks in advance

 

[thick_guy_9]

I think this is the solution:
http://msdn.microsoft.com/en-us/library/dd671279.aspx
You need to create a PUBLIC function(say getVal) in a STanDARD MODULE that will return the value of the variable that will filter your SELECT query.

In the QUERY designer, you will put :
=getVal()
against the criteria

But this only makes the query viewable using the VBA application. You will get an error if you open it standalone.

 

[wazz]

if you're still looking, do a search for this:

    'Evaluate and set the query's parameters.
    For Each prm In qdf.Parameters
        prm.Value = [B]Eval(prm.Name)
[/B]        Debug.Print prm.Value
    Next prm

for some ideas, if needed.