Filter sub form with multiple combo boxes on main form

  • Thread starter Thread starter DMUM via AccessMonster.com
  • Start date Start date
D

DMUM via AccessMonster.com

Hi I have created a form that contains a continuos subform. The main form
contains 15 combo boxes that will be used by the end user to filter out
information produced by a query into the subform. It is working find, but I
am wandering if I am going thw right way about this. I'm having 2 problems.


1. I don't know how to make the subform refresh. when I close the entire
form, it will open with all records which I want. I want the records to
disappear and/or change depending upon what combinations of combo box choices
the users picks - this seems to work fine as well, however, if I want to
refresh the sub form - I can't. I have placed a "Clear" in the driving combo
box that refreshes all the other combo boxes, but this action clears the
subform as well.

2. Can someone provide me a way to use Like NZ([Forms]![frm_filter]![txtFreq]
,"*")) for date. I tried a couple combinations of things, some which still
let me see my subform and others that wouldn't even let me leave the query
screen. I need this because when the form open it shows all possible data -
including all dates. Since there is nothing in the date combo nothing will
be returned unless it is filled in. I want the option of using a date
criteria or not.

Below is the query I have created. As you can see I use the "Like NZ([Forms]!
[frm_filter]![txtFreq],"*")) " A LOT!
I am wandering if this is the best way to do this or do I have another option.
if so, can you please give me as much detail as possible so I can implement
it?

Thank You
------------------------------------------------------------------------------
--------------------------------------------------------------
Here is the query:

SELECT lkup_BusinessLine.intBusID, tbl_Metric.intSDLID AS SDL, tbl_Metric.
intManagerID, tbl_Metric.intTeamLeadID, lkup_Region.intRegionID, lkup_Market.
intMarketID, lkup_FACT.intfactID, lkup_Measurement.intMeasurementID,
lkup_Process.intProcessID, lkup_subProcess.intsProcessID, lkup_Frequency.
intFreqID, tbl_Metric.MetricName, tbl_Metric.FCE_FCW, lkup_Category.intCatID,
tbl_Scorecard.dblDefect, tbl_Scorecard.dblOpportunities, tbl_Scorecard.
dblDPMO, tbl_Scorecard.dblSigma, tbl_Scorecard.dtScoreDate, tbl_PRSA.PRSA,
lkup_OwnerUser_3.Full_Name AS Test_Operator, lkup_OwnerUser_4.Full_Name AS
Control_Operator
FROM lkup_OwnerUser AS lkup_OwnerUser_4 RIGHT JOIN (lkup_OwnerUser AS
lkup_OwnerUser_3 RIGHT JOIN (tbl_PRSA RIGHT JOIN (lkup_OwnerUser RIGHT JOIN
(lkup_OwnerUser AS lkup_OwnerUser_2 RIGHT JOIN (lkup_OwnerUser AS
lkup_OwnerUser_1 RIGHT JOIN (lkup_Frequency RIGHT JOIN (lkup_subProcess RIGHT
JOIN (lkup_Measurement RIGHT JOIN (lkup_BusinessLine RIGHT JOIN
(lkup_Category RIGHT JOIN (lkup_Market RIGHT JOIN (tbl_Scorecard RIGHT JOIN
(lkup_Process RIGHT JOIN (lkup_FACT RIGHT JOIN (lkup_Region RIGHT JOIN
tbl_Metric ON lkup_Region.intRegionID = tbl_Metric.intRegion) ON lkup_FACT.
intfactID = tbl_Metric.intFactID) ON lkup_Process.intProcessID = tbl_Metric.
intProcessID) ON tbl_Scorecard.intMetricID = tbl_Metric.intMetricID) ON
lkup_Market.intMarketID = tbl_Metric.intMarketID) ON lkup_Category.intCatID =
tbl_Metric.intCategoryID) ON lkup_BusinessLine.intBusID = tbl_Metric.
intBusinessID) ON lkup_Measurement.intMeasurementID = tbl_Metric.
intMeasurementID) ON lkup_subProcess.intsProcessID = tbl_Metric.intsProcessID)
ON lkup_Frequency.intFreqID = tbl_Metric.intFrequencyID) ON lkup_OwnerUser_1.
intOwnerID = tbl_Metric.intManagerID) ON lkup_OwnerUser_2.intOwnerID =
tbl_Metric.intSDLID) ON lkup_OwnerUser.intOwnerID = tbl_Metric.intTeamLeadID)
ON tbl_PRSA.intPRSAID = tbl_Metric.intPRSAID) ON lkup_OwnerUser_3.intOwnerID
= tbl_PRSA.Test_Operator) ON lkup_OwnerUser_4.intOwnerID = tbl_PRSA.
Control_Operator
GROUP BY lkup_BusinessLine.intBusID, tbl_Metric.intSDLID, tbl_Metric.
intManagerID, tbl_Metric.intTeamLeadID, lkup_Region.intRegionID, lkup_Market.
intMarketID, lkup_FACT.intfactID, lkup_Measurement.intMeasurementID,
lkup_Process.intProcessID, lkup_subProcess.intsProcessID, lkup_Frequency.
intFreqID, tbl_Metric.MetricName, tbl_Metric.FCE_FCW, lkup_Category.intCatID,
tbl_Scorecard.dblDefect, tbl_Scorecard.dblOpportunities, tbl_Scorecard.
dblDPMO, tbl_Scorecard.dblSigma, tbl_Scorecard.dtScoreDate, tbl_PRSA.PRSA,
lkup_OwnerUser_3.Full_Name, lkup_OwnerUser_4.Full_Name
HAVING (((lkup_BusinessLine.intBusID) Like NZ([Forms]![frm_filter]![txtBLine],
"*")) AND ((tbl_Metric.intSDLID) Like NZ([Forms]![frm_filter]![txtSDL],"*"))
AND ((tbl_Metric.intManagerID) Like NZ([Forms]![frm_filter]![txtMgr],"*"))
AND ((tbl_Metric.intTeamLeadID) Like NZ([Forms]![frm_filter]![txtTLead],"*"))
AND ((lkup_Region.intRegionID) Like NZ([Forms]![frm_filter]![txtregion],"*"))
AND ((lkup_Market.intMarketID) Like NZ([Forms]![frm_filter]![txtMkt],"*"))
AND ((lkup_FACT.intfactID) Like NZ([Forms]![frm_filter]![txtfact],"*")) AND (
(lkup_Measurement.intMeasurementID) Like NZ([Forms]![frm_filter]![txtMeas],
"*")) AND ((lkup_Process.intProcessID) Like NZ([Forms]![frm_filter]![txtProc],
"*")) AND ((lkup_subProcess.intsProcessID) Like NZ([Forms]![frm_filter]!
[txtSubProc],"*")) AND ((lkup_Frequency.intFreqID) Like NZ([Forms]!
[frm_filter]![txtFreq],"*")) AND ((lkup_Category.intCatID) Like NZ([Forms]!
[frm_filter]![txtCat],"*")))
ORDER BY tbl_Metric.MetricName;
------------------------------------------------------------------------------
-----------------------------------------------------------------
Here is the Main forms VBA code. I have no code in the subform:

