macro halt on parameter query cancel

[Guest]

I am running a macro with the OpenQuery action to run an update query, which
is set up as a parameter query to prompt the user for the new value in the
updated field:

[To change BIN NUMBER, enter new value:]

If the user selects the [Cancel] button, the Action Failed dialog with the
[Halt] button pops up for the macro. Is there some way to avoid this pop-up
or to test for [Cancel] as a condition in the macro?

Also, I had wanted to cross-post this message but I was not sure how to
specify the group. What is the full USENET designation for the Access
Database Queries discussion group?

Thank you,
Oli
04.14.06 11.54 hst

 

[tina]

comments inline.

I am running a macro with the OpenQuery action to run an update query, which
is set up as a parameter query to prompt the user for the new value in the
updated field:

[To change BIN NUMBER, enter new value:]

If the user selects the [Cancel] button, the Action Failed dialog with the
[Halt] button pops up for the macro. Is there some way to avoid this pop-up
or to test for [Cancel] as a condition in the macro?

not in your current design. if you want to handle runtime errors, you have
to use VBA code rather than macros.

suggest you use a form control reference in your query, rather than a
parameter, as

[Forms]![FormName]![ControlName]

use an unbound textbox control in the form, so the user can enter the value.
add a command button to the form, to run the OpenQuery macro. you can set a
condition on the macro action, such as

[ControlName] Is Not Null

that way the query won't run unless the user enters a value in the textbox
control AND clicks the command button.

 

[Guest]

I haven't had a chance yet, but I'll give VBA a try. And thanks for the
USENET designation. I was wondering where one would find that for each
discussion group, and I see now that it's listed at the top of the reply
window where it says, "Post In Discussion Group."

Thanks for the reply,
Oli
04.15.06 06.24

comments inline.

I am running a macro with the OpenQuery action to run an update query, which
is set up as a parameter query to prompt the user for the new value in the
updated field:

[To change BIN NUMBER, enter new value:]

If the user selects the [Cancel] button, the Action Failed dialog with the
[Halt] button pops up for the macro. Is there some way to avoid this pop-up
or to test for [Cancel] as a condition in the macro?

not in your current design. if you want to handle runtime errors, you have
to use VBA code rather than macros.

suggest you use a form control reference in your query, rather than a
parameter, as

[Forms]![FormName]![ControlName]

use an unbound textbox control in the form, so the user can enter the value.
add a command button to the form, to run the OpenQuery macro. you can set a
condition on the macro action, such as

[ControlName] Is Not Null

that way the query won't run unless the user enters a value in the textbox
control AND clicks the command button.

Also, I had wanted to cross-post this message but I was not sure how to
specify the group. What is the full USENET designation for the Access
Database Queries discussion group?
microsoft.public.access.queries

hth


Thank you,
Oli
04.14.06 11.54 hst

 

[tina]

you're welcome

I haven't had a chance yet, but I'll give VBA a try. And thanks for the
USENET designation. I was wondering where one would find that for each
discussion group, and I see now that it's listed at the top of the reply
window where it says, "Post In Discussion Group."

Thanks for the reply,
Oli
04.15.06 06.24

comments inline.

I am running a macro with the OpenQuery action to run an update query, which
is set up as a parameter query to prompt the user for the new value in the
updated field:

[To change BIN NUMBER, enter new value:]

If the user selects the [Cancel] button, the Action Failed dialog with the
[Halt] button pops up for the macro. Is there some way to avoid this pop-up
or to test for [Cancel] as a condition in the macro?

not in your current design. if you want to handle runtime errors, you have
to use VBA code rather than macros.

suggest you use a form control reference in your query, rather than a
parameter, as

[Forms]![FormName]![ControlName]

use an unbound textbox control in the form, so the user can enter the value.
add a command button to the form, to run the OpenQuery macro. you can set a
condition on the macro action, such as

[ControlName] Is Not Null

that way the query won't run unless the user enters a value in the textbox
control AND clicks the command button.

Also, I had wanted to cross-post this message but I was not sure how to
specify the group. What is the full USENET designation for the Access
Database Queries discussion group?
microsoft.public.access.queries

hth


Thank you,
Oli
04.14.06 11.54 hst