Applying a Filter Using Multiple Option Groups

  • Thread starter Thread starter Jeff Garrison
  • Start date Start date
J

Jeff Garrison

Hell all -

I have a quick quesiton...(first off, I'n using Access 2007 FE and SQL
BE)...on a form, I would like to use 2 Option Groups to apply filters to the
form. Details below...

Option Group 1

All Records (No Filter)
Active Only (Filter Only Active Records)
Inactive Only (Filter Only Inactive Records)

Option Group 2

Company Employees
Temp Employees
All Employees

I'd like to do a filter with a combination of the 2 Option Groups...being
able to select options from both groups and filter the records based on the
Option selected.

Any help would be musch appreciated.

Thanks.

JeffG
 
The following would all go into your form module. Add error handling, adjust
control/field names, etc., as appropriate for your reality.

'***** start aircode **********
Option Explicit
'(Top-of-form module) Module-level variables:
Private mstrRecordFilter as String
Private mstrEmpFilter as String

Private Sub frmOption1_AfterUpdate()
Select Case Me.frmOption1
Case 1
'All records
mstrRecordFilter = ""
Case 2
mstrRecordFilter = "[Active] = True"
Case 3
mstrRecordFilter = "[Active] = False"
End Select
Call ApplyFormFilter
End Sub


Private Sub frmOption2_AfterUpdate()
Select Case Me.frmOption2
Case 1
mstrEmpFilter = "[EmpType] = 'Company'"
Case 2
mstrEmpFilter = "[EmpType] = 'Temp'"
Case 3
' All employees
mstrEmpFilter = ""
End Select
Call ApplyFormFilter
End Sub

Private Sub ApplyFormFilter()
Dim strCombinedFilter as String

' Combine the 2 filters
Select Case True
' Only one of these 3 things will execute: the 1st True
condition encountered
Case mstrRecordFilter = ""
strCombinedFilter = mstrEmpFilter
Case mstrEmpFilter = ""
strCombinedFilter = mstrRecordFilter
Case Else
' User wants to filter on multiple fields
strCombinedFilter = mstrRecordFilter & " AND " &
mstrEmpFilter
End Select

'Apply the combined filter
Me.Filter = strCombinedFilter
If strCombinedFilter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End Sub
'******* end aircode **************************
 
George -

Works like a charm.....THANKS!

Jeff


George Nicholson said:
The following would all go into your form module. Add error handling,
adjust control/field names, etc., as appropriate for your reality.

'***** start aircode **********
Option Explicit
'(Top-of-form module) Module-level variables:
Private mstrRecordFilter as String
Private mstrEmpFilter as String

Private Sub frmOption1_AfterUpdate()
Select Case Me.frmOption1
Case 1
'All records
mstrRecordFilter = ""
Case 2
mstrRecordFilter = "[Active] = True"
Case 3
mstrRecordFilter = "[Active] = False"
End Select
Call ApplyFormFilter
End Sub


Private Sub frmOption2_AfterUpdate()
Select Case Me.frmOption2
Case 1
mstrEmpFilter = "[EmpType] = 'Company'"
Case 2
mstrEmpFilter = "[EmpType] = 'Temp'"
Case 3
' All employees
mstrEmpFilter = ""
End Select
Call ApplyFormFilter
End Sub

Private Sub ApplyFormFilter()
Dim strCombinedFilter as String

' Combine the 2 filters
Select Case True
' Only one of these 3 things will execute: the 1st True
condition encountered
Case mstrRecordFilter = ""
strCombinedFilter = mstrEmpFilter
Case mstrEmpFilter = ""
strCombinedFilter = mstrRecordFilter
Case Else
' User wants to filter on multiple fields
strCombinedFilter = mstrRecordFilter & " AND " &
mstrEmpFilter
End Select

'Apply the combined filter
Me.Filter = strCombinedFilter
If strCombinedFilter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End Sub
'******* end aircode **************************

--
HTH,
George



Jeff Garrison said:
Hell all -

I have a quick quesiton...(first off, I'n using Access 2007 FE and SQL
BE)...on a form, I would like to use 2 Option Groups to apply filters to
the form. Details below...

Option Group 1

All Records (No Filter)
Active Only (Filter Only Active Records)
Inactive Only (Filter Only Inactive Records)

