Filtering forms

[linwork]

Hi,
I was wondering if you could help me with a problem. I have 2 combos in an access form and I would like to be able to filter based on both of them.
My first combo has usernames and the second one has company names. So I would like users to be able to pick their name from the first drop down and then pick the company name from the second drop down list so they can see what issues for that company are assigned to them.

I am currently filtering each one using this method as recommended in a previous post:

Private Sub cboCompany_AfterUpdate() 
If Not IsNull(Me![cboCompany]) Then 
  Me.Filter = "[Company]='" & Me![cboCompany] & "'" 
  Me.FilterOn = True 
End If 
End Sub

It works great if I only want to filter one at a time.
Any help you can provide is great! I am using access 2007.
Thanks!

[MikeTheBike]

@linwork
Hi

Perhaps something like this

Private Sub cboCompany_AfterUpdate()
    SetFilter
End Sub
Private Sub cboUsername_AfterUpdate()
    SetFilter
End Sub
Private Sub SetFilter()
    Dim FilterCriteria As String
    If cboCompany & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Company]='" & cboCompany & "'"
    If cboUsername & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Username]='" & cboUsername & "'"
    If FilterCriteria = "" Then
            Me.FilterOn = False
    Else
        FilterCriteria = Mid(FilterCriteria, 6) 'REMOVE THE LEADING " AND "
        Me.Filter = FilterCriteria
        Me.FilterOn = True
    End If
End Sub

(disclaimer!)
Note: Untried air code.

MTB

(ps. I know there is some redundant code in the first IF statment, but it does mean you can add criteria anywhere without worrying!!)

[linwork]

This works perfectly. Thank MTB!

[NeoPa]

A tutorial related to this can be found at Example Filtering on a Form.

I doubt you'll need it as MTB has clearly answered your question, however other searchers may be interested hence the post.

Welcome to Bytes!