Populate combobox from ADO recordset

[JonoB]

Hi, I am trying to get a combobox on a form to be populated by a recordset. I am using the following code, but always get a "Type Mismatch" error on the "cboSiteName.RowSource = rsSite" line.

What am I doing wrong?

Thanks for the help!

Dim cnn As ADODB.Connection
Dim rsSite As ADODB.Recordset

'Create the connection.
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Temp\DatabaseBE.mdb;"

'Create recordset reference and set its properties.
Set rsSite = New ADODB.Recordset
With rsSite
Set .ActiveConnection = cnn
.Source = "SELECT SiteName from tblSite"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With


'Set the combobox rowsource to the ADO recordset
cboSiteName.RowSource = rsSite

'Close the recordset and connection
Set rsSite = Nothing
Set cnn = Nothing

 

[RoyVidar]

Hi!

Yes, when the rowsource type of combos are table/query, the rowsrource can be a table, query or an sql string. I don't think you can assign a recordset. Have you tried just using the sql string?

Else, the GetString method of the ADO recordset might be worth a try, perhaps something like this:

[tt] 'Set the combobox rowsource to the ADO recordset
cboSiteName.RowSource = rsSite.GetString(adClipString,,";",";")[/tt]

Remember to set the combos row source type to Value List.

Roy-Vidar

 

[PHV]

Have you tried this (untested):
Set cboSiteName.Recordset = rsSite

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonoB]

Thanks Roy and OHV for your answers.

PHV - tried yours, no luck, error message = "method or data member not found"

Roy, cant populate using just the sql string, as the source is a different database. I havent tried your getstring method yet....

It just doesnt make sense to me that you cant populate a combobox with a recordset....there must be a way!

 

[hymn]

have a look at

http://support.microsoft.com/default.aspx?scid=kb;en-us;182061

Hope this helps
Hymn

 

[JonoB]

Hi all, Have spent the better part of this morning getting this right..... here is the code for those that are interested.

By the way, I suspect that this can be accomplished a whole lot easier with AC2002, and by using me.cboSiteName.recordsource = rsSite, although I am not able to test this.

Dim cnn As ADODB.Connection
Dim rsSite As ADODB.Recordset
Dim strList As String
Dim strLength As Integer

'Create the connection.
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Temp\DatabaseBE.mdb;"

'Create recordset reference and set its properties.
Set rsSite = New ADODB.Recordset
rsSite.CursorType = adOpenKeyset
rsSite.LockType = adLockOptimistic

'Open recordset
With rsSite
Set .ActiveConnection = cnn
.Source = "SELECT SiteID,SiteName from tblSite ORDER BY SiteName"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

'Loop through the recordset and generate the string
strList = ""
If rsSite.RecordCount > 0 Then
MsgBox "You have " & rsSite.RecordCount & " records"
Do While Not rsSite.EOF
strList = strList & ";" & rsSite("SiteID") & ";" & rsSite("SiteName")
rsSite.MoveNext
Loop
ElseIf rsSite.RecordCount <= 0 Then
MsgBox "No records!"
End If

'Get rid of the invalid characters
'We have to get rid of "," in any of our SiteNames else our combobox will not work properly
strList = Replace(strList, ",", " ")

'This part for debugging only
MsgBox strList
strLength = Len(strList)
MsgBox strLength

'We need to get rid of the first ';'
strList = Right(strList, strLength - 1)
MsgBox strList 'Check to see if our list is OK

'Set up our combox properly and use strList as the rowsource
With Me.cboSiteName
.ColumnCount = 2
.BoundColumn = 1
.ColumnWidths = "0;2.5cm"
.RowSourceType = "Value List"
.RowSource = strList
End With

'Tidy up
rsSite.Close
cnn.Close
Set rsSite = Nothing
Set cnn = Nothing

 

[PWise]

try
SELECT SiteName from tblSite in 'C:\Temp\DatabaseBE.mdb';

 

[JonoB]

Ummm, I was not asking for help, but thanks anyway!

I am merely pasting my code for those who are wondering how I got it right, or for those who are looking to do the same thing.