How does Docmd.ApplyFilter work?

Question

Can anyone explain how to use DoCmd.ApplyFilter ?

I have some code that does custom searches from a bound form. It used to work fine but now it gives either a 'Data Type Mismatch' error or 'No current Record' error. I get the former if there were records on the form and the latter if there were no records on the form when the search was initiated.

The ApplyFilter command triggers the Form_Current event and the error generates at the exact point when Form_Current returns to the routine that triggered it. I do not get control back after the ApplyFilter, it goes straight to my error handling routine. Single-stepping through the executing code told me nothing.

Help needed. Did the way these commands work change recently?

This is Access 2010. Posting this message because Access Help was absolutely useless. A search for 'Docmd.Applyfilter' returned:

Introduction to the Access 2010 file format

Article

Web compatibility check: Macro errors

Article

Answer 1

Hi,

As you said, the ApplyFilter method triggers the Form's Current event. Are you executing the ApplyFilter method in the Current event? If not, then what do you have in the Current event that could fail if there are no records as a result of applying the filter?

Answer 2

No, the ApplyFilter is not in the Form Current event.

I have stepped through the Current event and nothing fails. It reaches the last statement, exits and immediately goes to the error handling in the calling routine.

Answer 3

Okay, try replacing the DoCmd.ApplyFilter method with simply assigning the filter to the form and turning it on just to see if the problem goes away. For example, if we're filtering the current form:

Me.Filter = "FieldName='SomeValue'"

Me.FilterOn = True

Hope it helps...

Answer 4

I tried it but it made no difference. Still get same error. Immediately Form Current exits, it goes to the error handling in the procedure where the DoCmd.ApplyFilter is.

I tried compacting and repairing but made no difference either.

Would any other event be firing after the Form Current?

Answer 5

Maybe time for /decompile.

---

-Tom. Microsoft Access MVP

Answer 6

I think I may have a syntax error in the SQL of the filter. Odd that it would cause this behavior. Currently trying to research what is wrong with the SQL. It used to work fine and has not been changed.

Answer 7

Would any other event be firing after the Form Current?

According to this page, the Current event is usually the last event to fire. Let us know what you find with your SQL statement. If you can post it, we might be able to help as well.

Answer 8

Hi AllTheGoodNamesWereTaken,

First of all I want to inform you that the link you had posted in the original post are blank.

you had mentioned ,"Can anyone explain how to use DoCmd.ApplyFilter ?"

visit the link below.

-DoCmd.ApplyFilter Method (Access)

but I think that it will not going to solve your issue.

so it is better that you post the code that reside in Form_Current().

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 9

My Filter SQL was comparing dates with DateValue(datecolumn), I had to change the syntax to DateValue(CStr(datecolumn))

It's strange that this used to work in Access 2003 but Access 2010 needs the modified syntax.

However, this did not solve my 'no current record' error.

Answer 10

As I stated, I single-stepped through Form_Current and it runs without error and reaches the last statement. When it exits back to the calling function, it goes straight to the error handling routine.

I'm assuming this is the point where the filter actually runs?

Unfortunately, I have now been diverted to another task so I cannot work on this full-time.

Answer 11

Hi AllTheGoodNamesWereTaken,

without any code we are unable to understand what's going there in your code.

from your above mentioned description we can only assume that this can a possible reasons to occur this error but we can only predict here and it will not solve the problems every time.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.