Binding an ADO Recordset returned from a Stored Procedure to A Form

I have the following code that returns values from a Stored procedure. However, don't appear to be able to bind the RecordSet to a form. The Stored procedure is running and I can see Field  values returned.

Dim rst As New ADODB.Recordset
Dim con As New ADODB.Connection

IdValueToProcess = 12300  ' test data

con.ConnectionString = "????"
rst.Open "EXEC dbo.Martin " & IdValueToProcess, con

Debug.Print rst!Surname

Set Forms!Form1.Recordset = rat

On the last line, I get an error message saying

The object you entered is not a valid Recordset property

Any suggestions?
Who is Participating?
lluddenConnect With a Mentor Commented:
You can create a pass through query in Access, and bind that query to the form.

You can set the sql before you need it:

Application.CurrentDb.QueryDefs("queryName").SQL = "EXEC dbo.Martin " & IdValueToProcess

Open in new window

martmacAuthor Commented:
Thanks very much, not the way I wanted to go, but it does help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.