Option Group 2

Company Employees
Temp Employees
All Employees

I'd like to do a filter with a combination of the 2 Option Groups...being
able to select options from both groups and filter the records based on
the Option selected.

Any help would be musch appreciated.

Thanks.

JeffG
Click to expand...
Click to expand...
 
In a related question (sort of)...

How do you set the default option when the form is opened?

I used to remember how to do that, but it's been many years...


George Nicholson said:
The following would all go into your form module. Add error handling,
adjust control/field names, etc., as appropriate for your reality.

'***** start aircode **********
Option Explicit
'(Top-of-form module) Module-level variables:
Private mstrRecordFilter as String
Private mstrEmpFilter as String

Private Sub frmOption1_AfterUpdate()
Select Case Me.frmOption1
Case 1
'All records
mstrRecordFilter = ""
Case 2
mstrRecordFilter = "[Active] = True"
Case 3
mstrRecordFilter = "[Active] = False"
End Select
Call ApplyFormFilter
End Sub


Private Sub frmOption2_AfterUpdate()
Select Case Me.frmOption2
Case 1
mstrEmpFilter = "[EmpType] = 'Company'"
Case 2
mstrEmpFilter = "[EmpType] = 'Temp'"
Case 3
' All employees
mstrEmpFilter = ""
End Select
Call ApplyFormFilter
End Sub

Private Sub ApplyFormFilter()
Dim strCombinedFilter as String

' Combine the 2 filters
Select Case True
' Only one of these 3 things will execute: the 1st True
condition encountered
Case mstrRecordFilter = ""
strCombinedFilter = mstrEmpFilter
Case mstrEmpFilter = ""
strCombinedFilter = mstrRecordFilter
Case Else
' User wants to filter on multiple fields
strCombinedFilter = mstrRecordFilter & " AND " &
mstrEmpFilter
End Select

'Apply the combined filter
Me.Filter = strCombinedFilter
If strCombinedFilter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End Sub
'******* end aircode **************************

--
HTH,
George



Jeff Garrison said:
Hell all -

I have a quick quesiton...(first off, I'n using Access 2007 FE and SQL
BE)...on a form, I would like to use 2 Option Groups to apply filters to
the form. Details below...

Option Group 1

All Records (No Filter)
Active Only (Filter Only Active Records)
Inactive Only (Filter Only Inactive Records)

Option Group 2

Company Employees
Temp Employees
All Employees

I'd like to do a filter with a combination of the 2 Option Groups...being
able to select options from both groups and filter the records based on
the Option selected.

Any help would be musch appreciated.

Thanks.

JeffG
Click to expand...
Click to expand...
 
How do you set the default option when the form is opened?

the default value of the OptionFrame can be preset in design view.
(Or Me.frmOption.DefaultValue = 1 I suppose)

If you want to select something other than the default when the form opens:

in Form_Open:

Me.frmOption1 = 1
Me.frmOption2 = 3
' If you also want the AfterUpdate code to run (and apply the filter) based
on those settings:
frmOption1_AfterUpdate
frmOption2_AfterUpdate

--
HTH,
George


Jeff Garrison said:
In a related question (sort of)...

How do you set the default option when the form is opened?

I used to remember how to do that, but it's been many years...


George Nicholson said:
The following would all go into your form module. Add error handling,
adjust control/field names, etc., as appropriate for your reality.

'***** start aircode **********
Option Explicit
'(Top-of-form module) Module-level variables:
Private mstrRecordFilter as String
Private mstrEmpFilter as String

Private Sub frmOption1_AfterUpdate()
Select Case Me.frmOption1
Case 1
'All records
mstrRecordFilter = ""
Case 2
mstrRecordFilter = "[Active] = True"
Case 3
mstrRecordFilter = "[Active] = False"
End Select
Call ApplyFormFilter
End Sub


Private Sub frmOption2_AfterUpdate()
Select Case Me.frmOption2
Case 1
mstrEmpFilter = "[EmpType] = 'Company'"
Case 2
mstrEmpFilter = "[EmpType] = 'Temp'"
Case 3
' All employees
mstrEmpFilter = ""
End Select
Call ApplyFormFilter
End Sub

