queryDef Parameters and recordset, storing to variable

[leesirc2002]

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:

######## CODE START ###########
 
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
 
Dim strResult As String
 
Set qdf = dbs.QueryDefs("Report Query")
 
qdf.Parameters("[Forms]![qryFormReporting]![Date_Start]") = Date_Start
qdf.Parameters("[Forms]![qryFormReporting]![Date_End]") = Date_End
qdf.Parameters("[Forms]![qryFormReporting]![prod_ID]") = Prod_ID
qdf.Parameters("[Forms]![qryFormReporting]![class_ID]") = class_ID
qdf.Parameters("[Forms]![qryFormReporting]![origin_ID]") = origin_ID
qdf.Parameters("[Forms]![qryFormReporting]![Item_Type]") = Item_Type
qdf.Parameters("[Forms]![qryFormReporting]![type_ID]") = type_ID
 
Set dbs = CurrentDb
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];"
Set rst = qdf.OpenRecordset(sSql)
 
With rst
    .MoveFirst
    strResult = .Fields(0)
End With
 
Set rst = Nothing
Set dbs = Nothing
Set qdf = Nothing
 
txtReport = strResult
 
######## END CODE ###########

What am I missing?

Thank you so much for your help!

[Stewart Ross]

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

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