Canceling Query by Form

[kctss]

I'm using a query by form to create a report...the query form has a date
range and two buttons, OK and CANCEL. If the user selects CANCEL I just close
the form, but I still get the Enter Parameters dialog box, then the
openreport action canceled dialog box.
Is there a way to eliminate the Enter parameter dialog box and do a clean
cancel, ie just go back to the calling menu form without seeing the
dialog.The OpenReport dialog I should be able to avoid by SetWarnings.

Thanks

 

[Dirk Goldgar]

I'm using a query by form to create a report...the query form has a
date range and two buttons, OK and CANCEL. If the user selects CANCEL
I just close the form, but I still get the Enter Parameters dialog
box, then the openreport action canceled dialog box.
Is there a way to eliminate the Enter parameter dialog box and do a
clean cancel, ie just go back to the calling menu form without seeing
the dialog.The OpenReport dialog I should be able to avoid by
SetWarnings.

What code is involved in this? Does your calling menu form open the
report, or does your form open it? I'd have expected the report to be
opened by the code behind the OK button on the form, in which case the
CANCEL button wouldn't cause the problem you're seeing, because the
report would never have been opened. But that doesn't seem to be the
case. If you can explain how you have this set up, a solution will
probably become clear.

 

[kctss]

You got me thinking, but still not clear on where I need to step in.
The calling menu opens the report.
The on open event of the report opens the dialog form for the date ranges.
The OK button hides the form and the Report close event closes the dialog
form.
The CANCEL button I would like to simply return to the menu, without any
message boxes, etc.
I tried hiding the dialog form and then a cancel event but my On No Data
event jumps in there with its msgbox and then the "Open Report Action was
canceled" msgbox.
I could trap the Open Report Action msgbox, but I don't have the flick on
cleaning up the first part.

 

[Dirk Goldgar]

You got me thinking, but still not clear on where I need to step in.
The calling menu opens the report.
The on open event of the report opens the dialog form for the date
ranges. The OK button hides the form and the Report close event
closes the dialog form.
The CANCEL button I would like to simply return to the menu, without
any message boxes, etc.
I tried hiding the dialog form and then a cancel event but my On No
Data event jumps in there with its msgbox and then the "Open Report
Action was canceled" msgbox.
I could trap the Open Report Action msgbox, but I don't have the
flick on cleaning up the first part.

Using your current approach, you can make a couple of small changes to
fix the problem.

1. On the date-range form: let the OK button hide the form, and the
CANCEL button just close it.

2. In the report's Open event procedure: open the date-range form in
dialog mode, as you are doing now. In the next line of code (to be
executed after the form is hidden or closed), check to see if the form
is still open. If not, that means the user clicked the CANCEL button,
so set the Open event's Cancel argument to True to cancel the report.
The code might look something like this:

'----- start of example code for report's Open event -----
Private Sub Report_Open(Cancel As Integer)

' Open form to get date range for report.
DoCmd.OpenForm "frmDateRange", WindowMode:=acDialog

' Is the report still open now, or did the user close it (cancel)?
If CurrentProject.AllForms("frmDateRange).IsLoaded = False Then
Cancel = True
End If

End Sub
'----- end of example code for report's Open event -----

3. In the code that opens the report, trap and ignore error 2501, which
is the error that is raised any time an action initiated by code is
canceled. Code for that might look like this:

'----- start of example code for "open report" command button -----
Private Sub cmdOpenReport_Click()

On Error GoTo Err_Handler

DoCmd.OpenReport "rptYourReport"

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'----- end of example code for "open report" command button -----

 

[kctss]

Perfect...thanks for turning on the light.

Using your current approach, you can make a couple of small changes to
fix the problem.

1. On the date-range form: let the OK button hide the form, and the
CANCEL button just close it.

2. In the report's Open event procedure: open the date-range form in
dialog mode, as you are doing now. In the next line of code (to be
executed after the form is hidden or closed), check to see if the form
is still open. If not, that means the user clicked the CANCEL button,
so set the Open event's Cancel argument to True to cancel the report.
The code might look something like this:

'----- start of example code for report's Open event -----
Private Sub Report_Open(Cancel As Integer)

' Open form to get date range for report.
DoCmd.OpenForm "frmDateRange", WindowMode:=acDialog

' Is the report still open now, or did the user close it (cancel)?
If CurrentProject.AllForms("frmDateRange).IsLoaded = False Then
Cancel = True
End If

End Sub
'----- end of example code for report's Open event -----

3. In the code that opens the report, trap and ignore error 2501, which
is the error that is raised any time an action initiated by code is
canceled. Code for that might look like this:

'----- start of example code for "open report" command button -----
Private Sub cmdOpenReport_Click()

On Error GoTo Err_Handler

DoCmd.OpenReport "rptYourReport"

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'----- end of example code for "open report" command button -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[justme]

Following the instructions in this post, I changed my error handler to ignore
error 2501 in my report's open event

If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If

However, I still get the error!

What can it be?

Thanks

 

[Arvin Meyer [MVP]]

Try:

Select Case Err
Case 2501
Err = 0
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
' Handle Error or Exit
End Select