is it possible to set query criteria dynamically from code??

[Paul7905]

I have a cross tab query which i am running from code donated to me from Paul here at tek tips.

The name of the Crosstab Query that i am running in the code is "Crosstab_Size"

This Query has as it's source, another Query called "ProdinfoReportQuery"

If I execute this query by fetching it up and manually typing in the following into the "criteria" field of the item-name column in the Crosstab_Size query and run it, I get exactly the results I need.

criteria i key in is : (note, the ItemName is one i selected to test it and needs to be a variable somehow)

[ProdinfoReportQuery]![ItemName]="Bougainvillea brasiliensis X"


What i would like to to is, before funning the code to execute the query, is to set the "criteria" for the ItemName column in the query to read as above with the ability to insert the name of the item as a variable or argument (I have the item name argument in a string called "Itemnamecriteria&quot ~

is there a way to do this ?

Dim db As DAO.DATABASE, qdf As QueryDef, fld As Field
Dim X As Integer, strFldName() As String

X = 0
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.name = "Crosstab_Size" Then
X = qdf.Fields.Count

SOMEHOW, SET THE CRITERIA FOR ItemName HERE?
criteria for the item name column in the query set as follows ??
[ProdinfoReportQuery]![ItemName]="& Itemnamecriteria"



ReDim strFldName(1 To X) As String

X = 0
For Each fld In qdf.Fields
X = X + 1
strFldName(X) = fld.name
Next
End If
Next qdf


I hope this makes sense ~ basically, i want the code you gave me to run the query for one specific item only by setting the criteria dynamically somewhere in your code.

thanks !

 

[kathryn]

If I understand you correctly, what you want is eminently doable. Check out the help topic on Parameters in the help file.

Your query needs to be a parameter query. In the criteria row of the query, enter something like

[ItemName]

Then after you set your reference to the querydef, you resolve the parameter:

set qdf = currentdb.querydefs("Crosstab_Size&quot
qdf.parameters("ItemName&quot = Itemnamecriteria
qdf.execute


This is rough, as I am doing this OTTOMH, but it should point you in the right direction. Let us know if you need more.

Kathryn

 

[Paul7905]

thanks Kathryn ;-) ,

used your example and finally got it working ~ !! I am so excited !!

(now i am struggling with trying to get a report to run from a command button on a form ~ the report is driven by another parameter query and i want to pass it the parameter in vba code but am not having an good fortunes so far !! )

(put another post out on this one)

Paul

 

[emmy]

To make the paramater hold the value of a text box or dropdown on the form, make the paramater equal to the name of the controll on the form:
qdf.paramaters("ItemName&quot = textbox