Loading multi column combo box

[ronhvb0357]

I'm trying to load a multi column combo box from a recordset using the following code.

Dim db As Object
Dim rs As ADODB.Recordset
Set rss = New ADODB.Recordset
Dim fldEach As ADODB.Field
Dim i As Integer
Dim strSearch As String

strSearch = Me.cboFldOff.Text
MsgBox ("cbo value = " & strSearch)

Combo5.Requery


Dim strSQL As String

strSQL = "Select PHA_Code, PHA_Name FROM pha_codes_names" & _
" WHERE Field_Office = " & " '" & strSearch & "'"

'rs.Source = "SELECT TeamName, TeamNum from Teams"
'rs.ActiveConnection = Application.CodeProject.Connection
'rs.CursorType = adOpenStatic
'rs.LockType = adLockOptimistic
'rs.Open



rss.Open strSQL, _
Application.CodeProject.Connection, _
adLockOptimistic, adCmdText

' MsgBox ("I opened I think" & rss.RecordCount)

If rss.RecordCount > 0 Then
rss.MoveFirst

With Me.Combo5

Do
Combo5.AddItem (i)
Combo5.Column(0, i) = rss![PHA_Code]
Combo5.Column(1, i) = rss![PHA_Name]
rss.MoveNext
i = i + 1
Loop Until rss.EOF
End With
End If

rss.Close
Set rss = Nothing

Me.cboFldOff.Text is the value from another combo box.

Thanks in advance.

 

[Andrzejek]

First I would suggest to use [tt]Option Explicit[/tt] at the top of your code.
This way you will see you don't declare [tt]rss[/tt] anywhere :-(

Have fun.

---- Andy

 

[PHV]

Why not simply this ?

Me!Combo5.RowSource = "SELECT PHA_Code,PHA_Name FROM pha_codes_names" & _
 " WHERE Field_Office='" & Me!cboFldOff & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886