CODEWITHSUNDEEP
vbams-access
veckorevyn
veckorevyn

Reputation: 185

If combobox value is Sample, search column that matches Sample

I'm trying to create search feature, that lets the user to pick a desired column value from a combobox (say, Name, for example), type the thing they're searching for (ie. Alex) and press the search button, the program selects all the rows that have "Alex" in the "Name" column.

This is what I have so far, the With portion works without the If statements, so I guess I did something wrong there. The lngColumn and lngRow were set as Long at first, but it gave me Invalid use of null error so I changed them to variant.

Private Sub cmdSearch_Click()
Dim strSearch As Variant, lngColumn As Variant, lngRow As Variant
Dim strValue As String
strSearch = Me.txtSearch
strValue = Me.cboSearch

If strValue = "Name" Then
    lngColumn = Me.lstTest.Column(1)
ElseIf strValue = "Occupation" Then
    lngColumn = Me.lstTest.Column(2)
ElseIf strValue = "Location" Then
    lngColumn = Me.lstTest.Column(3)
    With Me.lstTestReports
        For lngRow = 0 To .ListCount - 1
            If (.Column(lngColumn, lngRow)) = strSearch Then
                .Selected(lngRow) = True
            Else
                .Selected(lngRow) = False
            End If
        Next
    End With
End If

End Sub

Upvotes: 1

Views: 83

Answers (1)

Andre
Andre

Reputation: 27634

You were correct with lngColumn As Long - you use them in

.Column(lngColumn, lngRow)

later, so you mustn't store the listbox value, but the column number.

Also, the With Me.lstTestReports part belongs outside the If.

Private Sub cmdSearch_Click()
Dim strSearch As Variant, lngColumn As Long, lngRow As Long
Dim strValue As String
strSearch = Me.txtSearch
strValue = Me.cboSearch

If strValue = "Name" Then
    lngColumn = 1
ElseIf strValue = "Occupation" Then
    lngColumn = 2
ElseIf strValue = "Location" Then
    lngColumn = 3
End If

With Me.lstTestReports
    For lngRow = 0 To .ListCount - 1
        If (.Column(lngColumn, lngRow)) = strSearch Then
            .Selected(lngRow) = True
        Else
            .Selected(lngRow) = False
        End If
    Next
End With

Upvotes: 2

Related Questions