Passing parameters to a pre existing query

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I have a query I am running to generate an import file.
To manipulate the data, I am using the queries resulting
recordset as an object and calling values from it that
way. Some of the criteria never changes and as such does
not represent a problem. However, criteria that does
require to be inputed by the user, i.e. date range, causes
an error when I put the criteria in the query. My
questions is simply, how do I pass parameters to a pre
existing query that the user imputs when the code is
executed?
 
Rich said:
I have a query I am running to generate an import file.
To manipulate the data, I am using the queries resulting
recordset as an object and calling values from it that
way. Some of the criteria never changes and as such does
not represent a problem. However, criteria that does
require to be inputed by the user, i.e. date range, causes
an error when I put the criteria in the query. My
questions is simply, how do I pass parameters to a pre
existing query that the user imputs when the code is
executed?
Click to expand...


That's too vague to have an answer.

What database engine are you using?
Jet, MSDE, SQLServer, Oracle, ...

How is the query being executed?
From the db window, in VBA code, in a Transfer type
operation, ...
 
-----Original Message-----
Rich said:
I have a query I am running to generate an import file.
To manipulate the data, I am using the queries resulting
recordset as an object and calling values from it that
way. Some of the criteria never changes and as such does
not represent a problem. However, criteria that does
require to be inputed by the user, i.e. date range, causes
an error when I put the criteria in the query. My
questions is simply, how do I pass parameters to a pre
existing query that the user imputs when the code is
executed?
Click to expand...


That's too vague to have an answer.

What database engine are you using?
Jet, MSDE, SQLServer, Oracle, ...

How is the query being executed?
From the db window, in VBA code, in a Transfer type
operation, ...
--
Marsh
MVP [MS Access]
.
Database engine is Jet
Click to expand...
Query is being executed in the VBA code
 
-----Original Message-----
Rich said:
Database engine is Jet
Query is being executed in the VBA code
Click to expand...


Ok. here's some air code to open recordset with parameters
that reference form controls:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("nameofquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
Do Until rs.EOF
' . . .
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set qdf = Nothing
Set db = Nothing
 
Back
Top