Multiple Filter Form

[crba724]

I have a form that has two different option button frames and 1 text box that I would like my users to be able to use to search/filter for specific records. I chose a form because a table does not work as a user-friendly interface for the data that is being stored.

• The first option button frame allows the user to filter by Month and is named [FilterDate].
• The second option button frame allows the user to filter by incomplete and complete records and is named [FilterOption].
• The text box is used as a search box and is named [txtsearch].

I am able to successfully create macros to apply filters for each of the above options; however, each time one of the options is updated, it removes the previous filter. What I need is help creating a macro (preferrably) or vba code (if macro is not possible) so that my users can first pick a month (as this will always be the first thing they do when opening the form), then pick complete or incomplete records within that month if desired. The search box then would only filter records that meet the first two criteria (month and complete/incomplete).

 

[Ranman256]

you must check ALL the controls to see what to apply....
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,5)

  'just use the filter

me.filter = sWhere
me.filterOn = true

 

[crba724]

Please forgive me but I am very new to writing code and am having a difficult time applying your example to my situation. Because I am using option buttons, I have several where conditions for each control and am using several If/Then statements depending on the option chosen. Currently I am using a macro (not vba code) to do this:

SetTempVar
Name: SetDate
Expression: [FilterDate]

If [TempVar]![SetDate]=1 Then
ApplyFilter
Where DatePart("m",[Meeting_Date])=2

(this is repearted for each value 1-6)

 

[Ranman256]

option buttons only allow ONE choice, so..

select case fraOptions
   case 1
     filter = A
   case 2
     filter = B
end select
filterOn = true

 

[crba724]

OK, maybe I am not being clear so I will start over.

I have a form with two completely set of option buttons. One set or frame of option buttons filters by MONTH (i.e. February, April, June, August, October, December). The other completely separate set of option buttons filters by STATUS (i.e. Pending, Complete). Then, I have a text box that is used as a freeform search box. I need to know how I can set this up as combined filters where a user could pick one option from the MONTH frame, one option from the STATUS frame, and type a word into the search box in combination without reseting the filter. So, if a user chose a month of "February", a status of "Complete" and wanted to find a record for "XXXX", the filter would find the record that matches all three criteria.

Can somebody please help me with this?

 

[vbaInet]

OK, maybe I am not being clear so I will start over.

Can somebody please help me with this?

It can't be any clearer and Ranman256 had answered your question. All that was left was for you to piece it all together.

You will need VBA (not a macro) and the function needs to be called in the After Update event of all three controls.

Private Sub FilterForm()
    Dim strWhere As String
    
    If Not IsNull(Me.FilterDate) Then
        strWhere = "Month([[COLOR="blue"]DateField[/COLOR]]) = " & Me.FilterDate & " AND "
    End If
    
    With Me.FilterOption
        If Not IsNull(.Value) Then
            strWhere = "[[COLOR="blue"]StatusField[/COLOR]] = '" & .Controls(.Value).Controls(0).Caption & "' AND "
        End If
        ' NOTE: .Controls(.Value).Controls(0).Caption = this will return "Pending" or "Complete"
    End With
    
    If Len(Me.txtSearch & vbNullString) <> 0 Then
        strWhere = "[[COLOR="Blue"]SearchField[/COLOR]] = '" & Me.txtSearch & "' AND "
    End If
    
    ' Strip off the trailing " AND "
    strWhere = Left(strWhere, Len(strWhere) - 5)    ' 5 represents Len(" AND ")

    ' Apply the filter
    If Len(strWhere) <> 0 Then
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Ensure that:
* The Option Value property for the months frame are Jan = 1 to Dec = 12... it needs to match with the Month() value.
* The labels for the status frame's option controls, i.e. Pending and Complete, match up with what's saved in the table
* Amend the bits in blue and paste it in your form's module.