Set Form or Subform Filter
~~~
Hi John,
Put comboboxes and textboxes on the form (I put then in the header and
give them a unique background color so they are not confused with data
controls). Assign this to the AfterUpdate event of each one...
=SetFormFilter()
then put this code behind the form
'~~~~~~~~~~~~~~~
Private Function SetFormFilter()
dim varFilter as variant
varFilter = null
If not IsNull(me.text_controlname ) Then
varFilter = (varFilter + " AND " ) _
& "[TextFieldname]= '" & me.text_controlname & "'"
end if
If not IsNull(me.date_controlname ) Then
varFilter = (varFilter + " AND " ) _
& "[DateFieldname]= #" & me.date_controlname & "#"
end if
If not IsNull(me.numeric_controlname ) Then
varFilter = (varFilter + " AND " ) _
& me.numeric_controlname
end if
'**************************************************
' Choose on of following code blocks
'**************************************************
'--------------- Filter form you are behind
if not IsNull(varFilter) then
me.filter = varFilter
me.FilterOn = true
else
me.FilterOn = false
end if
me.requery
'OR
'--------------- Filter subform
' if not IsNull(varFilter) then
' me.subformcontrolname.form.filter = varFilter
' me.subformcontrolname.form.FilterOn = true
' else
' me.subformcontrolname.form.FilterOn = false
' end if
' me.subformcontrolname.form.requery
End Function
'~~~~~~~~~~~~~~~
me.numeric_controlname 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
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.
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
Then, put another command button on the form
Name --> btnShowAll
OnClick --> [Event Procedure]
'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~
as for your question about indicating the filter to the user, you can
make a textbox with this as its Control Source:
=IIf([FilterOn],[Filter],"")
you could, of course, build a friendly string to show when you process
the criteria -- but this is the easy way <smile>
to make the code easier to read, I did not include an error handler --
but you should put it in.
'~~~~~~~~~~~~~~
'set up Error Handler - 'put this at the top of your program
' -- right after the procedure declaration
On Error GoTo Proc_Err
'...then come the statements of your procedure ...
'then the exit code and error handler statements at the bottom
Proc_Exit:
On Error Resume Next
'close and release object variables if applicable
Exit Sub ' or Function
Proc_Err:
'NOTE: replace ProcedureName with YOUR procedure name
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
'~~~~~~~~~~~~~~
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
I have taken over a db and on a continuous form the user can filter a Status
column by the use of several buttons. But on a second column (Type) the
double click event of the field was used for filtering. When the user
filters by button or the double click, what is being filtered is displayed in
a label. But, what is occuring is if a user filters using a button then the
double click, the label only shows the double click filter. The user still
needs to know that the button filter is still in use along with the double
click. How can that be displayed in the label? Or is there a better method
of filtering for the 2 columns?
Thanks.
... John
