Error passing parameters

I have the following piece of code trying to pass 3
parameters to a query but get the error message 'Object
Variable or With Block variable not set' on the set qdf.

Any ideas on what the problem is?

Dim MyDb As Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset
Set MyDb = CurrentDb
Set qdf = db.QueryDefs("QueryAv")
prn.Value = Eval(Forms![SINGLE BOOKING AVAILABILITY]!
BookingDate)
prn.Value = Forms![SINGLE BOOKING AVAILABILITY]!Combo8
prn.Value = Forms![SINGLE BOOKING AVAILABILITY]!Combo10


Also can I assign the parameters in this way?

Thanks in advance.

No. You need to relate the prm to the qdf.

Try:

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

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

Watch out for spelling. You had "prm" declared but used "prn"

--
HTH
Van T. Dinh
MVP (Access)




"Brian" wrote in message
...
I have the following piece of code trying to pass 3
parameters to a query but get the error message 'Object
Variable or With Block variable not set' on the set qdf.

Any ideas on what the problem is?

Dim MyDb As Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset
Set MyDb = CurrentDb
Set qdf = db.QueryDefs("QueryAv")
prn.Value = Eval(Forms![SINGLE BOOKING AVAILABILITY]!
BookingDate)
prn.Value = Forms![SINGLE BOOKING AVAILABILITY]!Combo8
prn.Value = Forms![SINGLE BOOKING AVAILABILITY]!Combo10


Also can I assign the parameters in this way?

Thanks in advance.

Van: You made the same mistake as Brian did to cause the 'Object Variable or
With Block variable not set' error.

You've instantiated MyDb as the Database object, but you're using db when
trying to instantiate the qdf Object.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)



"Van T. Dinh" wrote in message
...
No. You need to relate the prm to the qdf.

Try:

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

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

Watch out for spelling. You had "prm" declared but used "prn"

--
HTH
Van T. Dinh
MVP (Access)




"Brian" wrote in message
...
I have the following piece of code trying to pass 3
parameters to a query but get the error message 'Object
Variable or With Block variable not set' on the set qdf.

Any ideas on what the problem is?

Dim MyDb As Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset
Set MyDb = CurrentDb
Set qdf = db.QueryDefs("QueryAv")
prn.Value = Eval(Forms![SINGLE BOOKING AVAILABILITY]!
BookingDate)
prn.Value = Forms![SINGLE BOOKING AVAILABILITY]!Combo8
prn.Value = Forms![SINGLE BOOKING AVAILABILITY]!Combo10


Also can I assign the parameters in this way?

Thanks in advance.

Thanks, Doug.

I see the error on the Parameters and missed the error on the database.

Brian

You set the variable Mydb and then your code used "db" ratther than "Mydb".

You should have the "Option Explicit" set on your code so that VBA picks up
these spelling errors and give you wanings on these.

--
HTH
Van T. Dinh
MVP (Access)


"Douglas J. Steele" wrote in message
...
Van: You made the same mistake as Brian did to cause the 'Object Variable
or
With Block variable not set' error.

You've instantiated MyDb as the Database object, but you're using db when
trying to instantiate the qdf Object.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)