CODEWITHSUNDEEP
ms-accesscomboboxvbams-access-2010
adp
adp

Reputation: 311

Ms Access: VBA combobox

I use the following VBA Code for combo boxes in MS Access Form 

Private Sub ComboEmployee_AfterUpdate()
Dim myFilter As String
myFilter = "Select * from Filter_Employee where ([LastName] = iif('" & ComboEmployee & "'='(All)',[LastName],'" & ComboEmployee & "'))"
Me.Employee_subform.Form.RecordSource = myFilter
Me.Employee_subform.Form.Requery
End Sub

This works Fine, the problem is that I have now a combobox which contains more than one column in the combobox (see picture) enter image description here

If I use the Code above It doesn't work .. How should I adapt my vba code, so that it works when Filtering.

Upvotes: 1

Views: 365

Answers (1)

HansUp
HansUp

Reputation: 97131

Since your code is in the combo's After Update event, you can adapt the SELECT to the combo value --- only add the WHERE clause when the combo value is not equal to "(All)". 

Private Sub ComboEmployee_AfterUpdate()
    Dim myFilter As String
    myFilter = "Select * from Filter_Employee"
    If Me.ComboEmployee.Value <> "(ALL)" Then
        myFilter = myFilter & " where [LastName] = '" & Me.ComboEmployee.Value & "'"
    End If
    Debug.Print myFilter '<- view this in Immediate window; Ctrl+g will take you there
    Me.Employee_subform.Form.RecordSource = myFilter
End Sub

Upvotes: 2

Related Questions