I WANT TO PASS A PARAMETER TO AN EXISTING QUERY IN MY VBA CODE.
I am trying to call a crosstab query and passing it a parameter as follows :
qdf.Parameters(0) = BOTANICALSAVE$
the field BOTANICALSAVE$ Contains data that the query needs to select only records i want in the crosstab query.
I cannot get it to work ~
If I remove the statement "qdf.Parameters(0) = BOTANICALSAVE$"
and change the query to a regular cross-tab, the code runs fine (except the query returns all records and that is not what i want).
I think my problem is setting up the crosstab query correctly so that the parameter string "BOTANICALSAVE$" is processed correctly. If i run the query stand-alone and manually key in a item number when it prompts me, it returns the correct results. but will not when i try to pass it the same item number in code (i always get back 0 records when running it in code)
maybe the problem is how i am passing the parameter field ?
also, i don't see the correlation between the "parameters" and the actual criteria field in the columns in the query.
also, my access "help" file does not tell me exactly what is supposed to go into the parameter field and or how it relates to the query columns. what goes into this field ?
IN THE QUERY :
In the "criteria" field in the item-number column, in the criteria row, i have the following : [item]
in the first row of the parameters list for the query i have the following
parameter data type
[item] text
I AM USING THE FOLLOWING CODE :
Dim db As DAO.Database, qdf As QueryDef, fld As Field
Dim strSizeFldName() As String
Dim X As Integer
'store the key of the record to pass to the parameter Query
BOTANICALSAVE$ = (dblibdata.Fields(1).Value)
X = 0
Set db = CurrentDb
Set qdf = db.QueryDefs("ProdinfoReportQuery_Crosstab3"
'pass the parameter [itemnumber] to the parameter Query
qdf.Parameters(0) = BOTANICALSAVE$
X = qdf.Fields.Count
ReDim strSizeFldName(1 To X) As String
X = 0
For Each fld In qdf.Fields
X = X + 1
strSizeFldName(X) = fld.Name
Next
I am trying to call a crosstab query and passing it a parameter as follows :
qdf.Parameters(0) = BOTANICALSAVE$
the field BOTANICALSAVE$ Contains data that the query needs to select only records i want in the crosstab query.
I cannot get it to work ~
If I remove the statement "qdf.Parameters(0) = BOTANICALSAVE$"
and change the query to a regular cross-tab, the code runs fine (except the query returns all records and that is not what i want).
I think my problem is setting up the crosstab query correctly so that the parameter string "BOTANICALSAVE$" is processed correctly. If i run the query stand-alone and manually key in a item number when it prompts me, it returns the correct results. but will not when i try to pass it the same item number in code (i always get back 0 records when running it in code)
maybe the problem is how i am passing the parameter field ?
also, i don't see the correlation between the "parameters" and the actual criteria field in the columns in the query.
also, my access "help" file does not tell me exactly what is supposed to go into the parameter field and or how it relates to the query columns. what goes into this field ?
IN THE QUERY :
In the "criteria" field in the item-number column, in the criteria row, i have the following : [item]
in the first row of the parameters list for the query i have the following
parameter data type
[item] text
I AM USING THE FOLLOWING CODE :
Dim db As DAO.Database, qdf As QueryDef, fld As Field
Dim strSizeFldName() As String
Dim X As Integer
'store the key of the record to pass to the parameter Query
BOTANICALSAVE$ = (dblibdata.Fields(1).Value)
X = 0
Set db = CurrentDb
Set qdf = db.QueryDefs("ProdinfoReportQuery_Crosstab3"
'pass the parameter [itemnumber] to the parameter Query
qdf.Parameters(0) = BOTANICALSAVE$
X = qdf.Fields.Count
ReDim strSizeFldName(1 To X) As String
X = 0
For Each fld In qdf.Fields
X = X + 1
strSizeFldName(X) = fld.Name
Next
