Cancel button help

[fat controller]

I have a small form that is used as a pop up dialog when printing reports (works fine), and on that form is a cancel button, which very simply closes the dialog form:

DoCmd.RunMacro "CloseDateDialog"

However, when cancel is clicked the report continues to run and a dialog box appears asking for the parameters (that it would have obtained from the form had it still been open); is there a way to add a line on to stop the report running? I was thinking the CancelEvent command might be the answer, but not too sure?

EDITED TO ADD: This dialog is used when running various reports, so I cannot name the specific report in the cancel event

 

[pr2-eugin]

I am confused. Where is this code called from? Did you exit the sub? Show us the complete code.

 

[vbaInet]

Why don't you just close the report?

 

[fat controller]

The report will close, but only after showing a further parameter box.

Firstly there is a button, the code for that is:

 DoCmd.RunMacro "OpenDateDialog"
DoCmd.OpenReport "Report_Name", acPreview
DoCmd.RunMacro "CloseDateDialog"

That opens the date select form, user puts in desired dates and clicks OK and then the report runs - no problems.

However, if someone clicks the Cancel button on the DateSelect form, it will close the dialog form, however a further dialog box will appear from the query asking for the information that the query would have picked up from the DateSelect form. The user can of course click cancel on that, and it will then return a blank report, but I was wondering if I could cease the 'Open Report' action that had been carried out previously?

 

[fat controller]

OK, I have gotten a little bit further - I now have a macro 'CancelDateDialog' and if that is pressed, it closes the dialog form and the process is halted............. Unless something has been put into either of the two text boxes on the form - that will then cause the problem described above.

EDIT - I am wrong, it was just taking its time continuing on to the report.

FURTHER EDIT - I have added the following to the 'OnNoData' event:

Private sub Report_NoData(Cancel As Integer)
MsgBox "No data found - closing report"
Cancel = True
End Sub

And I have also added an error handler to the button that kicks off the report:

On Error Resume Next
If Err=2501 Then Err.Clear

Which seems to have helped a bit; the query still puts up the parameter boxes, however clicking the cancel button on them brings the process to an end and does not see the debug window appearing.

 

[vbaInet]

What is this dialog form you speak of? Is it really a form or an Input Box?

 

[fat controller]

What is this dialog form you speak of? Is it really a form or an Input Box?

The dialog form is a form yes - it has two text boxes, both short date format, that allows the user to put in a start date and an end date, clicking OK then opens the report using the dates in the two text boxes (they are referred to in the date criteria of the query)

I have a similar form (contains a more text boxes for other parameters), and on that form the OpenReport action is part of the OnClick event for the OK button, and cancelling it simply closes the form before the OpenReport has happened.

However, as I want this form to be able to do the same action for numerous reports, the OpenReport action is behind the button that the user will click that then opens the smaller form to put the dates in.... hope this makes sense?

 

[vbaInet]

So what you want to do is put some code in the Unload or Close event of the form that will cause the report to close as well.

 

[fat controller]

So what you want to do is put some code in the Unload or Close event of the form that will cause the report to close as well.

Yes The catch is, however, I will not be able to give a specific name to the report to be closed as that will be variable. It will be the only report running at the time however, if that makes any difference?

 

[vbaInet]

You have built the report already. It has a name in the section of your database where all the Tables, Queries, Reports etc are listed. That's the name you'll use to close the report.

You're probably confusing the Caption of a report to its Name.

 

[fat controller]

You have built the report already. It has a name in the section of your database where all the Tables, Queries, Reports etc are listed. That's the name you'll use to close the report.

You're probably confusing the Caption of a report to its Name.

Sorry, I have given you the wrong end of the stick. Let me try and explain more clearly

Lets say that I have Report A, Report B, Report C and Report D, each of which open with their related query; the criteria in each of those queries is the same (between start date and end date, with one other field =True)

I am trying to have it so that there is a form that contains buttons, each relating to the report that it runs (so a button each for Report A, Report B etc).

The idea is that the user will click on their desired button (lets say Report B), and then in turn the following happens

- The Date Selector Form opens
- User puts in start and end dates using date pickers then presses OK
- Report runs taking the start and end date from the Date Selector Form which is open
- Date Selector Form closes

All of the code relating to the above process is in the OnClick event for the report button of their choice (Report B)

So far, that works just fine, and the reports are displaying without issue,

The problem I have is that if the user clicks on the 'Cancel' button, it's code is to close the Date Selector Form - this works in as much that it closes the form, however the report/query continues from the process above and as the Date Selector form has now closed, it puts up Input Boxes asking for the parameters to be entered. If the user clicks on the Cancel button on these Input boxes, it then brings up the VBA error window.

