OpenQuery Macro that errs on Cancel

[Guest]

Button on switchboard runs a macro that opens a query. Query prompts for
parameter value with default OK or CANCEL buttons in param value dialog box.
If value IsNull and OK is clicked - datasheet view opens with resultset of no
records - OK so far. If value IsNull and CANCEL is selected - Action Failed
err occurs.

Macro is 2 steps: 1) SetWarnings OFF; 2) OpenQuery...

How do you suspend system errors? Is there another way to run a query from a
button on the Switchboard menu?

Many thanks for any help on this,
Chris

 

[abbarition]

I'm not sure if this is the best option, but it may get
you the results you want. How about having the query pull
from a form instead of asking for the parameter? Create
an unbound pop-up form with a text box. Add an "OK"
command button (and even a "Cancel" one if you want it).
Set the OK button to run your macro (the Cancel button
could just run the form). Then change your query criteria
to the form's text box ([Forms]![FormName]![TextBox]).

The only problem is you'll have to set your macro to
close the form after it opens the query. If the user just
looks at or enters data and closes the query, you'll be
fine. If the user tries to go to the design of the query
and then back to the select view, they'll get a parameter
box asking for the form value. I guess the only good news
to that is that if they press Cancel from there, they
don't get the Action Failed message!

Depending on your database, you could possibly choose to
hide the form in the macro instead of closing it. But
then you'll have to designate when/how to close the pop-
up form.

Hope that helps!