By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

queryDef Parameters and recordset, storing to variable

P: 1
Hello Everyone, I am trying to read from a stored query in MS Access to a variable.

My error: Run-time error '91': Object variable or With block variable not set.

My code:

Expand|Select|Wrap|Line Numbers
  1. ######## CODE START ###########
  2.  
  3. Dim rst As DAO.Recordset
  4. Dim dbs As DAO.Database
  5. Dim qdf As DAO.QueryDef
  6.  
  7. Dim strResult As String
  8.  
  9. Set qdf = dbs.QueryDefs("Report Query")
  10.  
  11. qdf.Parameters("[Forms]![qryFormReporting]![Date_Start]") = Date_Start
  12. qdf.Parameters("[Forms]![qryFormReporting]![Date_End]") = Date_End
  13. qdf.Parameters("[Forms]![qryFormReporting]![prod_ID]") = Prod_ID
  14. qdf.Parameters("[Forms]![qryFormReporting]![class_ID]") = class_ID
  15. qdf.Parameters("[Forms]![qryFormReporting]![origin_ID]") = origin_ID
  16. qdf.Parameters("[Forms]![qryFormReporting]![Item_Type]") = Item_Type
  17. qdf.Parameters("[Forms]![qryFormReporting]![type_ID]") = type_ID
  18.  
  19. Set dbs = CurrentDb
  20. sSql = "SELECT [Participation Query].*, Items_Book_HardBack_CountSold_byDate.totHard, Items_Book_SoftBack_CountSold_byDate.totSoft, Items_Book_PaperBack_Free_CountSold_byDate.*, Items_Book_PaperBack_CountSold_byDate.totPaper, Items_Book_HardBack_CountSold_byDate.[$HB], Items_Book_SoftBack_CountSold_byDate.[$SB], Items_Book_PaperBack_CountSold_byDate.[$PB], [$HB]+[$SB]+[$PB] AS sumReport, Order_Product_Revenue_Count_by_Date.sumRevenue FROM Order_Product_Revenue_Count_by_Date, Items_Book_HardBack_CountSold_byDate, Items_Book_SoftBack_CountSold_byDate, Items_Book_PaperBack_CountSold_byDate, Items_Book_PaperBack_Free_CountSold_byDate, [Participation Query];"
  21. Set rst = qdf.OpenRecordset(sSql)
  22.  
  23. With rst
  24.     .MoveFirst
  25.     strResult = .Fields(0)
  26. End With
  27.  
  28. Set rst = Nothing
  29. Set dbs = Nothing
  30. Set qdf = Nothing
  31.  
  32. txtReport = strResult
  33.  
  34. ######## END CODE ###########
What am I missing?

Thank you so much for your help!
Jun 12 '12 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
What you are trying to achieve is unclear. In lines 9 to 17 You have a parameterised querydef object, qdf, based on query "report query". At line 21 you try to open a separate recordset based on a SQL query, using the same qdf object - which makes no sense.

The qdf.OpenRecordset method does not take a string parameter - if you lookup the help you will find that its optional parameters relate to the type of the recordset you want to open. You don't need to supply an SQL string to a querydef's OpenRecordset method as the querydef is tied to a specific query already ("report query" in your case).

I would suggest that line 21 should actually be

Expand|Select|Wrap|Line Numbers
  1. set rs = CurrentDb.OpenRecordset(sSql)
However, I cannot see that the sql-based recordset relates at all to the parameterised querydef you are initially setting up.

It always helps if you tell us which line the error is occurring on.

-Stewart
Jun 13 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.