Applying a Filter Using Multiple Option Groups

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 **************************

--
HTH,
George



"Jeff Garrison" wrote in message
...
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

George -

Works like a charm.....THANKS!

Jeff


"George Nicholson" wrote in message
...

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" wrote in message
...
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

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" wrote in message
...

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
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

Thank.....I knew it had to be something easy...

"George Nicholson" wrote in message
...
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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
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