Run-time error '2501': The OpenReport action was cancelled

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
I have an option group on my form with two options. I have a combo box where the user can select the employee whose details they want to see based on either option from the option group.

There is a commnad button to preview the reports. I can either select an employee to see that employee's records or not select an employee and view all the records. The OnNoData event of the two reports which can be opened from the option group are set to: docmd.CancelEvent.

I have copied some common form error codes as part of a module as below:
Public Const errCancel As Long = 2501
Public Const errCancel2 As Long = 2001

If I select an employee who has records, the report is opened. If I select an employee who does not have any records I get Run-time error '2501': The OpenReport action was cancelled.

The code that I have in the On_Click event of the command button is below.

Rich (BB code):
Private Sub cmdPreviewClients_Click()
Dim varWhere As Variant, strReport As String

On Error GoTo Err_cmdPreviewClients_Click
    
    varWhere = Null
   
    If Not IsNothing(Me.cboEmployee) Then
    varWhere = "[EmployeeName] LIKE '" & Me.cboEmployee & "*'"
    End If
    
    If IsNothing(varWhere) Then
    If vbNo = MsgBox("You did not select an Employee." & vbCrLf & vbCrLf & "Do you want to print the report for all clients?", _
    vbYesNo + vbQuestion + vbDefaultButton2) Then
    Exit Sub
    Else
    
    varWhere = "1 = 1"
    End If
    End If
    
    If Me.optReports = 1 Then
    strReport = "Clients by Employee"
    Else
    strReport = "Client Meeting this Month by Employee"
    End If
    
    DoCmd.OpenReport strReport, acViewPreview, WhereCondition:=varWhere    DoCmd.Maximize
    DoCmd.RunCommand acCmdZoom100
    DoCmd.SelectObject acReport, strReport

Exit_cmdPreviewClients_Click:
    Exit Sub

Err_cmdPreviewClients_Click:
    If (Err = errCancel2) Then
    MsgBox "There are no clients assigned to " & cboEmployee & ".  The report was cancelled.", , "Client Management: Report Cancelled"
    Else
    MsgBox "There are no meetings assigned to " & cboEmploee & ".  The report was cancelled.", , " Client Management: Report Cancelled"
    End If
    Resume Exit_cmdPreviewClients_Click
    
End Sub

When I debug, the line of the code that I have in bold typeface is the line that is highlighted.

I have been at it for a while but I cannot figure out what is wrong. Please help.

Thanks in advance.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Michelle,

You'll probably want to kick something...
The WhereCondition won't work with a simple filter; you need to specify a complete query (usually a saved query).
Change the code to use the Filter instead, with the same filter string.

Denis
 

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
Hi Denis,

Thanks for replying.

Can you be a bit more specific. I do not follow.

Thanks
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Looks like I answered too fast. I tried a reply last night and my internet connection went down...

I'd suggest a couple of things.

1. The filter variable should be a string, not a variant. Use

Dim strWhere As String

instead, replace references to varWhere through the procedure, and also initialize it with

strWhere = ""

2. Does the combo have a numeric key field (eg, EmployeeID ?). If it does, the filter won't work in its current form. Instead of


varWhere = "[EmployeeName] LIKE '" & Me.cboEmployee & "*'"

use

strWhere = "[EmployeeName] =" & Me.cboEmployee

Denis
</pre>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,916
Messages
5,834,363
Members
430,279
Latest member
mkasledge1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top