Filter For Throws Error when user enters 'in'

[RossW]

I've created a custom shortcut menu for my form that include the built-in
'Filter For:' control (Id:=2863). The problem is this: if a user enters
something like, "Message in a Bottle" (w/o the quotes), Access throws an
error which I'm assuming is because the text contains "in". It seems like
Access doesn't qualify the entry with "" whereas it does when you select
'Filter By Selection'. I can't assume that my end users will know about SQL
reserved key words and I'd like to prevent the app from hanging as it does
when this error occurs.

How/where do I capture this action so I can check if the selected control is
text-based and add the "" to the beginning & end of the entry before Access
uses it as a filter?

 

[Alex Dybenko]

Hi,
try to use form's Filter event, there you can check the filter and cancel
event

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[RossW]

Hi Alex,

I tried that but Access throws the error before it gets to the Filter event
(I had a breakpoint on the first line of code in that method but I saw the
popup error msg first). It seems to go first to the Form_Error method, but
there's no cancel parameter for the filter event there.

Cheers,
Ross

 

[david]

Use the "applyfilter" event.

The data error happens after the filter, after the applyfilter, so it too
late to cancel anything. It just gives you a choice of how you want
to handle the error. You can, sometimes, handle the error by reformulating
the filter and trying again, but better to check first before filtering, in
the
"applyfilter" event.

(david)

 

[RossW]

Hey David,

I already have code in the Form_ApplyFilter() method to display a label when
a filter has been set and hide it when it's cleared. I had a breakpoint in
the first line of code in that method and it still went to the Form_Error()
method first.

Any ideas?

Cheers,
Ross

 

[david]

It may be that it is not called when a Filter is Changed, or it
may be that you can't successfully set breakpoints in that
place.

It is because of difficulties like this that I use code to modify
the form SQL instead of applying form level filters.

Also, some filters are implemented as form design changes,
and are not portable to runtime installations.

(david)