Filter not working in 2007 (1 Viewer)

hi,

I have now imported my app from 97 to 2007 and its now about 98% working, thanks for the previous information the guys on this forum gave me. Out of almost 100 forms doing different things the problems were small once I figured out what was happening. Mind you it did take hours and hours of hair pulling to figure out some of the simple issues. Now the whole program is working again apart for some basic but crucial elements.

I have 3 things not working.. The first is filters, the second is shortcut menu bars and toolbars, the third is getting rid of all the unwanted access menus (but I have not looked at this yet, I am guessing it will be simple)

For now I would like to fix the filters as they are a major component.

For example: I have a combo box where after the selection is made it runs the command form the AfterUpdate:

Docmd.Applyfilter, "[method]=forms![main]![combo32]


I have a series of these on this form, the form opens through query which presents records between certain dates, this works fine...

There are numerous combo boxes that run different filters, and some also can filter and print direct to a report.

The first time I select the filter it works fine. But the next time it fails.. If the filter returned 25 records the first time then each time I select it after that it returns one less and stays on the original filtered data..

This has driven me crazy for about 4 days, I have read and read about this on forums and typing into google to see what returns, I even got out 4 books from the library but they are no help, after reading on forums I have used all wonderful things such as:

Me.FilterOn=True
Me.FilterOn=False
Me.FilterOn=""

I have tried there above in different arrangements with my filters and I cannot get it to work.

Does anyone have any ideas..

First thing: Have you installed the SP2 update for Office 2007? In not then you should do it.

thanks Hiteck,,

yes I have installed service pk 2, I wished it was that simple, I even attempted to reinstall it in case I did forget, but it came back and told me the service pk was already installed.
.

Making sure SP2 is installed for Access/Office 2007 is the first step.

When convertion fomr olders datasbes foamts to 2007, I find hta some forms do not convert very well. I have had to recreate the forms in 2007. The good news this is very easy to do.

See: Backing up Objects

I would do this for the form and any sub forms used by the form.

thanks HiTech,

I was not aware of this ability, I will test it out...

However I have been helping a person from Perth who is creating a project and he has been unable to get around filters and some other issues he is having. So as a favor I decided to have a look at his form to see what needs changing.

This form has been created in 2007 and as part of his problem is filtering I tested out my method of filtering and the same problem comes up. So I am starting to feel that the method of filtering which I was using is no loner possible with 2007.

97 would instantly change the filtered records upon striking another instruction to filter. But everywhere I read it appears that tones of people of filter issues and are forced to use commands to turn filters on and off and other wonderful ideas.

Docmd.Applyfilter, "[method]=forms![main]![combo32]

The above command can be clicked from a combo box as many times as I like and in 97 it will change the filter according to the selection in the combo box. This also works the same if I have several combo boxes and even command buttons on a form. In 97 it always carries out the most recent instruction.

Trying this same method on a form and database created in 2007 still does not work. I tested this on the database form my friend in Perth and I get the same problems. Its obvious I need to move from 97 to 2007 but my whole concept of filtering is now non existent.

Do you or anyone else have any idea how I can get a form to filter using the most basic method of filtering (such as above). If for some reason 2007 has to have all the filters cleared and turned on and off to be able to refilter on an open form is there any set logic to do this, as nothing I have tried or read even points to logic (although I am assuming the is some logic somewhere).

At present I have had to look at SQL Statements to try a work around. I am not sure if this is going to create more problems for me or not.

That is interesting. I will see if I can reproduce this issue.

I normally do not use filters. I use a "where" clause in an SQL statement or the "where" parameter of the DoCmd.OpenForm. I find that using filters can cause slow performance issues. Filters seam to work best when a database is not split and on the local hard drive. I do ever database split with the back end on a different machine. So I have always avoid using filters.

thanks Hitech,

if you can find a solution then that would be great.

yes I am starting to think that I need to understand sql and using these other statements. I guess we get so use to what we find that works that everything outside the little box in our heads is scary. I know, ummm maybe nothing about writing an sql request so its all new thinking for me.

I have had a small win with now being able to filter on of the combos with an sql statement. Maybe its time to stretch my brain some more. I guess looking at my forms one would think that sql was used but its all filtering. Obviously I use queries which are useful for opening a form and keeping the data to a set criteria but after that I have always used filters, so maybe I have more to learn.

But now that you mention about the speed it does make sense as speed is an issue, in fact i have had to slow some aspects down as the filtering is not finished in time. So maybe this is a good thing, maybe i need to spend the time getting my head around this.

Do you know some good examples databases or forms which use basic sql statements in a practical sense that I can have a look at. You mention that you use the sql when you open a form. I followed your link to your site, is there any particular set of examples on your site that would help me to understand this more.

thanks...

I don't know if this is a typo or just an oversight but your string is not closed:

Docmd.Applyfilter, "[method]=forms![main]![combo32]"

I think it's a typo because you would have had an error message if that wasn't there.

I suspect that the reference is not being resolved properly so you should try this instead:

Docmd.Applyfilter, "[method] = " & Me.Combo32

But I prefer using the direct method, although I never use the Filter like HiTech, but if I were, I would use the form's Filter method:
If Combo32 returns text then it must be wrapped in single quotes like so:

thanks vba

yes it was a typo.. I tested out your method and it works, thanks for that.

I then did some tests with the filtering and using sql as per what hitech mentioned and I was very surprised with the results in time delay. The sql and the filtering is similar in delay time with reporting the records to the screen, but I am not sure how much difference it would be with a large table of records.

But there are math calculations on the screen and filtering takes a few seconds to calculate but the sql is instant. I am very surprised by the time difference, having almost instant calculations of the formulas could mean that some of the screens could run smoother.

thanks vba and hightech I can now use which ever method is more appropriate at that time.

I am starting to think the the change to 2007 may be all worth it.

Thanks for the update.

Sounds like you are making some great progress.

Great to hear some positive feedback too.

FYI: I had a problem with a REPORT - and as you know, there is no where clause there. I was able to resolve by setting the filter AFTER I had changed the recordsource. In previous versions of Access that was not necessary.