Action on Error Condition with MACROS

[John Baker]

Hi:

I have a MACRO and as one step in it I am opening a form, which is based on a query. The
query calls for entry of a portion of an associates name as the selection criteria for
records. Using the form : Like [ Enter first letters of Associates Name: ] & "*"

The snag is that the box that comes up offers the opportunity to click on "Cancel". When
the user clicks on "Cancel" (intending to stop the process) the system appears to close
out totally. In other words, there is no program response to the cancel command. With a
macro, I would like to be able to intercept the Cancel call, and close the macro without
any further action. Is there some other way to do this, or have I got myself in a hopeless
snarl? I tried putting a close on no data macro in the form, but that does not appear to
do the job either.

Best

John Baker

[Allen Browne]

Macros have no error handling.

As you found, they are only useful for a *very* limited range of things.

Looks like you have outgrown them, and it's time to dip your feet into VBA
where error handling is part of every procedure you write.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Baker" <Ba******@Verizon.net> wrote in message
news:an********************************@4ax.com...

I have a MACRO and as one step in it I am opening a form, which is based on a query. The query calls for entry of a portion of an associates name as the selection criteria for records. Using the form : Like [ Enter first letters of Associates Name: ] & "*"
The snag is that the box that comes up offers the opportunity to click on "Cancel". When the user clicks on "Cancel" (intending to stop the process) the system appears to close out totally. In other words, there is no program response to the cancel command. With a macro, I would like to be able to intercept the Cancel call, and close the macro without any further action. Is there some other way to do this, or have I got myself in a hopeless snarl? I tried putting a close on no data macro in the form, but that does not appear to do the job either.

Best

John Baker

[John Baker]

Thanks.

Unfortunately my VBA sills are very very limited.

How would I open a form (based on a query), check for an error and then close the form if
there was an error,or return to the main macro?

If you could give me a simple outline of the commands required to do this it would be a
great help.

Thanks

John baker

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:

Macros have no error handling.

As you found, they are only useful for a *very* limited range of things.

Looks like you have outgrown them, and it's time to dip your feet into VBA
where error handling is part of every procedure you write.

[Allen Browne]

In the Database window, choose the Modules tab, and click New.

In the new module, create a function by typing:
Function WhateverYouWantToCallIt()
Access adds
End Function

Between the 2 lines, enter:
DoCmd.OpenForm "Form1"
Include the quotes. Replace Form1 with the name of your form.

To call the function from the macro, use the RunCode action. That's the
basics.
Here's a more comprehensive example.
Save the code below into your module.

In your macro, choose the StopMacro action, and put this beside it in the
Condition column (View menu if you don't see a Condition column):
Not FormOpened("Form1")
This will cause the macro to stop if the form did not open; otherwise it
continues to the next line.

For more information about error handling, see:
Error Handling in VBA
at:
http://allenbrowne.com/ser-23a.html

----------code starts-----------
Function FormOpened(strForm As String) As Boolean
On Error GoTo Err_Handler
'Argument: Name of the form to open
'Return: True if the form opened.

DoCmd.OpenForm strForm
FormOpened = True

Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
Resume Exit_Handler
End Function
----------code ends-----------

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"John Baker" <Ba******@Verizon.net> wrote in message
news:nt********************************@4ax.com...

Thanks.

Unfortunately my VBA sills are very very limited.

How would I open a form (based on a query), check for an error and then close the form if there was an error,or return to the main macro?

If you could give me a simple outline of the commands required to do this it would be a great help.

Thanks

John baker

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:

Macros have no error handling.

As you found, they are only useful for a *very* limited range of things.

Looks like you have outgrown them, and it's time to dip your feet into VBAwhere error handling is part of every procedure you write.

[John Baker]

Allen:

Thank you very much indeed. This was most helpful.

Regards

John Baker

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:

In the Database window, choose the Modules tab, and click New.

In the new module, create a function by typing:
Function WhateverYouWantToCallIt()
Access adds
End Function

Between the 2 lines, enter:
DoCmd.OpenForm "Form1"
Include the quotes. Replace Form1 with the name of your form.

To call the function from the macro, use the RunCode action. That's the
basics.
Here's a more comprehensive example.
Save the code below into your module.

In your macro, choose the StopMacro action, and put this beside it in the
Condition column (View menu if you don't see a Condition column):
Not FormOpened("Form1")
This will cause the macro to stop if the form did not open; otherwise it
continues to the next line.

For more information about error handling, see:
Error Handling in VBA
at:
http://allenbrowne.com/ser-23a.html

----------code starts-----------
Function FormOpened(strForm As String) As Boolean
On Error GoTo Err_Handler
'Argument: Name of the form to open
'Return: True if the form opened.

DoCmd.OpenForm strForm
FormOpened = True

Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
Resume Exit_Handler
End Function
----------code ends-----------