Private Sub ApplyFormFilter()
Dim strCombinedFilter as String

' Combine the 2 filters
Select Case True
' Only one of these 3 things will execute: the 1st True
condition encountered
Case mstrRecordFilter = ""
strCombinedFilter = mstrEmpFilter
Case mstrEmpFilter = ""
strCombinedFilter = mstrRecordFilter
Case Else
' User wants to filter on multiple fields
strCombinedFilter = mstrRecordFilter & " AND " &
mstrEmpFilter
End Select

'Apply the combined filter
Me.Filter = strCombinedFilter
If strCombinedFilter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End Sub
'******* end aircode **************************

--
HTH,
George



Jeff Garrison said:
Hell all -

I have a quick quesiton...(first off, I'n using Access 2007 FE and SQL
BE)...on a form, I would like to use 2 Option Groups to apply filters to
the form. Details below...

Option Group 1

All Records (No Filter)
Active Only (Filter Only Active Records)
Inactive Only (Filter Only Inactive Records)

Option Group 2

Company Employees
Temp Employees
All Employees

I'd like to do a filter with a combination of the 2 Option
Groups...being able to select options from both groups and filter the
records based on the Option selected.

Any help would be musch appreciated.

Thanks.

JeffG
Click to expand...
Click to expand...
Click to expand...
 
Thank.....I knew it had to be something easy...

George Nicholson said:
How do you set the default option when the form is opened?
Click to expand...

the default value of the OptionFrame can be preset in design view.
(Or Me.frmOption.DefaultValue = 1 I suppose)

If you want to select something other than the default when the form
opens:

in Form_Open:

Me.frmOption1 = 1
Me.frmOption2 = 3
' If you also want the AfterUpdate code to run (and apply the filter)
based on those settings:
frmOption1_AfterUpdate
frmOption2_AfterUpdate

--
HTH,
George


Jeff Garrison said:
In a related question (sort of)...

How do you set the default option when the form is opened?

I used to remember how to do that, but it's been many years...


George Nicholson said:
The following would all go into your form module. Add error handling,
adjust control/field names, etc., as appropriate for your reality.

'***** start aircode **********
Option Explicit
'(Top-of-form module) Module-level variables:
Private mstrRecordFilter as String
Private mstrEmpFilter as String

Private Sub frmOption1_AfterUpdate()
Select Case Me.frmOption1
Case 1
'All records
mstrRecordFilter = ""
Case 2
mstrRecordFilter = "[Active] = True"
Case 3
mstrRecordFilter = "[Active] = False"
End Select
Call ApplyFormFilter
End Sub


Private Sub frmOption2_AfterUpdate()
Select Case Me.frmOption2
Case 1
mstrEmpFilter = "[EmpType] = 'Company'"
Case 2
mstrEmpFilter = "[EmpType] = 'Temp'"
Case 3
' All employees
mstrEmpFilter = ""
End Select
Call ApplyFormFilter
End Sub

Private Sub ApplyFormFilter()
Dim strCombinedFilter as String

' Combine the 2 filters
Select Case True
' Only one of these 3 things will execute: the 1st True
condition encountered
Case mstrRecordFilter = ""
strCombinedFilter = mstrEmpFilter
Case mstrEmpFilter = ""
strCombinedFilter = mstrRecordFilter
Case Else
' User wants to filter on multiple fields
strCombinedFilter = mstrRecordFilter & " AND " &
mstrEmpFilter
End Select

'Apply the combined filter
Me.Filter = strCombinedFilter
If strCombinedFilter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End Sub
'******* end aircode **************************

--
HTH,
George



Hell all -

I have a quick quesiton...(first off, I'n using Access 2007 FE and SQL
BE)...on a form, I would like to use 2 Option Groups to apply filters
to the form. Details below...

Option Group 1

All Records (No Filter)
Active Only (Filter Only Active Records)
Inactive Only (Filter Only Inactive Records)

Option Group 2

Company Employees
Temp Employees
All Employees

I'd like to do a filter with a combination of the 2 Option
Groups...being able to select options from both groups and filter the
records based on the Option selected.

Any help would be musch appreciated.

Thanks.

JeffG
Click to expand...
Click to expand...
Click to expand...
 
Back
Top