Full Version: Filtered form using query by form
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
I have a form, showing parts, that has a group of toggle buttons at the top of it to select my part type. This then filters the parts records appropriately to only show that type of part. Basically the toggle button changes the record source for the form to a query that filters for the specified part.

I have seen that something similar can be achieved by using QueryDef. My current database runs rather slowly when the record source property (toggle button clicked) is changed and it all takes a while to update. We're running on a network, with the data on a server.

Before I trawl through all the QueryDef code and try to understand it, would it speed up my database? It may well be a more elegant solution but I'm only really interested in making the change I will get a performance benefit.
Set Form Filter

It would be best to build a filter string for the report (as opposed to imbedding a parameter in a query)

put comboboxes in the header of the form. Assign this to the AfterUpdate event of each one...


then put this code behind the form

Function SetFormFilter()
   dim mFilter as string
   mFilter = ""
   If not IsNull(me.text_controlname ) Then
      mfilter = "[TextFieldname]= '" & me.controlname_for_text  & "'"
   end if
   If not IsNull(me.date_controlname ) Then
      mfilter = (mfilter + " AND ")  & "[DateFieldname]= #" & me.controlname_for_date  & "#"
   end if

   If not IsNull(me.numeric_controlname ) Then
      mfilter = (mfilter + " AND ")  & "[NumericFieldname]= " & me.controlname_for_number
   end if
    if len(mfilter) > 0 then  
       me.filter = mfilter
       me.FilterOn = true
       me.FilterOn = false
   end if
End Function

me.controlname_for_number refers 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 wink.gif)

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

mfilter is a string that is being built for each condition -- but if nothing is specified in the filter control (IsNull), then that addition to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that filter will show
Thanks, that looks quite straight forward - I presume I can modify this concept for my toggle buttons - there are only five generic part types so these filters should never change.
you're welcome wink.gif
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.