Form and subform filter

[A1]

Dear Programer:

I have Form (master form parent form) and sub form
the master form contain 10 controls combobox to filter subform
control No1 (after update event)
me.subformsearch.form.filter = "Groupnumber=" & me.cbogroupno
me.subformsearch.form.filteron=true
control No 2 (after update event)
me.subformsearch.form.filter="dateofdeparture"= # " & me.cbodate & "#"
me.subformsearch.form.filteron=true
controlno 3 after update event
same code as above
control no 4 after update
same code as above
etc
what is the problem in this code
every control not related to other controls
I mean
every control apply (((((separate filter)))))
=========================
what I want
I want use this new edited code and put this code in command button in
master form parent form and code check all combo box and at the end give me
one filter for all controls
and give me the result
===============

 

[strive4peace]

Set Form or Subform Filter
---

put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~
Private 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
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

'**************************************************
' Choose on of following code blocks
'**************************************************

'--------------- Filter form you are behind
if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery


'OR

'--------------- Filter subform
if len(mfilter) > 0 then
me.subformcontrolname.form.filter = mfilter
me.subformcontrolname.form.FilterOn = true
else
me.subformcontrolname.form.FilterOn = false
end if

me.subformcontrolname.form.requery


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

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]

'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~

a requery of the form will also reset the record pointer back to the
first record.




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*