Option Compare Database
Option Explicit
Dim frm As Form

Private Sub cboBline_AfterUpdate()

Me.cboSDl = ""
Me.cboManager = ""
Me.cboTeamLead = ""
Me.cboRegion = ""
Me.cboMarket = ""
Me.cboFACT = ""
Me.cboFrequency = ""
Me.cboProcess = ""
Me.cboCategory = ""
Me.cboFCEFCW = ""
Me.cboSubProcess = ""
Me.cboMeasurement = ""


Me.cboSDl.Requery
Me.cboManager.Requery
Me.cboTeamLead.Requery
Me.cboRegion.Requery
Me.cboMarket.Requery
Me.cboFACT.Requery
Me.cboFrequency.Requery
Me.cboProcess.Requery
Me.cboSubProcess.Requery
Me.cboCategory.Requery
Me.cboFCEFCW.Requery
Me.cboMeasurement.Requery
Me.qryFRM_filterQuery.Requery

End Sub

Private Sub cboSDL_AfterUpdate()
Me.cboManager = ""
Me.cboManager.Requery
Me.qryFRM_filterQuery.Requery

End Sub

Private Sub cboManager_AfterUpdate()
Me.cboTeamLead = ""
Me.cboTeamLead.Requery
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboRegion_AfterUpdate()
Me.cboMarket = ""
Me.cboMarket.Requery
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboMarket_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub

Private Sub cboFACT_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboMeasurement_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboFreq_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub

