OpenReport action canceled?

[jbphoenix]

Ok - I'm stumped. I've been assigned a task of having a query export to excel. Not a problem but the button has to on a form someone before me created. There are 2 btns on the form. The first one (Preview) opens the report. The second one I created (Command7). Command7 runs a macro that exports the query to excel. Ok now my problem - When I click my btn (Command7) the query is exported to excel. Back in access the form closes but then a message box pops up saying "The OpenReport action was canceled." I can't figure out why that message is poping up. Here is the code for the form. The blue is the original code from the previous person the rest is what I added.

Option Compare Database
Private Sub Ending_Order_Date_Exit(Cancel As Integer)
[WD] = Weekday([Ending Order Date])
[Day2] = IIf([WD] = 6, [Ending Order Date] + 3, IIf([WD] = 7, [Ending Order Date] + 2, [Ending Order Date] + 1))
[Day3] = IIf([WD] = 5, [Ending Order Date] + 4, IIf([WD] = 6, [Ending Order Date] + 4, IIf([WD] = 7, [Ending Order Date] + 3, [Ending Order Date] + 2)))
[Day4] = IIf([WD] = 4, [Ending Order Date] + 5, IIf([WD] = 5, [Ending Order Date] + 5, IIf([WD] = 6, [Ending Order Date] + 5, IIf([WD] = 7, [Ending Order Date] + 4, [Ending Order Date] + 3))))
[Day5] = IIf([WD] = 3, [Ending Order Date] + 6, IIf([WD] = 4, [Ending Order Date] + 6, IIf([WD] = 5, [Ending Order Date] + 6, IIf([WD] = 6, [Ending Order Date] + 6, IIf([WD] = 7, [Ending Order Date] + 5, [Ending Order Date] + 4)))))
[Day6] = IIf([WD] = 2, [Ending Order Date] + 7, IIf([WD] = 3, [Ending Order Date] + 7, IIf([WD] = 4, [Ending Order Date] + 7, IIf([WD] = 5, [Ending Order Date] + 7, IIf([WD] = 6, [Ending Order Date] + 7, IIf([WD] = 7, [Ending Order Date] + 6, [Ending Order Date] + 5))))))
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Forms![Backlog by Part Date].OpenArgs
End Sub
Private Sub Preview_Click()
[WD] = Weekday([Ending Order Date])
[Day2] = IIf([WD] = 6, [Ending Order Date] + 3, IIf([WD] = 7, [Ending Order Date] + 2, [Ending Order Date] + 1))
[Day3] = IIf([WD] = 5, [Ending Order Date] + 4, IIf([WD] = 6, [Ending Order Date] + 4, IIf([WD] = 7, [Ending Order Date] + 3, [Ending Order Date] + 2)))
[Day4] = IIf([WD] = 4, [Ending Order Date] + 5, IIf([WD] = 5, [Ending Order Date] + 5, IIf([WD] = 6, [Ending Order Date] + 5, IIf([WD] = 7, [Ending Order Date] + 4, [Ending Order Date] + 3))))
[Day5] = IIf([WD] = 3, [Ending Order Date] + 6, IIf([WD] = 4, [Ending Order Date] + 6, IIf([WD] = 5, [Ending Order Date] + 6, IIf([WD] = 6, [Ending Order Date] + 6, IIf([WD] = 7, [Ending Order Date] + 5, [Ending Order Date] + 4)))))
[Day6] = IIf([WD] = 2, [Ending Order Date] + 7, IIf([WD] = 3, [Ending Order Date] + 7, IIf([WD] = 4, [Ending Order Date] + 7, IIf([WD] = 5, [Ending Order Date] + 7, IIf([WD] = 6, [Ending Order Date] + 7, IIf([WD] = 7, [Ending Order Date] + 6, [Ending Order Date] + 5))))))

If IsNull([Beginning Order Date]) Then
MsgBox "You must enter a date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End Sub

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

[WD] = Weekday([Ending Order Date])
[Day2] = IIf([WD] = 6, [Ending Order Date] + 3, IIf([WD] = 7, [Ending Order Date] + 2, [Ending Order Date] + 1))
[Day3] = IIf([WD] = 5, [Ending Order Date] + 4, IIf([WD] = 6, [Ending Order Date] + 4, IIf([WD] = 7, [Ending Order Date] + 3, [Ending Order Date] + 2)))
[Day4] = IIf([WD] = 4, [Ending Order Date] + 5, IIf([WD] = 5, [Ending Order Date] + 5, IIf([WD] = 6, [Ending Order Date] + 5, IIf([WD] = 7, [Ending Order Date] + 4, [Ending Order Date] + 3))))
[Day5] = IIf([WD] = 3, [Ending Order Date] + 6, IIf([WD] = 4, [Ending Order Date] + 6, IIf([WD] = 5, [Ending Order Date] + 6, IIf([WD] = 6, [Ending Order Date] + 6, IIf([WD] = 7, [Ending Order Date] + 5, [Ending Order Date] + 4)))))
[Day6] = IIf([WD] = 2, [Ending Order Date] + 7, IIf([WD] = 3, [Ending Order Date] + 7, IIf([WD] = 4, [Ending Order Date] + 7, IIf([WD] = 5, [Ending Order Date] + 7, IIf([WD] = 6, [Ending Order Date] + 7, IIf([WD] = 7, [Ending Order Date] + 6, [Ending Order Date] + 5))))))



stDocName = "Macro1"
DoCmd.RunMacro stDocName
DoCmd.Close acForm, "Backlog By Part Date"


Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If
Resume Exit_Command7_Click



End Sub

 

[boblarson]

What is Macro1? It may be that you need to move that into VBA to be able to trap for that error.

 

[jbphoenix]

Macro1 outputs the query to excel. Can I move that into VBA? I've never tried that.

 

[boblarson]

Macro1 outputs the query to excel. Can I move that into VBA? I've never tried that.

Oh, definitely. Actually, you can give this a shot to save time and typing:

In the main database window go to

TOOLS > MACRO > CONVERT MACROS TO VISUAL BASIC

And let it do it for you, although their syntax at times leaves something to be desired. But, it should be a start.

 

[jbphoenix]

Awesome thanks Bob for pointing me in the right direction. I got it all figured out now.