notice of filtering

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

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
 
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 :)
*
 
Crystal (aka strive4peace), thanks for the reply. This info is great.
.... John


strive4peace said:
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
 
you're welcome, John ;) happy to help

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 :)
*



Crystal (aka strive4peace), thanks for the reply. This info is great.
... John


strive4peace said:
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
 
Back
Top