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.
Full Version: Filtered form using query by form
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...
=SetFormFilter()
then put this code behind the form
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
)
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
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...
=SetFormFilter()
then put this code behind the form
CODE
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
else
me.FilterOn = false
end if
End Function
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
else
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
)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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.