Cancel Report based on user input in OnOpen event 1

[craigglenn]

I have searched and labored over this simple task. I am having trouble getting out of a report if the user wants to cancel the report based on clicking cancel. Here is a look at the onopen event code. thanks in advance for your input.

Option Compare Database

Private Sub Report_NoData(cancel As Integer)

MsgBox ("No records found for specified route.&quot
cancel = True

End Sub

Private Sub Report_Open(cancel As Integer)

Dim routeN As Integer
Dim rday As Integer
Dim theQuery As String
Dim dayField As String


GetRouteDay rday 'prompt for route day
If rday = 0 Then
DoCmd.CancelEvent 'this does not work!!!
End If

GetRouteNum routeN 'prompt for route number

Me.RNUM.ControlSource = "=" & routeN & "" 'Set the route Number on Report

Select Case rday
Case 1
dayField = "PropSwpMonday"
Me.OrderBy = "[PropPriorityMonday]"
Me.PropRouteNum.ControlSource = "=""Monday"""
Case 2
dayField = "PropSwpTuesday"
Me.OrderBy = "[PropPriorityTuesday]"
Me.PropRouteNum.ControlSource = "=""Thuesday"""
Case 3
dayField = "PropSwpWednesday"
Me.OrderBy = "[PropPriorityWednesday]"
Me.PropRouteNum.ControlSource = "=""Wednesday"""
Case 4
dayField = "PropSwpThursday"
Me.OrderBy = "[PropPriorityThursday]"
Me.PropRouteNum.ControlSource = "=""Thursday"""
Case 5
dayField = "PropSwpFriday"
Me.OrderBy = "[PropPriorityFriday]"
Me.PropRouteNum.ControlSource = "=""Friday"""
Case 6
dayField = "PropSwpSaturday"
Me.OrderBy = "[PropPrioritySaturday]"
Me.PropRouteNum.ControlSource = "=""Saturday"""
Case 7
dayField = "PropSwpSunday"
Me.OrderBy = "[PropPrioritySunday]"
Me.PropRouteNum.ControlSource = "=""Sunday"""
End Select
Me.OrderByOn = True

theQuery = "Select * From mainProperty Where " & dayField & "=" & routeN & ";"
Me.RecordSource = theQuery

End Sub

Private Function GetRouteNum(routeN) As Integer

On Error GoTo ErrorHandler
Dim message, title
message = "Enter Route Number:"
title = "Route Number"
routeN = InputBox(message, title)

Exit Function
ErrorHandler:
Select Case Err.Number
Case 13
MsgBox ("You must enter a valid route number&quot
Case Else
MsgBox ("You must enter a valid route number&quot
End Select
Resume


End Function


Private Function GetRouteDay(rday) As Integer

On Error GoTo ErrorHandler
Dim message, title, default
message = "Enter number for day of route:" + (Chr(13)) + "1. Monday" + (Chr(13)) + "2. Tuesday" + (Chr(13)) + "3. Wednesday" + (Chr(13)) + "4. Thursday" + (Chr(13)) + "5. Friday" + (Chr(13)) + "6. Saturday" + (Chr(13)) + "7. Sunday"
title = "Route day"
default = "1"

InvalidDay:

rday = InputBox(message, title, default)

Select Case rday
Case "1", "2", "3", "4", "5", "6", "7"
Exit Function
Case Else
MsgBox ("You must enter a valid Day Number&quot
GoTo InvalidDay
End Select

ErrorHandler:
Dim response As String

Select Case Err.Number
Case 13
response = MsgBox("Cancel Report", vbYesNo)
If response = vbYes Then
rday = 0
Exit Function
End If
Case Else
MsgBox ("You must enter a valid day&quot
End Select
Resume

End Function

 

[dhookom]

I'm not sure about your methodology but you could try:
Private Sub Report_Open(cancel As Integer)

Dim routeN As Integer
Dim rday As Integer
Dim theQuery As String
Dim dayField As String


GetRouteDay rday 'prompt for route day
Cancel = (rday = 0)


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JohnLowell]

In the Report_Open event, change this

If rday = 0 Then
DoCmd.CancelEvent 'this does not work!!!
End If

to this

If rday = 0 Then
Cancel = True'this does not work!!!
Exit Sub
End If

 

[dhookom]

I just love it when someone states "this does not work!!!" and doesn't take the time to suggest:
1) what makes them think it doesn't work
2) the full extent and context of what they are doing
3) what, if any error message they get
4) what other solution they might have tried

I did create a report that had the following code in the on open event:
[blue]

Private Sub Report_Open(Cancel As Integer)
    Dim intNum As Integer
    intNum = InputBox("Enter number or 0 to cancel", "Enter Number", 0)
    Cancel = intNum = 0
End Sub

[/blue]
The report attempted to open and prompted with the input box. If I entered 0 or left the default value to 0, then the report closed without any errors.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[craigglenn]

John,

That was right on the money! Thanks for your help.

Craig

 

[craigglenn]

I just love it when someone talks at you rather than to you.
1. it's counter productive
2. it's insulting
3. it's just rude.

Duane,

I would like to thank you for your helpful post in the past. I was pretty frustrated when I posted this and was wrong not to give more detail.

The problem was that it would not close the report from the onopen event. because there are fields on the report, when the report executed it would prompt me for values of those fields. My purpose was the give the user a chance to exit the report with out further input. John's solution work for me.

If you would like to know more, Please let me know. I value your help as well as everyone one else's.

Thanks,
Craig
NOT A PROGRAMER
just a guy looking for a little help.

 

[dhookom]

Excuse me for my reply. I was referring to John's response. Since he basically duplicated my response and left the "'this does not work!!!" in his code, I assumed that John was implying that my suggested code did not work.

I think your question was adequate for getting a response. After all, you did get two responses that were almost exactly the same.

Also, I prefer to get all user input prior to opening the report. This allows you to use combo boxes etc. to get user input for the report parameters. Also, using the Order By property of a report is meaningless if the report has used any sorting and grouping levels. Allen Browne has a good page on how to set the sorting levels using code

http://members.iinet.net.au/~allenbrowne/ser-33.html.

Again, I am sorry for the confusion. My message was directed to a perception that John was suggesting that my reply didn't work and wasn't stating his reasons.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JohnLowell]

Duane,
I'm sorry for any confusion. I didn't even see your reply until after I had posted my response.

Group Hug.


John

 

[dhookom]

John,
I understand. I know that posts will be near simultaneous since it takes time to read, compose a response, (test), preview, and post.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[craigglenn]

OK Friends. Thanks again.

I will also try to be more detailed in the future.

Craig