Need help in generating a report from recordset

[dsk]

Hi,

I have the following code written in access vb to print a report form, the recordset. I have the record set defined and have the data inside the recordset as well. The problem I have is that I need to assign the name of the recordset to the RecordSource property of the report . I have all the code in a seperate function.

This is where i am trying to assign the RecordSource property with the recordset.This is done when the report is opened:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Recordset.Name
End Sub

I could not find the Name property for the recordset. Some how I need to assign the RecordSource property with the recordset in order to generate the report.

I would really appreciate if any one could help. I have also the remaining code below:

Option Compare Database
Public Recordset As ADODB.Recordset

Public Function test()
Dim Connection As New ADODB.Connection
''Public Recordset As ADODB.Recordset

On Error GoTo errh

Set Recordset = New ADODB.Recordset

txtSQLD = "Select * from ClaimPayment where ClaimID = 'P08208'"


Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\N_Data\EMR\emrdata.mdb"

Call Recordset.Open(txtSQLD, Connection, adOpenDynamic, adLockOptimistic)

DoCmd.OpenReport "rptClaimPaymentByNum", acViewPreview

Exit Function

errh:
'Code to close the recordset if open.
Recordset.Close
Set Recordset = Nothing
MsgBox Err.Description
MsgBox Err.Source
End Function

 

[Travis]

From Microsoft:
You can use the RecordSource property to specify the source of the data for a form or report. You can display data from a table, query, or SQL statement.

Access reports don't have a RecordSet Property. Access Forms do have a RecordSet Property. You can use this approach of setting the RecordSet Property if you are using outside report application such as Crystal.

Personally I wish you could do this with reports, but I quess we just can't have everything.

A quick way around this would be to make your Recordset into a temporary table, then you can have the report access the table. (Best if you use an outside MDB file that you link to keep the Bloat down)