How to permanently remove filter from subform

[sschrupp]

Somehow my forms keep thinking that they have a filter that causes all my data to disappear. This happens after the first time I open the form and go to design view. Once I do that this mysterious filter pops into existance and won't go away.

When this happens no data is shown until I manually go to Records -> Remove Filter/Sort. Once I click that the data shows up as normal, but only for the time you are currently looking at it. If you close the form and re-open that filter is back and no data is shown.

Nowhere in my code do I tell it to filter. No events tell it to filter. In the properties of the form there is nothing in the Filter property.

If I programmatically try and remove filters and sorts in the ****** or OnOpen using the below code it does not work.

   If Me.FilterOn = False Then
      Me.Filter = ""
      Me.OrderBy = ""
   End If

Changing the recordsource does nothing as well.

Searching the net I found mention of this and it said to disable Name AutoCorrect, compact/repair, decompile the database, then compact/repair again. This did not help unless I didn't decompile the database properly.

Does anyone have any other ideas on how to get rid of this mysterious filter that doesn't exist? I really don't want to tell the users that they have to manually remove filters in order to see their data.

Also, any solution I use must not keep the user from doing their own filtering or sorting of the data.

 

[sschrupp]

Re: [SOLVED] How to permanently remove filter from subform [SOLVED]

Ok, I tried to decompile again and this time it worked. For anyone that comes across this issue here is what I did. I found these steps at http://www.granite.ab.ca/access/decompile.htm

1. Back up the database just in case

2. Create a .bat file with the contents being:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Temp\name of database.mdb" /decompile

Quotes included and of course your location of MSACCESS.EXE might be different, and your database you can store anywhere besides C:\Temp

3. Run the .bat file

4. Compact/repair database

5. Go into your VBA code and Compile/Debug it.

6. Compact/repair database

I believe I may have missed one of the compact/repairs, or maybe I didn't re-compile the code or something the first time I tried this.

After taking these steps exactly as shown the form no longer has some invisible filter/sort going on. So if anyone has a form or query with missing or empty data or records or find they have a hidden or invisible filter or sort then give these steps a try. It might just solve the problem.

 

[sschrupp]

ARGH! The form somehow did it again. I'm playing around with various triggers, on the main form, adjusting various routines in VBA and *POOF* the subform is no longer showing data unless I open the subform by itself and manually go to Records -> Remove Filter/Sort. Doing so doesn't fix the problem of not seeing any records when using the main form.

This is really annoying if I have to decompile and recompile every single time I make changes to the database.

Does anyone have any clue why this is happening, and some way of getting around it?

 

[SydneyGeek]

How are the forms synchronised? For example...
Are the forms set up with master and child fields? Or does the subform have a query with criteria based on the main form?

Denis

 

[revans]

   If Me.FilterOn = False Then
      Me.Filter = ""
      Me.OrderBy = ""
   End If

This should be

If Me.FilterOn=True then
  Me.Filter=""
  Me.OrderBy=""
  Me.AllowFilters=False
End If

Alternatively you could just set them without the if/then.

If this doesn't work, I suggest creating a new subform and possibly a new form. Sometimes Access gets weird corruption where the only solution is to start over.

hth,

Rich

 

[sschrupp]

How are the forms synchronised? For example...
Are the forms set up with master and child fields? Or does the subform have a query with criteria based on the main form?

Denis

The main form is totally unbound and not synchronised with the subform. The subform is based on some SQL and refers to a user name stored in a hidden form. In the subform's "current" it fires off some code that then updates fields on the main form.

So the user basically has a datasheet that they can scroll through and select records to see more detail, add comments, etc.

It's really odd because when I first make the form and subform it all works just fine. It just seems like at some point when I go to make changes in design view that things get muddled up and the subform decides that it should filter the SQL down to no records showing.

There are no filters involved anywhere in my code so I just don't understand how that's happening.

Revans, you're right that was an error on my part and long since been changed. Like you suggest I've just simply gotten rid of the IF and tried forcing filters away but with no luck. I've tried doing that in the OnOpen, ******, OnActive, OnWhaterICouldThinkOf and it's a no go. Nothing in code will get rid of the filter. Only manually selecting Records -> Remove Filter/Sort works. Argh!

So I'm to the point where I'll keep remaking the forms until I have everything working how I want, and then do a final remake and never go into design view again. Maybe then the forms will continue to work.

I swear there's a gremlin in my machine!

 

[lonabec]

I know that this is late to the show, but I had this problem in the past. My experience is that unless you ALSO TURN OFF the FilterOn property, you will continue to have problems sporadically. I use the following whenever I want to cancel form filtering...

If Me.FilterOn=True then
Me.Filter=""
Me.FilterOn=False
Me.OrderBy=""
Me.AllowFilters=False
End If