Action Failed

[Guest]

Currently, I have a macro that runs a Make Table Query, when a button on a
form is pressed. I have SetWarnings = No. When the Query Runs It first
Prompts the user to enter a value. Whenever the user hits cancel on the box
they get an "Action Failed" Box that will only allow them to halt.

My question is, is there a way to not have that come up when the user clicks
cancel and it will just cancel? I would perfer to be able to do that with a
macro rather than VB simply because I am not very good with using VB.

Thanks for your help

 

[Allen Browne]

Anthony, one of the limitations of macros is exactly the problem you
describe: no error handling.

Might be time to bite the bullet and begin some VBA.

 

[Guest]

Yeah...I had a feeling that I was going to have to do that. Could you
perhaps give me a crash course in how to get this macro into VB ? I used
Access' Conversion tool to make the macro into VB below is a copy of the code:

Function Module_Enter_Part_Number()
On Error GoTo Module_Enter_Part_Number_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "Lookup Sizes", acNormal, acReadOnly
DoCmd.Close acQuery, "Lookup Sizes"
DoCmd.OpenForm "Forms Part Number results", acNormal, "", "", acEdit,
acNormal


Module_Enter_Part_Number_Exit:
Exit Function

Module_Enter_Part_Number_Err:
MsgBox Error$
Resume Module_Enter_Part_Number_Exit

End Function


First off, is that correct?
Second, how do I get it to run when I click a command button on my Form?

 

[Allen Browne]

Starting from the end of your question, set the On Click property of your
button to:
[Event Procedure]

Then click the Build button (...) beside this property.
Access opens a code window, and creates the "Private Sub..." and "End Sub
lines.

Place your code in between.

If Lookup Sizes is an action query (append, update, delete, or make table),
use RunSql rather than OpenQuery. Then there is no need to close the query.
If it is a select query, i could not follow why you would want to open it
and close it immediately again.

Assuming an action query, the code I would prefer would be just this (with
the error handler):
dbEngine(0)(0).Execute "Lookup Sizes", dbFailOnError
DoCmd.OpenForm "Forms Part Number results"

For more ideas on the error handler, see:
http://allenbrowne.com/ser-23a.html
The article explains how to log the errors to a table. You don't need that,
but the suggested layout and explanation may help.

 

[Guest]

Yeah, looking back on that it seems redundant, but it works great now, thank
you for your help

Starting from the end of your question, set the On Click property of your
button to:
[Event Procedure]

Then click the Build button (...) beside this property.
Access opens a code window, and creates the "Private Sub..." and "End Sub
lines.

Place your code in between.

If Lookup Sizes is an action query (append, update, delete, or make table),
use RunSql rather than OpenQuery. Then there is no need to close the query.
If it is a select query, i could not follow why you would want to open it
and close it immediately again.

Assuming an action query, the code I would prefer would be just this (with
the error handler):
dbEngine(0)(0).Execute "Lookup Sizes", dbFailOnError
DoCmd.OpenForm "Forms Part Number results"

For more ideas on the error handler, see:
http://allenbrowne.com/ser-23a.html
The article explains how to log the errors to a table. You don't need that,
but the suggested layout and explanation may help.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yeah...I had a feeling that I was going to have to do that. Could you
perhaps give me a crash course in how to get this macro into VB ? I used
Access' Conversion tool to make the macro into VB below is a copy of the
code:

Function Module_Enter_Part_Number()
On Error GoTo Module_Enter_Part_Number_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "Lookup Sizes", acNormal, acReadOnly
DoCmd.Close acQuery, "Lookup Sizes"
DoCmd.OpenForm "Forms Part Number results", acNormal, "", "", acEdit,
acNormal


Module_Enter_Part_Number_Exit:
Exit Function

Module_Enter_Part_Number_Err:
MsgBox Error$
Resume Module_Enter_Part_Number_Exit

End Function


First off, is that correct?
Second, how do I get it to run when I click a command button on my Form?