Weird Filter Issue

[Wayne Emminizer]

I have a form with combo boxes the filter the records on
a subform. With each choise the filter works fine. I
have a "Clear Criteria" button that sets the combo boxes
to null and sets the subform filter to "" to display all
records. That works fine too. My problem is that after
setting the filter and then clearing it when you go into
design view of the form the FILTER property is still set
to the precleared filter value. The reason this is a
problem is my combo boxes pull their available values
from a query using that subform filter as the WHERE
clause. So if I clear the value from the combo box all
of the subform records come back which they should but
since the subforms filter property still registers a
filter my combo box values don't report correctly. If I
close and reload the form I am all set.....any thoughts?

 

[Jonathan Parminter]

-----Original Message-----
I have a form with combo boxes the filter the records on
a subform. With each choise the filter works fine. I
have a "Clear Criteria" button that sets the combo boxes
to null and sets the subform filter to "" to display all
records. That works fine too. My problem is that after
setting the filter and then clearing it when you go into
design view of the form the FILTER property is still set
to the precleared filter value. The reason this is a
problem is my combo boxes pull their available values
from a query using that subform filter as the WHERE
clause. So if I clear the value from the combo box all
of the subform records come back which they should but
since the subforms filter property still registers a
filter my combo box values don't report correctly. If I
close and reload the form I am all set.....any thoughts?
.

Hi Wayne,

my thought is... don't use this approach. Instead have the
subform recordsource a saved query. Then you build an sql
where condition using the comboboxes that you append to
the known base query. This you set as the forms new
recordsource.

dim strSQL as string
dim strWhere as string

if not isnull(comboSomething) then
if len(strWhere)>0 then
strWhere = strWhere & " AND "
end if
strWhere = strWhere & "[fieldname]=" & comboSomething
end if

strSQL="Select * From qryRecordSource " _
& "Where (" & strWhere & ");"

me.recordsource=strSQL

Luck
Jonathan