Error - OpenReport action was canceled!!

[jwr]

I am attempting to Preview Invoice Report from a form where I have a command
button "Preview Invoice" with a click command. Following is a vain attempt
to view this report. When I select the command button, the info pops in a
box stating that it is supposed to Preview the Invoice. "X" off brings up a
box stating that OpenReport Action is action was canceled.



Private Sub cmdPreviewInvoice_Click()
On Error GoTo Err_cmdPreviewInvoice_Click

Dim stDocName As String

stDocName = "Invoice"
DoCmd.OpenReport "Invoice", acViewPreview

Exit_cmdPreviewInvoice_Click:
Exit Sub

Err_cmdPreviewInvoice_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewInvoice_Click

End Sub


Your assistance is greatly appreciated!!
JR

 

[Steve Schapel]

JR,

Well, the 2 lines in your code:
Dim stDocName As String
stDocName = "Invoice"
.... are not achieving anything at all, so you might as well delete them.
But that's not related to the problem. I don't understand where "the
info pops in a box stating that it is supposed to Preview the Invoice"
is coming from. Is this an error message or a message box? Can you
post back with the exact wording? Can you also check that "Invoice" is
the exact name of the report? And also, can you select the Invoice
report in the Database Window, and click Preview, to confirm that the
report actually works?

 

[jwr]

I am changing code info sent to you earlier. I have two databases - both
not working. If I can get this one working it would be much more useful.

Here is the code behind the Preview Invoice Command Button:

Private Sub PreviewInvoice_Click()

On Error GoTo Err_PreviewInvoice_Click

