CODEWITHSUNDEEP
ms-accesscomboboxvba
A Lawliet
A Lawliet

Reputation: 43

Filtering with combo in Access not working properly

I've been trying to filter the result of a SQL query by using a combo box. I need it to be done with AfterUpdate() because I need it to show all the resutls (unfiltered) and then, if wanted, filter based on the content of the combo box.

Here "Cuadro_combinado30" is the name of the combo box and "Dossier" is the name of the field I want to filter by.

Private Sub Cuadro_combinado30_AfterUpdate()

Dim strFilter As String

With Me.Cuadro_combinado30
    If IsNull(.Value) Or .Value = "**ALL**" Then
       ' If the combo box is cleared or ALL selected, clear the form filter.
       Me.Filter = vbNullString
       Me.FilterOn = False
    Else
        ' item other than ALL is selected, filter for an exact match.
        strFilter = "[Dossier] = '" & _
            Replace(.Value, "'", "''") & "'"
        Debug.Print strFilter ' check this in Immediate window in case of 
        ' trouble you can use Ctrl+g to go to the Immediate window
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
    End With

End Sub

So far, the result has been that when I enter the form, all results are showing, but when I select one option of the combo box in order to filter the results, then the query does not seem to find anything as no result is shown.

Why is this code not working and what should I modify for it to work?

Thank you.

UPDATE: I've tried to use a text box instead of a combo box, and it works, when I type something on the text box the results are filtered based on what I just wrote. I guess it has to do with how the combo box is created, it seems that even when the combo box is showing content, the filter finds a NULL on it.

Upvotes: 0

Views: 584

Answers (2)

A Lawliet
A Lawliet

Reputation: 43

Turned out the combo box has 2 columns instead of one, @Santosh solution worked perfectly:

 Replace(.Column(1), "'", "''")

Upvotes: 1

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

Change the combo properties to one column should solve the problem

Upvotes: 0

Related Questions