If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Error passing parameters
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. |
#3
|
|||
|
|||
Error passing parameters
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. |
#4
|
|||
|
|||
Error passing parameters
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) |
Thread Tools | |
Display Modes | |
|
|