Private Sub cboProcess_AfterUpdate()
Me.cboSubProcess = ""
Me.cboSubProcess.Requery
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboSubProcess_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboCategory_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboFCEFCW_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub Form_Open(Cancel As Integer)


Me.cboBline = ""
Me.cboSDl = ""
Me.cboManager = ""
Me.cboTeamLead = ""
Me.cboRegion = ""
Me.cboMarket = ""
Me.cboFACT = ""
Me.cboFrequency = ""
Me.cboProcess = ""
Me.cboSubProcess = ""
Me.cboCategory = ""
Me.cboFCEFCW = ""
Me.cboSubProcess = ""
Me.cboMeasurement = ""
End Sub
 
rather than using LIKE in all the criteria that you MAY have
.... consider having the subform show all the records and
just building the Filter property from your choices

Assign this to the AfterUpdate event of each one...

=SetSubFormFilter()

then put this code behind the form

'-------------------------
Private Function SetSubFormFilter()

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

if len(mfilter) > 0 then
me.subform_controlname.form.filter = mfilter
me.subform_controlname.form.FilterOn = true
else
me.subform_controlname.form.FilterOn = false
end if

me.subform_controlname.form.requery
DoEvents

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.subform_controlname.form.filteron = false
me.subform_controlname.form.requery
DoEvents
'-------------------------
Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi I have created a form that contains a continuos subform. The main form
contains 15 combo boxes that will be used by the end user to filter out
information produced by a query into the subform. It is working find, but I
am wandering if I am going thw right way about this. I'm having 2 problems.


1. I don't know how to make the subform refresh. when I close the entire
form, it will open with all records which I want. I want the records to
disappear and/or change depending upon what combinations of combo box choices
the users picks - this seems to work fine as well, however, if I want to
refresh the sub form - I can't. I have placed a "Clear" in the driving combo
box that refreshes all the other combo boxes, but this action clears the
subform as well.

2. Can someone provide me a way to use Like NZ([Forms]![frm_filter]![txtFreq]
,"*")) for date. I tried a couple combinations of things, some which still
let me see my subform and others that wouldn't even let me leave the query
screen. I need this because when the form open it shows all possible data -
including all dates. Since there is nothing in the date combo nothing will
be returned unless it is filled in. I want the option of using a date
criteria or not.

Below is the query I have created. As you can see I use the "Like NZ([Forms]!
[frm_filter]![txtFreq],"*")) " A LOT!
I am wandering if this is the best way to do this or do I have another option.
if so, can you please give me as much detail as possible so I can implement
it?

Thank You
------------------------------------------------------------------------------
--------------------------------------------------------------
Here is the query:

