recordset from stored procedure

[farquar]

I have a form in Access 2000 and I have a stored procedure which queries a SQL-Server db. The sp returns a recordset and I want to view the contents of the recordset in a continuous form. My code is below but it only seems to want to return the last row in the recordset. Can you help ? I am fairly new to using sp with forms so have prob missed something simple.

Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim rst1 As Recordset

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "provider=sqloledb;driver={SQL Server};SERVER=srvname;DATABASE=dbname;trusted_connection=Yes"

cnn1.Open

Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = cnn1
cmd1.CommandText = "USP_TEST" 'sp name
cmd1.CommandType = adCmdStoredProc

Set rst1 = cmd1.Execute

MsgBox "no of records rst1 = " & rst1.RecordCount

Do Until rst1.EOF
Me.Text0 = rst1("address")
rst1.MoveNext
Loop

exit_odbcsetup:
Exit Sub

err_odbcsetup:
MsgBox "Err " & Err.Number & " " & Err.Description

rst1.Close
cnn1.Close

Set rst1 = Nothing
Set cnn1 = Nothing

 

[Eupher]

Hi, farquar,

Try this: remove the Do Until loop, and replace it with

Set Me.Recordset = rst1

HTH,

Ken S.

p.s. I don't know if this will work, as Access form recordsets are DAO by default... but give it a try.

 

[farquar]

Thanks - I have tried all those sorts of things, even setting recordsource but it doesn't like it. I put a counter in the loop and it goes through the 2400 records in my table but displays the last one in the form and the record selectors say 1 of 1.

 

[Eupher]

farquar,

"recordsource"? or recordset?

Ken S.

 

[farquar]

I tried what you suggested, being me.recordset and it said object variable or with block variable not set. I have tried me.recordsource = rst1 but it doesn't like that either. all my code is on the on load of a form. thanks.