Using a recordset as a Report RecordSource

[JoJoFromCoCoMo]

I have built an ADO recordset and want to use it for my
source data on a report.

It will not compile because I get a "Type Mismatch" error
on the following segment of code:

ADO code building recordset works:
....
' Execute once and display...
Set rs = cmd.Execute
Me.RecordSource = rs - issues error

I think it is looking for a SELECT statement rather than
a record set!

Is there a way to use a resulting recordset as the
recordsource for a report?

 

[Ken Snell [MVP]]

You can't assign a recordset to the Record Source property, but you can
assign a recordset to the Recordset property. Try this:

Set Me.Recordset = rs

 

[JoJoFromCoCoMo]

Thanks for the idea but it didn't work either...
I get a compile error: "Method or data member not found"

I also tried:

Set Me.Report.Recordset = rs

and it compiled but I get a run error of:
"Application defined or object defined error"

Any other ideads would be greatly appreciated.

 

[Guest]

Here is the actual code I'm using:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim rs As ADODB.Recordset
Call ConnectToPOSCheck 'connect to SQL from Module
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient

rs.Open _
" SELECT Merchants.MerchantState, " & _
" CASE TranSum.CleanRIDNumb " & _
" WHEN '763060' THEN 'NonPart' " & _
" WHEN '763057' THEN 'NonPart' " & _
" ELSE 'Part' " & _
" END AS PartOrNonPart, " & _
" SUM(TranSum.TransCount) AS TotalCount, SUM
(TranSum.TotalSettleAmt) AS TotalAmount, " & _
" CONVERT(DATETIME, '2004-07-01 00:00:00', 102) AS
StartDate, CONVERT(DATETIME, '2004-07-31 00:00:00', 102)
AS EndDate " & _
" FROM TranSum " & _
" LEFT OUTER JOIN v_SQLCreateBID_BIN ON
TranSum.AcqBINNumb = v_SQLCreateBID_BIN.AcqBINNumb AND "
& _
" TranSum.BIDNumb =
v_SQLCreateBID_BIN.BIDNumb " & _
" LEFT OUTER JOIN Merchants ON TranSum.AcqBINNumb =
Merchants.AcqBINNumb AND " & _
" TranSum.MerchantID =
Merchants.MerchantID " & _
" WHERE TranSum.TransDate >= CONVERT(DATETIME, '2004-07-
01 00:00:00', 102) AND " & _
" TranSum.TransDate <= CONVERT
(DATETIME, '2004-07-31 00:00:00', 102) " & _
" GROUP BY Merchants.MerchantState, " & _
" CASE TranSum.CleanRIDNumb " & _
" WHEN '763060' THEN 'NonPart' " & _
" WHEN '763057' THEN 'NonPart' " & _
" ELSE 'Part' " & _
" END " & _
" ORDER BY Merchants.MerchantState ", conn, adOpenKeyset,
adLockReadOnly

Set Me.Report.Recordset = rs

Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open
End Sub

 

[Ken Snell [MVP]]

Me.Report.Recordset is not valid. Me.Recordset is.

In which module and during which event are you trying to run this code?