SELECT lkup_BusinessLine.intBusID, tbl_Metric.intSDLID AS SDL, tbl_Metric.
intManagerID, tbl_Metric.intTeamLeadID, lkup_Region.intRegionID, lkup_Market.
intMarketID, lkup_FACT.intfactID, lkup_Measurement.intMeasurementID,
lkup_Process.intProcessID, lkup_subProcess.intsProcessID, lkup_Frequency.
intFreqID, tbl_Metric.MetricName, tbl_Metric.FCE_FCW, lkup_Category.intCatID,
tbl_Scorecard.dblDefect, tbl_Scorecard.dblOpportunities, tbl_Scorecard.
dblDPMO, tbl_Scorecard.dblSigma, tbl_Scorecard.dtScoreDate, tbl_PRSA.PRSA,
lkup_OwnerUser_3.Full_Name AS Test_Operator, lkup_OwnerUser_4.Full_Name AS
Control_Operator
FROM lkup_OwnerUser AS lkup_OwnerUser_4 RIGHT JOIN (lkup_OwnerUser AS
lkup_OwnerUser_3 RIGHT JOIN (tbl_PRSA RIGHT JOIN (lkup_OwnerUser RIGHT JOIN
(lkup_OwnerUser AS lkup_OwnerUser_2 RIGHT JOIN (lkup_OwnerUser AS
lkup_OwnerUser_1 RIGHT JOIN (lkup_Frequency RIGHT JOIN (lkup_subProcess RIGHT
JOIN (lkup_Measurement RIGHT JOIN (lkup_BusinessLine RIGHT JOIN
(lkup_Category RIGHT JOIN (lkup_Market RIGHT JOIN (tbl_Scorecard RIGHT JOIN
(lkup_Process RIGHT JOIN (lkup_FACT RIGHT JOIN (lkup_Region RIGHT JOIN
tbl_Metric ON lkup_Region.intRegionID = tbl_Metric.intRegion) ON lkup_FACT.
intfactID = tbl_Metric.intFactID) ON lkup_Process.intProcessID = tbl_Metric.
intProcessID) ON tbl_Scorecard.intMetricID = tbl_Metric.intMetricID) ON
lkup_Market.intMarketID = tbl_Metric.intMarketID) ON lkup_Category.intCatID =
tbl_Metric.intCategoryID) ON lkup_BusinessLine.intBusID = tbl_Metric.
intBusinessID) ON lkup_Measurement.intMeasurementID = tbl_Metric.
intMeasurementID) ON lkup_subProcess.intsProcessID = tbl_Metric.intsProcessID)
ON lkup_Frequency.intFreqID = tbl_Metric.intFrequencyID) ON lkup_OwnerUser_1.
intOwnerID = tbl_Metric.intManagerID) ON lkup_OwnerUser_2.intOwnerID =
tbl_Metric.intSDLID) ON lkup_OwnerUser.intOwnerID = tbl_Metric.intTeamLeadID)
ON tbl_PRSA.intPRSAID = tbl_Metric.intPRSAID) ON lkup_OwnerUser_3.intOwnerID
= tbl_PRSA.Test_Operator) ON lkup_OwnerUser_4.intOwnerID = tbl_PRSA.
Control_Operator
GROUP BY lkup_BusinessLine.intBusID, tbl_Metric.intSDLID, tbl_Metric.
intManagerID, tbl_Metric.intTeamLeadID, lkup_Region.intRegionID, lkup_Market.
intMarketID, lkup_FACT.intfactID, lkup_Measurement.intMeasurementID,
lkup_Process.intProcessID, lkup_subProcess.intsProcessID, lkup_Frequency.
intFreqID, tbl_Metric.MetricName, tbl_Metric.FCE_FCW, lkup_Category.intCatID,
tbl_Scorecard.dblDefect, tbl_Scorecard.dblOpportunities, tbl_Scorecard.
dblDPMO, tbl_Scorecard.dblSigma, tbl_Scorecard.dtScoreDate, tbl_PRSA.PRSA,
lkup_OwnerUser_3.Full_Name, lkup_OwnerUser_4.Full_Name
HAVING (((lkup_BusinessLine.intBusID) Like NZ([Forms]![frm_filter]![txtBLine],
"*")) AND ((tbl_Metric.intSDLID) Like NZ([Forms]![frm_filter]![txtSDL],"*"))
AND ((tbl_Metric.intManagerID) Like NZ([Forms]![frm_filter]![txtMgr],"*"))
AND ((tbl_Metric.intTeamLeadID) Like NZ([Forms]![frm_filter]![txtTLead],"*"))
AND ((lkup_Region.intRegionID) Like NZ([Forms]![frm_filter]![txtregion],"*"))
AND ((lkup_Market.intMarketID) Like NZ([Forms]![frm_filter]![txtMkt],"*"))
AND ((lkup_FACT.intfactID) Like NZ([Forms]![frm_filter]![txtfact],"*")) AND (
(lkup_Measurement.intMeasurementID) Like NZ([Forms]![frm_filter]![txtMeas],
"*")) AND ((lkup_Process.intProcessID) Like NZ([Forms]![frm_filter]![txtProc],
"*")) AND ((lkup_subProcess.intsProcessID) Like NZ([Forms]![frm_filter]!
[txtSubProc],"*")) AND ((lkup_Frequency.intFreqID) Like NZ([Forms]!
[frm_filter]![txtFreq],"*")) AND ((lkup_Category.intCatID) Like NZ([Forms]!
[frm_filter]![txtCat],"*")))
ORDER BY tbl_Metric.MetricName;
------------------------------------------------------------------------------
-----------------------------------------------------------------
Here is the Main forms VBA code. I have no code in the subform:

Option Compare Database
Option Explicit
Dim frm As Form

Private Sub cboBline_AfterUpdate()

Me.cboSDl = ""
Me.cboManager = ""
Me.cboTeamLead = ""
Me.cboRegion = ""
Me.cboMarket = ""
Me.cboFACT = ""
Me.cboFrequency = ""
Me.cboProcess = ""
Me.cboCategory = ""
Me.cboFCEFCW = ""
Me.cboSubProcess = ""
Me.cboMeasurement = ""


