Run Query with parameters in a Moudle

  • Thread starter Thread starter Liora
  • Start date Start date
L

Liora

How can I run an Query with parameters in Moudle.

I have a old function that was working and there I wrote:
-----------------------
Dim db As Database
Dim t As DAO.Recordset, R As DAO.Recordset, x As
DAO.Recordset
Dim I As Integer
Dim str As String
Dim MYQUERY As QueryDef

Set db = CurrentDb
Set t = db.OpenRecordset("T-CHOOSE-DAT", DB_OPEN_TABLE)
DoCmd.RunMacro ("M_DEL_PERIOD.DELREC2")
While Not t.EOF
DoCmd.RunMacro ("M_DEL_PERIOD.DELTAB")
I = I + 1

Set MYQUERY = db.QueryDefs(QRY)
MYQUERY.Parameters(Forms![R-Order Status Dialog33]!
[FDAT]) = t![FDAT]
MYQUERY.Parameters(Forms![R-Order Status Dialog33]!
[TDAT]) = t![TDAT]
 
Liora said:
How can I run an Query with parameters in Moudle.

I have a old function that was working and there I wrote:
-----------------------
Dim db As Database
Dim t As DAO.Recordset, R As DAO.Recordset, x As
DAO.Recordset
Dim I As Integer
Dim str As String
Dim MYQUERY As QueryDef

Set db = CurrentDb
Set t = db.OpenRecordset("T-CHOOSE-DAT", DB_OPEN_TABLE)
DoCmd.RunMacro ("M_DEL_PERIOD.DELREC2")
While Not t.EOF
DoCmd.RunMacro ("M_DEL_PERIOD.DELTAB")
I = I + 1

Set MYQUERY = db.QueryDefs(QRY)
MYQUERY.Parameters(Forms![R-Order Status Dialog33]!
[FDAT]) = t![FDAT]
MYQUERY.Parameters(Forms![R-Order Status Dialog33]!
[TDAT]) = t![TDAT]
-------------------------
It was working. now when I try to run it I received an
error: "data type conversion error"

and when I wrote:
----------------------------------------------------
MYQUERY.Parameters("Forms![R-Order Status Dialog33]!
[FDAT]") = t![FDAT]
MYQUERY.Parameters("Forms![R-Order Status Dialog33]!
[TDAT]") = t![TDAT]
----------------------------------
I recieved error:Item not found in this collection


working with Access XP -Windows 2000 prof
Click to expand...

Here you have 2 Parameters exemple....!

Dim dbs As Database
Dim qdf As QueryDef
Dim rst as Recordset
Set dbs = CurrentDB
Set qdf = dbs.QueryDefs("qryName")
qdf.Parameters![Month:] = "March"
qdf.Parameters![Sex:] = "F"
Set rst = qdf.OpenRecordset
'...
rst.close
qdf.close
Set dbs = nothing

Here you use a strange Parameter Name, i think w'll be better like this:

MYQUERY.Parameters![FDAT:] = t![FDAT]


Bye.
Alessandro(IT).
 
Back
Top