Experts - How to bypass the "Enter Parameter Value" 2

[jBelisle]

Greetings,
I know many Theads have already been created about ACCESS’s “Enter Parameter Value” popup response to various scenarios involving questionable SQL statement/filter syntax. My question is this - Does anyone know of a way to disable this popup response or maybe somehow trap like an error or is there an undocumented flag that can be set to disable this popup response? I don’t want bother everyone with details why…I simply would like to handle parameter issues/questions/what ever in my own ways.

FYI – Woking with Form.Filter I have already worked with ACCESS 2002’s “WithEvents” in redirecting Command Bar “Apply Filter” event with no success. Yes I was able to trap an event before the filter is applied, but at that point I am not able modify the .Filter property resulting in the same unwanted parameter popup.

Thanks for listening!

Sincerely annoyed with Microsoft,
JB

 

[RickSpr]

On a second reading of your post, I seem to read it differently. Is it only when the user requests filtering that you need to catch the parameter prompt? If so, use the form's Filter and ApplyFilter events. You can completely substitute your own filter mechanism for all aspects of filtering on that form. No need to sink CommandBar events.

That's the problem with "I don't want to bother everyone with details why"-type statements. Usually, we need the details to give a useful answer; they narrow the problem down to a manageable size. When we have to guess at details, we often come up with half a dozen solutions for different specific circumstances. Then, instead of an answer, you get a request for more details. Details are good! (I appreciate you not wanting to overwhelm us with them, though; that can be a problem, too.)

Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jBelisle]

Rick,
Thanks for the response and I understand your need for details...I guess what I was looking for was a central mechanism to trap/turn off this popup feature regardless of which of the many scenarios caused it’s activation.

One example…
The popup occurs when a field, a criteria, an expression, or a control in a query, a form, or a report references a name that Access cannot find.

I was looking for a way to have ACCESS to respond in all these cases with a system error instead of the popup form. I am working on a mammoth ACCESS application that is intricate by design and contains’ many areas that unintentionally invoke this popup. My intentions were to find short term solutions to allow time for a redesign of certain functionalities and by the looks of things, it doesn’t look like I will be successful in this endeavor.

Thanks anyway!
JB

 

[RickSpr]

No, I'm afraid there's no global switch to turn off a prompt for parameters. You see, the prompt doesn't really come out of Access code at all. Access just builds the SQL statement in all these scenarios and submits it to Jet for execution. When Jet finds something it can't recognize, it passes it to an interface call the "Visual Basic Expression Service" to see if VBA can figure it out. It's the Expression Service that prompts for the value, and that's two levels down from the Access code, so Access has no control over whether the prompt happens. (Except that it's Access that hooks up the Expression Service for Jet to use in the first place, but that happens when Access starts up, so if you disabled it, it would apply globally and you wouldn't be able to use parameters at all, anywhere. You wouldn't even be able to use references to form fields in queries, in fact!) Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[annsolomon]

Rick, your post is VERY interesting. I posted a similar question on several sites and have been beating myself up trying to figure out how to customize that darned default popup until I read your post. Thanks for saving me from myself.
Ann

 

[jBelisle]

I concur with Ann, excellent feedback Rick!
Thanks again for your time!

JB