Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    7

    Question Unanswered: Troublehooting using a RecordSet as a RecordSource

    Hello all,
    I have an Access db that I'm using as a DSN-less GUI for an Access/SQL Server application. The Access client piece contains no tables or queries, and SQL stored procedures and VBA code do all of the record handling.

    What I'm trying to set up at the moment is a form that will display multiple records passed from SQL to a RecordSet. I have had 'some' success at this using code examples from others. By 'some' I mean, by setting the form's RecordSource to the RecordSet, I get a number of rows in the form that equals the number of records being returned by SQL Server (seven in this case). However, all of the rows are blank.

    So my question is, how do I get the data to actually display?

    Here's the code I'm using to set the form's RecordSource to the RecordSet...

    Code:
    Public rsSubSummary As ADODB.Recordset
    
    Private Sub Form_Open(Cancel As Integer)
    Dim vParam As String
    
    Set rsSubSummary = New ADODB.Recordset
    rsSubSummary.CursorLocation = adUseClient '--May not be necessary, but was recommended in one example.
    
    vParam = "{call stp_SubSummary_Unfiltered}"
    Set rsSubSummary.DataSource = ReturnRecordset(vParam)
    
    Set Me.Recordset = rsSubSummary
    
    rsSubSummary.Close
    Set rsSubSummary = Nothing
    End Sub
    Some notes to keep in mind, vParam is a string carrying the SQL call procedure to a generic function (ReturnRecordset) that returns the SQL data in the form of a RecordSet. I have verified that records are being returned using a text string. And the fact that the correct number of rows are being created on the form suggests it is getting at least part of the RecordSet data.

    Any help is much appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Not the way I use recordsets and forms but... are you sure you want to set the recordset as the form's recordset... and then close it and set it to nothing?

    BTW - adUseClient is spot on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If ReturnRecordset returns an ADODB.Recordset, wouldn't you directly assign rsSubSummary to the results?

    rsSubSummary = ReturnRecordset(vParam) ?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2006
    Posts
    7
    Quote Originally Posted by pootle flump
    Hi

    Not the way I use recordsets and forms but... are you sure you want to set the recordset as the form's recordset... and then close it and set it to nothing?

    BTW - adUseClient is spot on.
    Hey Pootle! Honestly I'm not sure if I want to close it or not. I guess I kind of assumed that rsSubSummary wouldn't be needed anymore once the RecordSource was set?

    But I did try removing both the close and nothing statements and it did the exact same thing.


    Quote Originally Posted by Teddy
    If ReturnRecordset returns an ADODB.Recordset, wouldn't you directly assign rsSubSummary to the results?

    rsSubSummary = ReturnRecordset(vParam) ?
    Teddy, thanks for the reply but I'm not sure I follow you. I am setting rsSubSummary to the results. Are you asking why I don't set the form's RecordSource directly to ReturnRecordset(vParam)?

    If so, I didn't really think about it. I will try it now to see if it makes any difference.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'll admit I had to look up datasource property of ado recordset (never seen, never used):

    Quote Originally Posted by Access Help
    DataSource Property


    Indicates an object that contains data to be represented as a Recordset object.
    Remarks
    This property is used to create data-bound controls with the Data Environment. The Data Environment maintains collections of data (data sources) containing named objects (data members) that will be represented as a Recordset object.
    The DataMember and DataSource properties must be used in conjunction.
    The object referenced must implement the IDataSource interface and must contain an IRowset interface.
    Usage
    Code:
    Dim rs as New ADODB.Recordset
    rs.DataMember = "Command" 'Name of the rowset to bind to
    Set rs.DataSource = myDE 'Name of the object containing an IRowset
    I think Teddy is right - you want to set the recordset object itself to the function return (assuming the return is a recordset object).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2006
    Posts
    7
    Teddy, changing the RecordSource directly did the same thing; 7 rows with no data. I changed 'Me' to the form's real name here to make things a little more clear. Here's the code...

    Code:
    Dim vParam As String
    vParam = "{call stp_SubSummary_Unfiltered}"
    Set Forms![Sub_Summary].Recordset = ReturnRecordset(vParam)
    I just realized, though, that I'm not actually setting the RecordSource property in either piece of code, but rather the Recordset property. However, when I actually try to set the RecordSource property instead of the form's Recordset, I get an "Invalid Use of Property" error in both cases.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - if you are getting 7 "blank" records - have you set the recordsource of the controls?

    Recordsource property, btw, for forms and controls has to be a string. You are setting the right property.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2006
    Posts
    7
    Quote Originally Posted by pootle flump
    Actually - if you are getting 7 "blank" records - have you set the recordsource of the controls?

    Recordsource property, btw, for forms and controls has to be a string. You are setting the right property.
    Okay, I feel better about the Recordset property then. Thought I was being dense there for a second.

    Wait a minute, I take that back. I didn't have the controls' recordsources set. Matter of fact, I didn't even think to check that because I wasn't binding to a table. Jeez, I feel like a dork.

    Thanks guys, it's working just fine now.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    can you create a query based on the SQL Procedure call and base the form on the query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •