Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004

    Unanswered: HELP with subform's recordset

    I'm using ADO in Access 2003 (MDB) database.

    I'm trying to set a subform's recordset to a recordset returned by a SQL stored procedure and I get an error message.

    The stored procedure takes two parameters and it works fine, it returns the correct information if I run it directly in SQL server;
    however, it seems to return the recordset in the wrong format when ran from the Access application. Therefore, it makes my code fail when it tries to set the subform's recordset = to the recordset returned by the stored procedure.

    If I replace the stored procedure for a simple SELECT * FROM TABLE statement query against the SQL server, the code works fine and sets the subform's recordset to that of the returning query.
    Unfortunately, I need to use a stored procedure not a select statement.

    What am I missing in my code?? Here it is:

    Dim Mysql as string

    sql="sp_enter_data '" & StrInput & "'," & IntListValue ' my stored procedure and its parameters

    Set rstVersion = New ADODB.Recordset ' I set a new ADO recordset

    rstVersion.Open MySQL, Cnxn1, adOpenKeyset, adLockReadOnly, adCmdText ' I open the recordset

    Set Forms!frmenterdatamenu.SubSelection.Form.Recordset = rstVersion ' I set the subform's recordset to the returning recordset

    Then I get:
    "ERROR: The object you entered is not a valid Recordset property (Error 7965)"

    If I use a message box with a loop to make sure the stored procedure is returning the correct recordset, like so:

    'Do Until rstVersion.EOF
    ' strtest1 = rstVersion![1]
    ' strtest2 = rstVersion![2]
    ' strtest3 = rstVersion![3]
    ' strtest4 = rstVersion![4]
    ' strtest5 = rstVersion![5]
    ' msgbox strtest1, strtest2, strstest3, strtest4, strtest5
    ' rstVersion.MoveNext

    but If I do a record count of the returned recordset like so:

    dim count

    count = rstversion.recordcount

    I GET "count= -1" I DON'T UNDERSTAND!!

    Any help is and will be greatly appreciated...



  2. #2
    Join Date
    Apr 2004

    This is over my head but

    Don't you want to set the forms RecordSource property?

  3. #3
    Join Date
    May 2004


    Yes, I do.
    But I can't set the record source property of the form that way (as shown above), is there a way to do it like that?

  4. #4
    Join Date
    Feb 2004
    One Flump in One Place
    I suspect that this is a typo in the post or an error in cutting and pasting, but you dim Mysql, don't assign it a value (your code reads sql = "....", not Mysql = "....") then use Mysql as your recordset argument. Thought it worth a shout.

    ok, now I know it's a typo coz I saw your post on another forum
    Last edited by pootle flump; 05-11-04 at 15:44. Reason: Scanning the web and...
    pootle flump
    ur codings are working excelent.

Posting Permissions

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