Removing Filter

[Guest]

I have a list form to select companyId and use an unbound combobox to sort
company category. After making a selection, I open a edit form and I get the
correct record. My problem is removing the filter in the edit form. I have an
unbound combobox which I use to sort company category and it works find until
I use the list form, I use a isloaded to check for list form in the Edit Form
to set me.cboCompanySort = Forms!list.cboCompanyCategory.Colum(0).
I cannot remove filter [CompanyId] IN ('RAICAL'). I have try on
cboCompanySort_AfterUpdate:

me.fitleron = false
me.fitler=""
me.requery

It does not clear the property setting when form is view in designview, when
I use a msgbox to check me.filter it shows nothing or I would think null.
After selecting a different category the records do not change from first
filter. the only way I can get the cboCompanySort to work right is by
deleting Filter in designview property sheet.

thanks in advance for any help on this.

 

[Larry Linson]

As you appear to have sufficient information to construct an SQL statement
that will return just the one Record that you want to edit, why don't you
handle the problem that way instead of using a filter (I presume the filter
to which you refer is the Filter or Where argument of DoCmd.OpenForm).
Filters are, by design, "sticky". If you use a single-Record Record Source,
then close the Form, I think you'll simplify your task.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,
I use SQL statement for a listbox in the CompanyList form, there are three
diffent statement for Row Source of the listbox. I have two unbound combo box
with Row Source Type set to Value List, (one is used to sort all, archive, or
current company's; the other one takes care of company category, there are 5
for both forms). I have this form set so I can select more than one company
from the same category, and then I want to click Edit button to open
CompanyInfo form with the one's selected.
' Open the companies form filtered on the selected companies
strWhere = "[CompanyID] IN (" & strWhere & ") "
DoCmd.OpenForm FormName:="frmCompanyInfo", WhereCondition:=strWhere

I have put basic info for Company's in one table.

The CompanyInfo form Record Source I used the build button to make the query
for this form and set the criteria: WHERE
(((tblCompanyInfo.Category)=[Forms]![frmCompanyInfo]![cboCompanySort]))
I had to put me.requry in the forms onload event to get this to work. The
cboCompanySort Row Source Type: Value List and I have set the value's for
this form and have a Default Value. After making a selection for
cboCompanySort_AfterUpdate I requery the form.

The problem is opening CompanyInfo form and setting cboCompanySort to match
the same Category as CompanyList. I was able to do that, but now
cboCompanySort on the CompanyInfo form will not work right unless I close the
form.

As you appear to have sufficient information to construct an SQL statement
that will return just the one Record that you want to edit, why don't you
handle the problem that way instead of using a filter (I presume the filter
to which you refer is the Filter or Where argument of DoCmd.OpenForm).
Filters are, by design, "sticky". If you use a single-Record Record Source,
then close the Form, I think you'll simplify your task.

Larry Linson
Microsoft Access MVP

I have a list form to select companyId and use an unbound combobox to sort
company category. After making a selection, I open a edit form and I get
the
correct record. My problem is removing the filter in the edit form. I have
an
unbound combobox which I use to sort company category and it works find
until
I use the list form, I use a isloaded to check for list form in the Edit
Form
to set me.cboCompanySort = Forms!list.cboCompanyCategory.Colum(0).
I cannot remove filter [CompanyId] IN ('RAICAL'). I have try on
cboCompanySort_AfterUpdate:

me.fitleron = false
me.fitler=""
me.requery

It does not clear the property setting when form is view in designview,
when
I use a msgbox to check me.filter it shows nothing or I would think null.
After selecting a different category the records do not change from first
filter. the only way I can get the cboCompanySort to work right is by
deleting Filter in designview property sheet.

thanks in advance for any help on this.