Me.cboSDl.Requery
Me.cboManager.Requery
Me.cboTeamLead.Requery
Me.cboRegion.Requery
Me.cboMarket.Requery
Me.cboFACT.Requery
Me.cboFrequency.Requery
Me.cboProcess.Requery
Me.cboSubProcess.Requery
Me.cboCategory.Requery
Me.cboFCEFCW.Requery
Me.cboMeasurement.Requery
Me.qryFRM_filterQuery.Requery

End Sub

Private Sub cboSDL_AfterUpdate()
Me.cboManager = ""
Me.cboManager.Requery
Me.qryFRM_filterQuery.Requery

End Sub

Private Sub cboManager_AfterUpdate()
Me.cboTeamLead = ""
Me.cboTeamLead.Requery
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboRegion_AfterUpdate()
Me.cboMarket = ""
Me.cboMarket.Requery
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboMarket_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub

Private Sub cboFACT_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboMeasurement_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboFreq_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub

Private Sub cboProcess_AfterUpdate()
Me.cboSubProcess = ""
Me.cboSubProcess.Requery
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboSubProcess_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboCategory_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub cboFCEFCW_AfterUpdate()
Me.qryFRM_filterQuery.Requery
End Sub
Private Sub Form_Open(Cancel As Integer)


Me.cboBline = ""
Me.cboSDl = ""
Me.cboManager = ""
Me.cboTeamLead = ""
Me.cboRegion = ""
Me.cboMarket = ""
Me.cboFACT = ""
Me.cboFrequency = ""
Me.cboProcess = ""
Me.cboSubProcess = ""
Me.cboCategory = ""
Me.cboFCEFCW = ""
Me.cboSubProcess = ""
Me.cboMeasurement = ""
End Sub
Click to expand...
 
Thank you so very much for sending me this code. Just got in this morning,
so I will give it a try, however, I was wandering, just from reading the code
it seems that nothing in the subform will change until the command button is
actually clicked. Is this correct? If so, is there a way to make the
subform change - real time - AS EACH COMBO is changes?
rather than using LIKE in all the criteria that you MAY have
... consider having the subform show all the records and
just building the Filter property from your choices

Assign this to the AfterUpdate event of each one...

=SetSubFormFilter()

then put this code behind the form

'-------------------------
Private Function SetSubFormFilter()

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

if len(mfilter) > 0 then
me.subform_controlname.form.filter = mfilter
me.subform_controlname.form.FilterOn = true
else
me.subform_controlname.form.FilterOn = false
end if

me.subform_controlname.form.requery
DoEvents

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.subform_controlname.form.filteron = false
me.subform_controlname.form.requery
DoEvents
'-------------------------
Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
Hi I have created a form that contains a continuos subform. The main form
contains 15 combo boxes that will be used by the end user to filter out
Click to expand...
[quoted text clipped - 180 lines]
Me.cboMeasurement = ""
End Sub
Click to expand...
Click to expand...
 
you're welcome ;)

.... what command button?

Assign this to the AfterUpdate event of each combo or filter
control:

=SetSubFormFilter()

then, the code is launched everytime you have completed a
change in the filter

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Thank you so very much for sending me this code. Just got in this morning,
so I will give it a try, however, I was wandering, just from reading the code
it seems that nothing in the subform will change until the command button is
actually clicked. Is this correct? If so, is there a way to make the
subform change - real time - AS EACH COMBO is changes?
rather than using LIKE in all the criteria that you MAY have
... consider having the subform show all the records and
just building the Filter property from your choices

Assign this to the AfterUpdate event of each one...

=SetSubFormFilter()

then put this code behind the form

'-------------------------
Private Function SetSubFormFilter()

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

if len(mfilter) > 0 then
me.subform_controlname.form.filter = mfilter
me.subform_controlname.form.FilterOn = true
else
me.subform_controlname.form.FilterOn = false
end if

me.subform_controlname.form.requery
DoEvents

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.subform_controlname.form.filteron = false
me.subform_controlname.form.requery
DoEvents
'-------------------------
Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Hi I have created a form that contains a continuos subform. The main form
contains 15 combo boxes that will be used by the end user to filter out
Click to expand...

[quoted text clipped - 180 lines]
Me.cboMeasurement = ""
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top