with dynamic recordset, field data is blank

[jordanking]

Hello,

I have a report that is based on a dynamically generated recordset, but the filds in the report are all empty. They are all named correctly and have the right field name for control source.

The report has the right number of rows (178), but they are all blnak

has anyone encountered this?

here is my code

Private Sub Report_Open(Cancel As Integer)
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    ''''initialise connection string and open connection
    cnn.Open Application.CurrentProject.Connection

    Set rs = DMS_Residential_DriverList_Get(cnn, Forms!frmReport!dtDriverList)

    Debug.Print rs.RecordCount
    Debug.Print rs.Fields(0)

    Set Me.Recordset = rs

End Sub

DMS_Residential_DriverList_Get is teh name of a custom function that returns a adodb recordset

.....
I'd rather be surfing

 

[JoeAtWork]

I found out a few months ago that it's not possible to set the recordset of a report dynamically (unlike forms, where it is possible). At least that was for Access2003, I don't know if that has changed for Access2007.

My work around was to populate a temp table and bind the report to it.

By the way, instead of opening a new connection object, I would suggest you just do this:

Set cnn = Application.CurrentProject.Connection

 

[RyanNL]

Just found this via google. Came against this problem again today, and had totally forgotten that I'd found a solution to it a few months back!
What you need to do is bind the recordset by it's name property.

I used the below code to create a report based on the results of an UDF..

[tt]
Private qdf As DAO.QueryDef
Private rs As DAO.Recordset

Private Sub Report_Open(Cancel As Integer)

Dim strConnectionString As String
Dim strSQL As String

strConnectionString = "ODBC;DSN=dbname;UID=sa;PWD=password;DATABASE=dbname"

strSQL = "SELECT field1, field2 FROM dbo.udf_which_returns_recordset(udf_arguments)"

Set qdf = CurrentDb.CreateQueryDef("qdftemp")

With qdf

.Connect = strConnectionString
.SQL = strSQL
Set rs = .OpenRecordset(dbOpenSnapshot)

End With

Me.RecordSource = rs.Name

End Sub

Private Sub Report_Close()

On Error Resume Next

CurrentDb.QueryDefs.Delete ("qdftemp")

rs.Close
qdf.Close

Set rs = Nothing
Set qdf = Nothing

End Sub
[/tt]

You should change the name [tt]qdftemp[/tt] to something unique.