I have been having another go (I might not be knowledgeable, but I am determined ), and have now set the OnClick event for the Cancel button to run a macro called 'CancelDateSelector' - this closes the Date Selector form and then the next step is to 'StopAllMacros' - - this halts the report process, but still leads to the VBA error window showing an error 2501 and three buttons (End, Debug & Help)

I could have a Date Selector form for each report, with the OpenReport command being the OnClick event of the OK button of that particular Date Selector form, but that would be fairly horrendous as there is no less than 138 different reports - hence looking for some sort of generic 'close this window and halt the prior action' so I can use one form for the lot

I'm just wondering - what about if I set the OnClick event to set the textbox values to Null, then allow the remainder of the process to take place as it would if there were dates in the box, then let the null values close the report (as per the error handling above) and then make the last command for the button that kicks off the process (Report B button, if following the example above) to close the DateSelector Form?

 

[vbaInet]

Long explanations make my eyes water fat controller

The problem I have is that if the user clicks on the 'Cancel' button, it's code is to close the Date Selector Form - this works in as much that it closes the form, however the report/query continues from the process above and as the Date Selector form has now closed, it puts up Input Boxes asking for the parameters to be entered. If the user clicks on the Cancel button on these Input boxes, it then brings up the VBA error window.

Basically the form must remain open otherwise where will the report get the values from?

You can however do a couple of things. The easiest being this one:

http://baldyweb.com/wherecondition.htm

OpenForm and OpenReport use the same type of parameters.

 

[fat controller]

Forgive me - I got home with my final thoughts about where the close command should be still banging away in my head, and decided to have a go before I read my emails ; anyway, I appear to have a solution.

The code for the button that kicks off the report now reads:

On Error Resume Next
If Err = 2501 Then Err.Clear
DoCmd.RunMacro "OpenDateDialog"
DoCmd.OpenReport "REPORT_NAME", acPreview
DoCmd.RunMacro "CloseDateDialog"

Then, knowing that the code for the 'OK' button worked perfectly

DoCmd.RunMacro "OK"

I simply changed the code for the 'Cancel' button as follows:

Me.txtStartDate.Value = Null
Me.txtEndDate.Value = Null
DoCmd.RunMacro "OK"

Then, in the report itself, in the OnNoData Event, I added

Private sub Report_NoData(Cancel As Integer)
MsgBox "No data found - closing report"
Cancel = True
End Sub

Private sub Report_NoData(Cancel As Integer) 
MsgBox "No data found - closing report" 
Cancel = True 
End Sub

When the report opens, with the two parameter fields being null in the date selector box, it causes the NoData event, which then throws back to the original button that was pressed.


I am dead chuffed that my wee brain managed to figure a way round it (even if it doesn't have the finesse that other solutions have) - it makes it feel that I am actually (slowly) learning.

Thank you, not only for the advice, but also for not laughing me out of the place when I was having a bash at different things - must be really frustrating when you know what you are talking about, and someone like me comes along with only a half-baked knowledge of what they are trying to do.

 

[vbaInet]

I am dead chuffed that my wee brain managed to figure a way round it (even if it doesn't have the finesse that other solutions have) - it makes it feel that I am actually (slowly) learning.

Thank you, not only for the advice, but also for not laughing me out of the place when I was having a bash at different things - must be really frustrating when you know what you are talking about, and someone like me comes along with only a half-baked knowledge of what they are trying to do.

I'm chuffed that you managed to find some way of doing it too... but I don't think you've done it the completely right way. Can you upload a sample db so I can see exactly how things are set up between the dialog and the report?

 

[fat controller]

I'm chuffed that you managed to find some way of doing it too... but I don't think you've done it the completely right way. Can you upload a sample db so I can see exactly how things are set up between the dialog and the report?

I will try at some point - but not sure if it will work, as its part of a fairly large split db that has its back end lodged on a network server?

 

[vbaInet]

You can create it instead of stripping your existing db. One table, one form, one report with the relevant pieces of code. I just want to see the order of things and how your code is linked in.

 

[fat controller]

OK - bear with me and I will have a bash (probably tomorrow now to be fair - been at it since five this morning)

Thanks again

 

[fat controller]

Apologies - I am on my fifth 16-hour shift, and still haven't had chance; I will come back to this thread and update as soon as I can.

 

[vbaInet]

Apologies - I am on my fifth 16-hour shift, and still haven't had chance; ...

You're a lucky chap!

 

[fat controller]

You're a lucky chap!

I would be if I got paid for all 16 hours - - sadly, I am 'salaried' so they are getting around 9 hours a day free at the moment