populating list box with Ado record set (1 Viewer)

sonny123

Registered User.
Local time
Today, 15:48
Joined
Apr 8, 2011
Messages
31
I am trying to populate a combo box on a client side access form from a server side stored procedure on sql server. using ado

Im am getting the record set accross and the debug.print prints out from 1 to 4 addresses but I cant seem to bind this record set to the combo box in my form (cboAddress)

the recordset property of the combo is set to Table/Query

i get runtime error 7965
the object you entered is not a valid record set property

i'm stumped
this what i have done
can anybody push me in the right direction?


Code:
Option Compare Database
Dim Addrs As ADODB.Recordset
 
 
Private Sub Form_Close()
Set Addrs = Nothing
End Sub
 
 
Private Sub PcodeIN_AfterUpdate()
Dim address As String
Dim strSQL As String
strSQL = "EXEC [Postcode1105].[dbo].[AddressFromPostcode] '" & Me.PcodeIN & "'"
 
OpenMyRecordset Addrs, strSQL
Addrs.MoveFirst
While (Not Addrs.EOF)
        With Addrs
                address = Addrs.Fields("Line1") _
                & IIf(Addrs.Fields("Line2") = "", "", vbNewLine) & Addrs.Fields("Line2") _
                & IIf(Addrs.Fields("Line3") = "", "", vbNewLine) & Addrs.Fields("Line3") _
                & IIf(Addrs.Fields("District") = "", "", vbNewLine) & Addrs.Fields("District") _
                & IIf(Addrs.Fields("County") = "", "", vbNewLine) & Addrs.Fields("County") _
                & vbNewLine & Addrs.Fields("PostCodeA") & " " & Addrs.Fields("PostCodeB") & vbNewLine
                Debug.Print address
            .MoveNext
        End With
Wend
[B][SIZE=3]Set Me.cboAddress.Recordset = Addrs[/SIZE][/B]
End Sub
 
Last edited:

VilaRestal

';drop database master;--
Local time
Today, 15:48
Joined
Jun 8, 2011
Messages
1,046
An ADO Recordset is not the same as a DAO Recordset and the combobox recordset is DAO.

Either you change the combo box to a value list and loop through the ADODB recordset adding its records as values.

Or you open a DAO Recordset and use that. Do you have DAO database object connected to the SQL Server. If not then this link might help: ado_to_dao.bas
 

Users who are viewing this thread

Top Bottom