If Forms![Orders by Customer]![Orders by Customer
Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "Enter order information before previewing invoice."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.OpenReport "Invoice", acPreview
End If

Exit_PreviewInvoice_Click:
Exit Sub

Err_PreviewInvoice_Click:
Call Error_Handler(strMod:=CONST_FORMORDERSCUST, _
strProc:="PreviewInvoice_Click")
Resume Exit_PreviewInvoice_Click

End Sub

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
Call Error_Handler(strMod:=CONST_FORMORDERSCUST, _
strProc:="cmdAddRecord_Click")
Resume Exit_cmdAddRecord_Click

End Sub


When I click Preview Invoice, I get message box that says, "There is no
data for this report. Canceling Report. OK." When I click OK, I get the
following error message: "Orders by Customer; PreviewInvoice_Click; 2501;
The OpeReport action was canceled. User:

I have some order entries that I had previously entered and they preview
fine; however, the last few that I have entered have encountered this
problem.

 

[Steve Schapel]

JR,

I would guess that the message box is being generated by code on the No
Data event of the report. The question then is, why does the report
have no data? I assume you need the report to print with the data
associated with the current record on the form? You should check the
query that the report is based on, to see that it is returning the
expected data. We also need to make sure that the newly entered data
has been saved to the table at the time you are trying to print the
report. Apparently you have a form named "Orders by Customers", with a
subform named "Orders by Customer Subform". Where is the PreviewInvoice
Command Button... on one of these forms, or somewhere else?

 

[jwr]

Exactly - the preview button is on the Orders by customer form.

JR,

I would guess that the message box is being generated by code on the No
Data event of the report. The question then is, why does the report
have no data? I assume you need the report to print with the data
associated with the current record on the form? You should check the
query that the report is based on, to see that it is returning the
expected data. We also need to make sure that the newly entered data
has been saved to the table at the time you are trying to print the
report. Apparently you have a form named "Orders by Customers", with a
subform named "Orders by Customer Subform". Where is the PreviewInvoice
Command Button... on one of these forms, or somewhere else?

--
Steve Schapel, Microsoft Access MVP

I am changing code info sent to you earlier. I have two databases - both
not working. If I can get this one working it would be much more useful.

Here is the code behind the Preview Invoice Command Button:

Private Sub PreviewInvoice_Click()

On Error GoTo Err_PreviewInvoice_Click

If Forms![Orders by Customer]![Orders by Customer
Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "Enter order information before previewing invoice."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.OpenReport "Invoice", acPreview
End If

Exit_PreviewInvoice_Click:
Exit Sub

Err_PreviewInvoice_Click:
Call Error_Handler(strMod:=CONST_FORMORDERSCUST, _
strProc:="PreviewInvoice_Click")
Resume Exit_PreviewInvoice_Click

End Sub

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
Call Error_Handler(strMod:=CONST_FORMORDERSCUST, _
strProc:="cmdAddRecord_Click")
Resume Exit_cmdAddRecord_Click

End Sub


When I click Preview Invoice, I get message box that says, "There is no
data for this report. Canceling Report. OK." When I click OK, I get the
following error message: "Orders by Customer; PreviewInvoice_Click; 2501;
The OpeReport action was canceled. User:

I have some order entries that I had previously entered and they preview
fine; however, the last few that I have entered have encountered this
problem.

 

[Steve Schapel]

JR,

Does the report work correctly if you come back later to a record where
it initially produced the error?

 

[jwr]

No, once I have an error, I cannot open the report. Some of my customer
orders preview without any problem. I fail to see what I am doing
differently for it to work on some and not on all.

 

[Steve Schapel]

JR,

Could you post the SQL view of the query that the report is based on?
Hope I'm not barking up the wrong tree here, but at the moment I am
focussed on trying to track down why the report has no data in these
cases. Can you also look at the design view of the report, and check
the code behind the On No Data event property, to confirm that I am
correct in my supposition that this is whare the error message is being
generated? Thanks.

 

[jwr]

SQL VIEW OF QUERY FOR REPORT:

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, ShipTo.ShipName,
ShipTo.Address1, ShipTo.Address2, ShipTo.City, ShipTo.State, ShipTo.ZipCode,
ShipTo.Country, ShipTo.Phone, Orders.ShipDate, Orders.FreightCharge,
Orders.SalesTaxRate, Products.HandlingPct, [Order Details].OrderDetailID,
[Order Details].ProductID, [Order Details].SerialNum, [Order
Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.Country,
Customers.PhoneNumber, Customers.ContactFirstName & " " &
Customers.ContactLastName AS [Contact Name], Dealer.DealerName,
Products.ProductName, Products.ProductCode, [Order Details].LineItem,
Orders.PONumber, BillTo.BillTo, BillTo.Address1, BillTo.Address2,
BillTo.City, BillTo.State, BillTo.Country, BillTo.ZipCode, [Order
Details].Notes, Orders.InvoiceNum
FROM ((Customers INNER JOIN ((Orders INNER JOIN ShipTo ON Orders.ShipToID =
ShipTo.ShipToID) INNER JOIN BillTo ON Orders.BillToID = BillTo.BillToID) ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID) INNER JOIN (Dealer INNER JOIN
OrdersDeliverDealer ON Dealer.DealerID =
OrdersDeliverDealer.DeliverDealerID) ON Orders.OrderID =
OrdersDeliverDealer.OrderID
WHERE (((Orders.OrderID)=[Forms]![Orders by Customer]![Orders by Customer
Subform].[form]![OrderID]))
ORDER BY [Order Details].LineItem, Orders.ShipDate;



CODE BEHIND THE ON DATA EVENT PROPERTY:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
Private Sub Report_Close()
DoCmd.Close acForm, "Print Invoice"
End Sub
Private Sub Report_Open(Cancel As Integer)

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strContract As String
Dim strSQL As String

On Error GoTo Report_Open_Err

DoCmd.Maximize

If Not IsLoaded(CONST_FORMORDERSCUST) Then
MsgBox "Open the Invoice report using the Preview Invoice button on
the Orders by Customer form."
Cancel = True
Else
strSQL = "SELECT Contracts.ContractNum,
OrderContractControl.ControlNumber " & _
"FROM Contracts INNER JOIN OrderContractControl ON
Contracts.ContractID = OrderContractControl.ContractID " & _
"WHERE OrderContractControl.OrderID = " & _
Forms(CONST_FORMORDERSCUST).Controls("Orders by Customer
Subform").Form.Controls("OrderID")
Set con = CurrentProject.Connection
Set rst = con.Execute(CommandText:=strSQL, options:=adCmdText)
If rst.EOF = False Then
Do While rst.EOF = False
strContract = rst.Fields("ContractNum") & vbNullString & _
": " & rst.Fields("ControlNumber") & vbNullString & "; "
rst.MoveNext
Loop
Me.lblContract.Caption = strContract
End If
End If

Report_Open_Exit:
Exit Sub

Report_Open_Err:
Call Error_Handler(strMod:=CONST_RPTINV, _
strProc:="Report_Open")
Cancel = True
Resume Report_Open_Exit

End Sub

Thanks, JR

 

[Steve Schapel]

JR,

I am not in a position to analyse this in detail, without having acces
to your actual data. The report is based on a query with a number of
Inner Joins, which means that if there is no matching data in *any* of
the related tables, the overall output of the query will be no records
returned. My suggestion at this stage would be to identify one of the
customer orders where this problem arose, and manually assess the data.
For example, find the value of the ShipToID field in the Orders table
for the problem order, and check that there exists a record in the
ShipTo table with the same value in the ShipToID field. Same with the
BillToID field in the BillTo table. Check that there is a record in the
OrdersDeliverDealer table with the same value in the OrderID field. And
check that for that OrdersDeliverDealer record, there is a DealerID in
the Dealers table corresponding with the DeliverDealerID in the
OrdersDeliverDealer table. See if this unearths the cause of the problem.

--
Steve Schapel, Microsoft Access MVP

SQL VIEW OF QUERY FOR REPORT:

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, ShipTo.ShipName,
ShipTo.Address1, ShipTo.Address2, ShipTo.City, ShipTo.State, ShipTo.ZipCode,
ShipTo.Country, ShipTo.Phone, Orders.ShipDate, Orders.FreightCharge,
Orders.SalesTaxRate, Products.HandlingPct, [Order Details].OrderDetailID,
[Order Details].ProductID, [Order Details].SerialNum, [Order
Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.Country,
Customers.PhoneNumber, Customers.ContactFirstName & " " &
Customers.ContactLastName AS [Contact Name], Dealer.DealerName,
Products.ProductName, Products.ProductCode, [Order Details].LineItem,
Orders.PONumber, BillTo.BillTo, BillTo.Address1, BillTo.Address2,
BillTo.City, BillTo.State, BillTo.Country, BillTo.ZipCode, [Order
Details].Notes, Orders.InvoiceNum
FROM ((Customers INNER JOIN ((Orders INNER JOIN ShipTo ON Orders.ShipToID =
ShipTo.ShipToID) INNER JOIN BillTo ON Orders.BillToID = BillTo.BillToID) ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID) INNER JOIN (Dealer INNER JOIN
OrdersDeliverDealer ON Dealer.DealerID =
OrdersDeliverDealer.DeliverDealerID) ON Orders.OrderID =
OrdersDeliverDealer.OrderID
WHERE (((Orders.OrderID)=[Forms]![Orders by Customer]![Orders by Customer
Subform].[form]![OrderID]))
ORDER BY [Order Details].LineItem, Orders.ShipDate;



CODE BEHIND THE ON DATA EVENT PROPERTY:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
Private Sub Report_Close()
DoCmd.Close acForm, "Print Invoice"
End Sub
Private Sub Report_Open(Cancel As Integer)

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strContract As String
Dim strSQL As String

On Error GoTo Report_Open_Err

DoCmd.Maximize

If Not IsLoaded(CONST_FORMORDERSCUST) Then
MsgBox "Open the Invoice report using the Preview Invoice button on
the Orders by Customer form."
Cancel = True
Else
strSQL = "SELECT Contracts.ContractNum,
OrderContractControl.ControlNumber " & _
"FROM Contracts INNER JOIN OrderContractControl ON
Contracts.ContractID = OrderContractControl.ContractID " & _
"WHERE OrderContractControl.OrderID = " & _
Forms(CONST_FORMORDERSCUST).Controls("Orders by Customer
Subform").Form.Controls("OrderID")
Set con = CurrentProject.Connection
Set rst = con.Execute(CommandText:=strSQL, options:=adCmdText)
If rst.EOF = False Then
Do While rst.EOF = False
strContract = rst.Fields("ContractNum") & vbNullString & _
": " & rst.Fields("ControlNumber") & vbNullString & "; "
rst.MoveNext
Loop
Me.lblContract.Caption = strContract
End If
End If

Report_Open_Exit:
Exit Sub

Report_Open_Err:
Call Error_Handler(strMod:=CONST_RPTINV, _
strProc:="Report_Open")
Cancel = True
Resume Report_Open_Exit

End Sub

Thanks, JR