How can I loop through a combo box's list to determine if a particular value
Here's a quick example of searching through *all* the rows and columns of a
combo box:
'*****EXAMPLE START
Private Sub txtComboFind_AfterUpdate()
' Comments :
' Parameters:
' Created : 10/29/03 18:04 BMT
' Modified :
'
' --------------------------------------------------
On Error GoTo txtComboFind_AfterUpdate_ERR
Dim strCompare As String
Dim iListCount As Integer
Dim iRow As Integer 'The Row
Dim iColumn As Integer 'The Column
'The search value
strCompare = Me.txtComboFind.Value
With Me.cboNames
'Get count of "data" rows
iListCount = .ListCount - 1 + Abs(.ColumnHeads)
'Cycle through Rows
For iRow = Abs(.ColumnHeads) To iListCount
'Cycle through Columns
For iColumn = 0 To .ColumnCount - 1
'Compare values in combo's list to string variable
If .Column(iColumn, iRow) = strCompare Then
Me.txtFindStat.Value = "Yes"
'Select first Row containing value and then
' exit sub
.Value = .ItemData(iRow)
Exit Sub
Else
Me.txtFindStat.Value = "No"
End If
Next iColumn
Next iRow
End With
txtComboFind_AfterUpdate_EXIT:
Exit Sub
txtComboFind_AfterUpdate_ERR:
MsgBox "Error " & Err.Number & _
" occurred in txtComboFind_AfterUpdate: " & Err.Description
Resume txtComboFind_AfterUpdate_EXIT
End Sub
'*****EXAMPLE END
You can modify the code to search only a specific column by replacing "iColumn"
in ".Column(iColumn, iRow)" with the column number and removing the lines ...
'Cycle through Columns
For iColumn = 0 To .ColumnCount - 1
.... and ...
Next iColumn
.... along with any other lines that don't suit your purpose. Obviously, all
control names would need to be changed to match those in your project.
Hopefully, this example will help you understand the technique I used.
