Multiple filters in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to filter records on my form based on 2 criteria. The first criteria
is to filter based on [Bus Unit Full Desc], I then want to take those records
and filter on [Posting Full Acct Desc]. I've got 2 combo boxes, one for Bus
Unit, and one for Posting Full Acct Desc. Right now, the filters are
"fighting" each other, not working WITH each other - I was hoping to get them
to work together to filter down to the records I want.

Any ideas? I'm fairly new at VBA, and looking through books to try and
figure this out.

1st Combo Box
Private Sub cboBU_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.FilterOn = True

End Sub

2nd Combo Box
Private Sub cboObject_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.Filter = "[Posting Full Acct Desc] = Forms!frmBaseData!cboObject"
Me.FilterOn = True

End Sub
 
Kirk P. said:
I want to filter records on my form based on 2 criteria. The first criteria
is to filter based on [Bus Unit Full Desc], I then want to take those records
and filter on [Posting Full Acct Desc]. I've got 2 combo boxes, one for Bus
Unit, and one for Posting Full Acct Desc. Right now, the filters are
"fighting" each other, not working WITH each other - I was hoping to get them
to work together to filter down to the records I want.

Any ideas? I'm fairly new at VBA, and looking through books to try and
figure this out.

1st Combo Box
Private Sub cboBU_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.FilterOn = True

End Sub

2nd Combo Box
Private Sub cboObject_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.Filter = "[Posting Full Acct Desc] = Forms!frmBaseData!cboObject"
Me.FilterOn = True

End Sub
Click to expand...


Both combos need to check each other, so create a function
that to do that:

Private Function BuildFilter() As Variant
BuildFilter = (" And [Bus Unit Full Desc] = """ + Me.cboBU +
"""") & (" And [Posting Full Acct Desc] = """ + Me.cboObject
+ """") & ""
BuildFilter = Mid(BuildFilter, 6)
End Function

Note the careful use of both + and & to do the
concatenation.

Then both AfterUpdate events would be:

Me.Filter = BuildFilter()
Me.FilterOn = True
 
Marshall,

Your solution worked perfectly. Just wondering if you can clue me in a
little more on how that function worked. I understand the concept of
concatenation, but the syntax of the function confuses me.

Thanks!

Marshall Barton said:
Kirk P. said:
I want to filter records on my form based on 2 criteria. The first criteria
is to filter based on [Bus Unit Full Desc], I then want to take those records
and filter on [Posting Full Acct Desc]. I've got 2 combo boxes, one for Bus
Unit, and one for Posting Full Acct Desc. Right now, the filters are
"fighting" each other, not working WITH each other - I was hoping to get them
to work together to filter down to the records I want.

Any ideas? I'm fairly new at VBA, and looking through books to try and
figure this out.

1st Combo Box
Private Sub cboBU_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.FilterOn = True

End Sub

2nd Combo Box
Private Sub cboObject_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.Filter = "[Posting Full Acct Desc] = Forms!frmBaseData!cboObject"
Me.FilterOn = True

End Sub
Click to expand...


Both combos need to check each other, so create a function
that to do that:

Private Function BuildFilter() As Variant
BuildFilter = (" And [Bus Unit Full Desc] = """ + Me.cboBU +
"""") & (" And [Posting Full Acct Desc] = """ + Me.cboObject
+ """") & ""
BuildFilter = Mid(BuildFilter, 6)
End Function

Note the careful use of both + and & to do the
concatenation.

Then both AfterUpdate events would be:

Me.Filter = BuildFilter()
Me.FilterOn = True
Click to expand...
 
Kirk said:
Your solution worked perfectly. Just wondering if you can clue me in a
little more on how that function worked. I understand the concept of
concatenation, but the syntax of the function confuses me.
Click to expand...

THere are two concatenation operators, & and +. THe
difference between them is that & does not propogate Null,
but + does. E.g.

"A" + Null returns Null
"A" & Null returns "A"

so if one of your controls does not have a value, the rest
of that one condition is Null. The & between the two
conditions will return whatever was specified. The
parenthesis are critical to make sure that the + only
operates on the appropriate pieces of the expression.
--
Marsh
MVP [MS Access]


Kirk P. said:
I want to filter records on my form based on 2 criteria. The first criteria
is to filter based on [Bus Unit Full Desc], I then want to take those records
and filter on [Posting Full Acct Desc]. I've got 2 combo boxes, one for Bus
Unit, and one for Posting Full Acct Desc. Right now, the filters are
"fighting" each other, not working WITH each other - I was hoping to get them
to work together to filter down to the records I want.

Any ideas? I'm fairly new at VBA, and looking through books to try and
figure this out.

1st Combo Box
Private Sub cboBU_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.FilterOn = True

End Sub

2nd Combo Box
Private Sub cboObject_AfterUpdate()
' Apply or remove the filter for the option chosen by user.

Me.Filter = "[Bus Unit Full Desc] = Forms!frmBaseData!cboBU"
Me.Filter = "[Posting Full Acct Desc] = Forms!frmBaseData!cboObject"
Me.FilterOn = True

End Sub
Click to expand...
Click to expand...
Marshall Barton said:
Both combos need to check each other, so create a function
that to do that:

Private Function BuildFilter() As Variant
BuildFilter = (" And [Bus Unit Full Desc] = """ + Me.cboBU +
"""") & (" And [Posting Full Acct Desc] = """ + Me.cboObject
+ """") & ""
BuildFilter = Mid(BuildFilter, 6)
End Function

Note the careful use of both + and & to do the
concatenation.

Then both AfterUpdate events would be:

Me.Filter = BuildFilter()
Me.FilterOn = True
Click to expand...
Click to expand...
 
Back
Top