UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Form Filters    
Form Filters

Contents

Set Form Filter

Code to SetFormFilter

put unbound comboboxes/textboxes in your form header. Then, to trigger the code, put this in the [Event Procedure] code of the AfterUpdate event of each filter control:

CODE
Private Sub Controlname_AfterUpdate()
  SetFormFilter
End Sub

this is a generic version of the code that would go behind your form:

CODE
Private Function SetFormFilter()

  Dim varFilter As Variant
 
  varFilter = Null

  If Not IsNull(Me.controlname_for_text) Then
     varFilter = (varFilter + " AND ") _
        & "[TextFieldname]= '" & Replace(Me.controlname_for_text, "'", "''")  & "'"
  End If

  If Not IsNull(Me.controlname_for_date) Then
     varFilter = (varFilter + " AND ") _
        & "[DateFieldname]= " & Format(Me.controlname_for_date, "\#yyyy\-mm\-dd\#")  & " "
  End If

  If Not IsNull(Me.controlname_for_number) Then
     varFilter = (varFilter + " AND ") _
        & "[NumericFieldname]= " & Me.controlname_for_number
  End If

  With Me
      If Not IsNull(varFilter)  Then
         .Filter = varFilter
         .FilterOn = True
      Else
         .FilterOn = False
      End If
     ' .Requery
  End With

End Function

WHERE me.controlname_for_number, controlname_for_date, and controlname_for_text refer to the NAME property of a control on the form you are behind (Me. represents the form -- like "me" for me is not "me" for you )

delimiters are used for data that is not a number quote marks ' or " for text number signs # for dates

varFilter is a variant that will hold the string you are building for each condition -- but if nothing is specified in the filter control (IsNull), then that addition to the filter string is skipped.

(varFilter + " AND ")

If something is already in the filter, the word AND will be added. The beauty of using the + operator is that nothing + something = nothing ... so if nothing is there then nothing is added before the new criteria

finally, when the filter string is done, it is applied to your form if it has something in it. If not, then all the records will be displayed

That means that as you flip through records, only records matching that filter will show

Filtering a Subform

if you are wanting to filter a subform, instead of using With Me you would use -->

CODE
  With Me.subform_controlname.Form

WHERE .subform_controlname is the Name property of the subform control

Filter another subform of the main form

if the form to get criteria is on one subform, and what is being filtered is another subform on the same main form, use -->

CODE
     With Me.Parent.other_subform_controlname.Form

Searching many fields for the same criteria

When you want to search several fields, is to combine them. For instance, say you have a textbox. The textbox collects the search criteria. The AfterUpdate event then applies a filter to the form

CODE
  With Me
      If Not IsNull(me.textbox_controlname)  Then
         .Filter = "LastName & FirstName & EmailAddress LIKE '*" _
              & Replace(Me.textbox_controlname, "'", "''") & "*'"
         .FilterOn = True
     Else
         .FilterOn = False
     End if
     ' .Requery
  End With

I prefer, however, to search fields individually.

Remove Filter

Then, put another command button on the form

Name --> cmd_ShowAll Caption --> Show All OnClick --> [Event Procedure]

CODE

   'clear any controls that are showing filter criteria
  Me.ControlName_FilterCriteria  = Null

  With Me
      .FilterOn = False
      ' .Requery
  End With

  'or
  'With Me.subform_controlname.Form

Show Filter String in a control on the Form

to show the filter to the user, you can make a textbox with this as its Control Source:

CODE
  =IIf([FilterOn],[Filter],"")

if using Access 2007+, you will have to calculate this in code and write it to an unbound control

you could, of course, build a friendly string to show when you process the criteria -- but this is the easy way

Set the Record Pointer after the Form is Filtered

Changing the filter sets the record pointer back to the first record.

If you want to capture the record you are on and set it back, you can do this:

CODE
'put this at the top of the SetFormFilter code

  'save value of primary key (assumption it is autoNumber of Long Integer)
  Dim nRecordID As Long
  nRecordID = 0
  If Not Me.NewRecord then
     nRecordID = Nz(Me.PrimaryKey_fieldname)
  End If

  'then, after the code sets the filter...

  If nRecordID = 0 Then Exit Function

  'go back to record you were on
  Me.RecordsetClone.FindFirst _
    "PrimaryKey_fieldname = " & nRecordID

  If Not Me.RecordsetClone.NoMatch Then
     Me.Bookmark = Me.RecordsetClone.Bookmark
  End If
Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 4,619 times.  This page was last modified 16:06, 11 October 2016 by djsteele. Contributions by Strive4peace  Disclaimers