Form permanently filtered?

[sevilla10 via AccessMonster.com]

I have form which I want to launch from VBA. I tested the form calling it
from the access menu and was able to roll through the table contents, editing
and adding as required.

I then added the following line to another form to call the code:

DoCmd.OpenForm "FAso", , , "[DS]='" & Me.DN & "'", , , Me.DN

The form correctly launched a few times but now it will not. Further, when I
open the form in access 2003, it does not show any records. I can get the
records to show by clicking ADD FILTER and then REMOVE FILTER in the
datasheet mode. I can also get the contents to show with this in ON OPEN FORM:


Me.FilterOn = True
Me.FilterOn = False

On closer inspection, if I open the form in FORM VIEW and click RECORDS/
FILTERS/ ADVANCED FILTERS I see a filter defined as “FAso1†but I cant remove
it. When I delete and try to save, I get

“The Microsoft Jet database engine could not find the object <name>. Make
sure the object exists and that you spell its name and the path name
correctly. (Error 3011)â€

This sounds like the object is corrupted somehow. Is there anyway to recover
from this? I spend the past 3hrs trying to find a way..

 

[ruralguy via AccessMonster.com]

Try importing your db into an empty db and see if the problem follows.

I have form which I want to launch from VBA. I tested the form calling it
from the access menu and was able to roll through the table contents, editing
and adding as required.

I then added the following line to another form to call the code:

DoCmd.OpenForm "FAso", , , "[DS]='" & Me.DN & "'", , , Me.DN

The form correctly launched a few times but now it will not. Further, when I
open the form in access 2003, it does not show any records. I can get the
records to show by clicking ADD FILTER and then REMOVE FILTER in the
datasheet mode. I can also get the contents to show with this in ON OPEN FORM:

Me.FilterOn = True
Me.FilterOn = False

On closer inspection, if I open the form in FORM VIEW and click RECORDS/
FILTERS/ ADVANCED FILTERS I see a filter defined as “FAso1†but I cant remove
it. When I delete and try to save, I get

“The Microsoft Jet database engine could not find the object <name>. Make
sure the object exists and that you spell its name and the path name
correctly. (Error 3011)â€

This sounds like the object is corrupted somehow. Is there anyway to recover
from this? I spend the past 3hrs trying to find a way..

 

[sevilla10 via AccessMonster.com]

Try importing your db into an empty db and see if the problem follows.

thanks- the problem remained but is solved now.

Launching the form with DoCmd.OpenForm "FAso", , , "[DS]='" & Me.DN & "'"
results in the form’s filter field being set to the results of "[DS]='" & Me.
DN & "'" while the form is active. If there is a problem with the VBA in the
form and you correct and save the code, the filter field is set to whatever
was in there when the error occurred.

The problem is that when you launch a form with the filter set in this way,
FLTR does not show in Access. If you open the Form in design, change the
property, save, and open again the filter property remains- it is impossible
to change the filter field in the property! The only way to remove the filter
is open in the UI, RECORDS/ REMOVE FILTER, and then save.

To avoid this problem during development and to ensure nothing strange might
happen once the DB goes live, I am now passing the filter field as an
argument using this code in ON OPEN:

Me.FilterOn = True
Me.FilterOn = False
Me.Filter = "DN='" & Me.OpenArgs & "'"
Me.FilterOn = True

Regards

 

[BruceM via AccessMonster.com]

I would have thought the Open event is too soon for filtering, as the records
have not yet been loaded. You may be able to set FilterOn to False (not sure
why you set it to True, then False), but you may need the Load event to set a
new filter. I could be wrong, as it could be you are setting the Filter and
FilterOn form propeties as if you had changed them in the Property Sheet, but
it may be worth noting for future reference that there is no recordset yet at
the form's Open event.

Try importing your db into an empty db and see if the problem follows.

thanks- the problem remained but is solved now.

Launching the form with DoCmd.OpenForm "FAso", , , "[DS]='" & Me.DN & "'"
results in the form’s filter field being set to the results of "[DS]='" & Me.
DN & "'" while the form is active. If there is a problem with the VBA in the
form and you correct and save the code, the filter field is set to whatever
was in there when the error occurred.

The problem is that when you launch a form with the filter set in this way,
FLTR does not show in Access. If you open the Form in design, change the
property, save, and open again the filter property remains- it is impossible
to change the filter field in the property! The only way to remove the filter
is open in the UI, RECORDS/ REMOVE FILTER, and then save.

To avoid this problem during development and to ensure nothing strange might
happen once the DB goes live, I am now passing the filter field as an
argument using this code in ON OPEN:

Me.FilterOn = True
Me.FilterOn = False
Me.Filter = "DN='" & Me.OpenArgs & "'"
Me.FilterOn = True

Regards

 

[sevilla10 via AccessMonster.com]

Hi Bruce, you obviously know more about Access inner workings than I.

I do not know why I need to toggle the filter but it does not work unless I
do true/false. The property remains set. Maybe someone else can shed light on
this?