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:
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...