filtering records based on another filter

[seandelaney]

Hi guys,

Ive got a form with a radio button group that I use to filter what records I can browse through. 

My filter options are:

• All Employees
• Currently Employed
• Left Employees

The default radio selection is "All Employees".  Its a simple filter - if you select "Currently Employed", you can only browse employees who are still working in the company...

Ive now been asked by the Admin girl, if she could have a second filter option on the form that would allow her to filter between employees who are staff and who are contractors.  Ive added a second radio button group to the form, added 2 labels and 2 radio buttons, gave them names etc, but I haven't added any code in the backend yet as i've a few questions.

My plan is to filter by Staff/Contractor first, then based on that, filter by All, Current and Left second.

As i'm not that confidence with coding in Access, i'm not sure how to add the code i've got for my original filter to my new filter or visa versa and get them working together.

My first filters code was:

Code:

Private Sub EmployeeFilterOptions_AfterUpdate()
    'Apply or remove filter for the options the user chose
    If EmployeeFilterOptions = 2 Then
        Me.Filter = "Active=0"
        Me.FilterOn = True        ' Apply the filter to just see currently employeed user
    ElseIf EmployeeFilterOptions = 3 Then
        Me.Filter = "Active=-1"
        Me.FilterOn = True        ' Apply the Filter to just see left company users
    Else
        Me.FilterOn = False       ' Remove the filter
    End If
End Sub

Code:

Private Sub EmployeeFilterOptions_Click()
    If EmployeeFilterOptions = 2 Then
        Me.Combo65.RowSource = "qryCurrentlyEmployed"
    ElseIf EmployeeFilterOptions = 3 Then
        Me.Combo65.RowSource = "qryLeftCompany"
    Else
        Me.Combo65.RowSource = "qryAllEmployees"
    End If
End Sub

I haven't created the queries for select all staff employees or all contractor employees yet.  I'm going to do this next...

[nico5038]

I would construct the filter depending on the selected values.
Just use the threeway selection Active/NotActive/All, but also Staff/Employee/All

Now code for both frames:

Code:

Private Sub EmployeeFilterOptions_AfterUpdate()
   call subFilter
End Sub

Private Sub ActiveFilterOptions_AfterUpdate()
   call subFilter
End Sub

Code:

And add this sub for the activation:

sub subFilter
    dim strFilter as String
    ' initialize filter
    strFilter = ""
    'Apply or remove filter for the options the user chose for employee
    If EmployeeFilterOptions = 2 Then
        strFilter = " and Employee=0"
    ElseIf EmployeeFilterOptions = 3 Then
        strFilter = " and Employee=-1"
    End If
    'Apply or remove filter for the options the user chose for Active
    If ActiveFilterOptions = 2 Then
        strFilter = " and Active=0"
    ElseIf ActiveFilterOptions = 3 Then
        strFilter = " and Active=-1"
    End If
    ' now test for filled filter and remove the first " and "
    if len(strFilter) > 0 then
       Me.Filter = mid(strFilter,6)
       Me.FilterOn = True
    else
       Me.FilterOn = False
    endif
end sub

Hope this helps.

Nic;o)