Leo said:
No one has answered this message posted yesterday. Please Help
Any idea why the SQL queries that are created by the Cross Tab Query Wizard
cannot be ran in VBA?
It starts off with the 'TRANSFORM' statement, as opposed to the usual
'SELECT' statement
IS this the reason why? Or is it because I have 'PARAMETERS' defined in the
SQL statement?
How would I ran an SQL statement created by a Cross Tab query in VBA?
Hi Leo,
I believe you have a xtab query that expects a parameter
which you can run in the query designer with no problem.
But, when you try to open a recordset to it in VBA,
you find that you don't get a prompt for the parameter,
or, it cannot find the parameter if it references a form control.
In VBA you must provide the parameter value yourself.
There are several scenarios, depending on if you were
using DAO or ADO, and, if the parameter referred to
a form control or not.
========================================
DAO, parameter refers to form control of form that is open
========================================
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("nameofyourquery")
For Each prm In qdf.Parameters
'following only works if parameter(s) are controls on open form
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
========================================
DAO, parameter(s) do not refer to form control
========================================
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("nameofyourquery")
For Each prm In qdf.Parameters
'following works if parameter(s) are not controls on open form
prm.Value = InputBox("Value for " & prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
If you are using ADO, please provide copy of
your code and will try to modify for you.
Good luck,
Gary Walter
