I am using an SQL Server 2000 backend and an Access 2000 frontend to a database I am (kind of!) making.
My problem is that for one of my forms I want to display a certain record (with a unique ID and CustomerID) and on a subform display in datasheet view all records with the same CustomerID but not the one in the main form. (don't worry I will get to some VB!)
The way I am doing this is putting a Stored Procedure on SQL Server that is passed the Unique ID of the record and it's CustomerID and returns the recordset I want. The SQL procedure works fine.. it returns the records related to the CustomerID but not the record that has the selected Unique ID.
My problem occurs when I use ADO to call that procedure and it returns the recordset. I then want to bind that recordset to the subform, I am using the code me.recordset = objRS <- the returned recordset, inside the subforms On_Load sub. I thought that this would work but I get the error message "The object you entered is not a valid recordset property" for the above line. From help files I found that this error is about trying to use a disconnected ADO recordset in this way but my recordset definitly has an active connection.
I am running out of ideas of why this would be (the only thing I can think of is that it has something to do with the subform) but I would like some advise as to whether I could do this with ADO recordsets etc..
If anyone wants I will put up my code but atm it is in a real mess (I've been playing with it all morning) so I will see if there are any responses first.
Thanks in advance