bind adodb recordset to ADP report. 1

[jordanking]

Hello,

I have several forms in an access databse project. I am able to set the recordset value through VBA using an adodb connection to sql server and adodb recordset.

I am running into trouble when trying to do the same with access reports. I found some documentation (

http://support.microsoft.com/kb/q301987/)

that mentioned previous versions of access, under 2002, you could not do this. But now you can, if you are using a ADP.

the following is the error i get when trying to set the recorset property of a report in the onload event.

run-time error '7965'
The object you entered is not a valid recordset property

the following is the code that I used

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim rsCust As ADODB.Recordset
    Set rsCust = New ADODB.Recordset
    
    ''''initialise connection string and open connection
    cnn.Open Application.CurrentProject.Connection
    rsCust.CursorType = adUseClient
    Set rsCust = DMS_Customer_Search(cnn, Forms!frmCustomerSearch.Form, Forms!frmCustomerSearch.Form!lstColumn, Forms!frmCustomerSearch.Form!lstSort.Value, Nz(Forms!frmCustomerSearch.Form!intCompanyID.Value, 0), Nz(Forms!frmCustomerSearch.Form!chrFirstName, "NULL"), Nz(Forms!frmCustomerSearch.Form!chrLastName, "NULL"), Nz(Forms!frmCustomerSearch.Form!ChrBilling, "NULL"), Nz(Forms!frmCustomerSearch.Form!intAccountStatus, 0), Nz(Forms!frmCustomerSearch.Form!intSDBID, 0), Nz(Forms!frmCustomerSearch.Form!intERUserID, 0), Nz(Forms!frmCustomerSearch.Form!blnErequest, 2), Nz(Forms!frmCustomerSearch.Form!blnMail, 2), Nz(Forms!frmCustomerSearch.Form!dtCreated, 0))
    Debug.Print rsCust.RecordCount
    Set Me.Report.Recordset = rsCust
    
    '''release recordset resources
    rsCust.Close
    Set rsCust = Nothing
    
    cnn.Close
    
End Sub

the recordset will return a record count and i know it is valid because i used this exact code elsewhere to set the recordset value of a form. The error is not thrown until the sub completes its code.

Does anyone have any ideas as to where this error is comming from or how to set the value of a ADP report to a recordset?


.....
I'd rather be surfing

 

[PWise]

If your problem is the same as describe in this post

then I am sorry that i did not get around to post the solution

http://www.tek-tips.com/viewthread.cfm?qid=1407341&page=1

but let me post it right here

Private Sub Report_Open(Cancel As Integer)
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim rsCust As ADODB.Recordset
    Set rsCust = New ADODB.Recordset
    
    ''''initialise connection string and open connection
    cnn.Open Application.CurrentProject.Connection
    rsCust.CursorType = adUseClient
    Set rsCust = DMS_Customer_Search(cnn, Forms!frmCustomerSearch.Form, Forms!frmCustomerSearch.Form!lstColumn, Forms!frmCustomerSearch.Form!lstSort.Value, Nz(Forms!frmCustomerSearch.Form!intCompanyID.Value, 0), Nz(Forms!frmCustomerSearch.Form!chrFirstName, "NULL"), Nz(Forms!frmCustomerSearch.Form!chrLastName, "NULL"), Nz(Forms!frmCustomerSearch.Form!ChrBilling, "NULL"), Nz(Forms!frmCustomerSearch.Form!intAccountStatus, 0), Nz(Forms!frmCustomerSearch.Form!intSDBID, 0), Nz(Forms!frmCustomerSearch.Form!intERUserID, 0), Nz(Forms!frmCustomerSearch.Form!blnErequest, 2), Nz(Forms!frmCustomerSearch.Form!blnMail, 2), Nz(Forms!frmCustomerSearch.Form!dtCreated, 0))
[COLOR=red]

do until rsCust.state = adStateOpen
    set rsCust=rsCust.nextrecordset
loop
[/color]
    Debug.Print rsCust.RecordCount
    Set Me.Report.Recordset = rsCust
    
    '''release recordset resources
    rsCust.Close
    Set rsCust = Nothing
    
    cnn.Close
    
End Sub

   
loop

Set Me.Report.Recordset = rsCust

 

[jordanking]

thanks for the reply pwise,

I tried putting your code into the report module and it still returns the same error. I debug.printed the state value just before the
set me.rscust.recordset = rscust
and it returns 1 or open.


what I am trying to do is clone the recordset from an open form and set it to the report's recordset.

here is my stripped down code

Private Sub Report_Open(Cancel As Integer)
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim rsCust As ADODB.Recordset
    Set rsCust = New ADODB.Recordset

    ''''initialise connection string and open connection
    cnn.Open Application.CurrentProject.Connection
    
    ''' clone recordset from customer search subform
    Set rsCust = Forms!frmCustomerSearch.Form!fsubCustomerSearch.Form.RecordsetClone
    
    Do Until rsCust.State = adStateOpen
        Set rsCust = rsCust.NextRecordset
    Loop

    Debug.Print rsCust.State
    Debug.print rsCust.RecordCount
    ''' set the recordset value of this report to the recordset close from the search subreport
    Set Me.Recordset = rsCust
    
    '''release recordset resources
    rsCust.Close
    Set rsCust = Nothing
    
    cnn.Close
End Sub

The recordset is open and returns a recordcount etc. but I still get the following error, not when I set the recordset, but when the sub get to the the exit sub or last line.

run-time error '7965'
The object you entered is not a valid recordset property

I can set the RecordSource of the form to a sql string and it works, but I have yet to get anything to work for setting the recordset value.

PHV,
the Set rsCust = DMS_Customer_Search is a custom function that is used to execute a stored procedure on sql server and its return value is a adodb recordset.


.....
I'd rather be surfing

 

[PWise]

rsCust.Close
    Set rsCust = Nothing
    
    cnn.Close

try not closeing recordset and Connection

 

[jordanking]

thanks pwise, It works,
glowing orb for you,


the open sub needes to complete without closing the connection or the recordset in order to succesfully bind the data to the report. so I worked with this idea a bit and came up with this finished code for the report's modlue:

Option Compare Database
Option Explicit

Public rsCust As ADODB.Recordset

Private Sub Report_Load()
    Set rsCust = Nothing
End Sub

Private Sub Report_Open(Cancel As Integer)
    Set rsCust = New ADODB.Recordset
    
    ''' clone recordset from customer dearch subform
    Set rsCust = Forms!frmCustomerSearch.Form!fsubCustomerSearch.Form.RecordsetClone
    
    ''' set the recordset value of this report to the recordset close from the search subreport
    Set Me.Recordset = rsCust

End Sub

I am picky about clearing up variable resources, do I need to release the recordset in the onload event, or if I declare it privatly within the open sub, are the recordste's resources cleared once the open sub completes?


This turned out to be a nice way to only call the stored procedure from sql server once and clone the recordset for use in both the from and the report.


.....
I'd rather be surfing