ACCESS FEED: Re: [MS_AccessPros] Filter a continuous form

Kamis, 29 Desember 2011

Re: [MS_AccessPros] Filter a continuous form

Hi Art,

rather than a choice for all, which usually means a Union query, I like to include a Reset button to set the filter control(s) to null.

Set Form Filter

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:

'~~~~~~~~~~~~~~~~~~~~~~
   SetFormFilter
'~~~~~~~~~~~~~~~~~~~~~~

this is a generic version of the code that would go behind your form:
'~~~~~~~~~~~~~~~~~~~~~~
Private Function SetFormFilter()

   dim varFilter as variant
   varFilter = Null

   If not IsNull(me.controlname_for_text) Then
      varFilter = (varFilter + " AND ") _
         & "[TextFieldname]= '" & me.controlname_for_text & "'"
   end if

   If not IsNull(me.controlname_for_date ) Then
      varFilter = (varFilter + " AND ") _
         & "[DateFieldname]= #" & me.controlname_for_date & "#"
   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 -- kinda 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

if you are wanting to filter a subform, instead of using
With Me
you would use -->
'~~~~~~~~~~~~~~~~~~~~~~
   With Me.subform_controlname.form
'~~~~~~~~~~~~~~~~~~~~~~

WHERE
.subform_controlname is the Name property of the subform control

if the form to get criteria from is is subforms of the same main form, use -->

'~~~~~~~~~~~~~~~~~~~~~~
   With me.parent.subform_controlname.form
'~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

Learn Access on YouTube
http://www.YouTube.com/LearnAccessByCrystal

*
   (: have an awesome day :)
*

________________________________
From: Art

I have a continous form and on it I have a combo box cboTransTypeFilter with the record source of:

SELECT [tlkpTransactionType].[TransactionTypeID], [tlkpTransactionType].[TransactionTypeName] FROM tlkpTransactionType;

I would like to filter the continous form based on whatever is selected from the cboTransTypeFilter. Also I would want the first item in the combo to say "All Transaction Types" and if that is selected then return all types.

Thanks,

Art Lorenzini
Sioux Falls, SD

[Non-text portions of this message have been removed]