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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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>
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,912
Messages
5,514,125
Members
408,983
Latest member
mlee13

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top