Results 1 to 14 of 14
  1. #1
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45

    Error 2501 displays with Error handling

    Hello,

    I have a report that is filtered by a form. I am getting error 2501 when there are no results to be displayed. I would like the report to not open if there are no results to display. I tried adding error handling but it is not helping. I also added a msgbox to the "No data" event in the report and that prompts, followed by the 2501 error. Here is my code:

    Private Sub cmdGo_Click()

    On Error GoTo Err_Handler
    Dim strWhere
    Dim strDateField
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    strDateField = "[Publication Date]"


    If Me.txtenddate > #1/1/2000# Then
    If Me.txtstartdate > Me.txtenddate Then
    MsgBox ("You must enter a start date that is before the end date")
    Exit Sub
    End If


    End If

    If IsDate(Me.txtstartdate) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.txtstartdate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtenddate) Then
    If strWhere <> vbNullString Then
    strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtenddate + 1, strcJetDate) & ")"
    End If

    If Me!cboStatus = " ALL" Then
    DoCmd.OpenReport "rptPipeline_byState", acViewPreview, , strWhere
    DoCmd.Maximize
    Else
    DoCmd.OpenReport "rptPipeline_byState", acViewPreview, , "St = '" & Forms!frmDialog_for_rptPipeline_byState!cboStatus. Value & "' AND " & strWhere
    DoCmd.Maximize
    End If

    Exit Sub

    Err_Handler:
    Select Case Err
    Case 2501 ' Report canceled
    Case Else ' All other errors
    MsgBox Err.Description, vbExclamation
    End Select

    End Sub

    And for the report:

    Private Sub Report_NoData(Cancel As Integer)

    MsgBox "There are no records for this filter"
    Err.Clear

    End Sub

    I have tested the "Cancel = True/False" function in the NoData report but have not had any luck. Any help will be greatly appreciated!

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    I recommend a different approach. To make the report to not open if there are no results to display - perform a DCount of the report's record source query. (or make a new query that equals the same data as is always in the report and do a DCount of this.)

    Preceeding the Report Open command - perform the DCount vba - and structure with If/then that the Report Open command triggers only if Dcount >0

    Hope this helps.

  3. #3
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Sounds like a good idea also. I had a different solution that I tested today and worked, but I will try the DCOUNT and see what happens.

    The query is dynamic based on the form so I will have to add the SQL into the VBA to check based on the report filter, and then DCOUNT that.

    Update: Actually how can I DCOUNT the report when the report is only filtered based on my docmd.openreport statement with the filter at the end? DCOUNT does not allow me to type in the SQL statement and evaluate it.
    Last edited by agent-; 08-05-2011 at 12:13 PM. Reason: Update error

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Get rid of the Err.Clear on the No Data event as that is conflicting with your error handling on the click event. Clear it at the end of THAT one.

  5. #5
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    My solution was to do this:

    Suspend breaks & resume breaks function in global module.

    I call suspend breaks for the specific form and I check to see if the error is 2501. I replaced that error with a generic error message.

    It seems like an okay approach as long as other errors are not encountered, but that is the only one I have seen so far.

  6. #6
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Quote Originally Posted by boblarson View Post
    Get rid of the Err.Clear on the No Data event as that is conflicting with your error handling on the click event. Clear it at the end of THAT one.
    That works too. Good suggestion thanks Bob!

  7. #7
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    I have a separate question now.

    I also have an Export to Excel function that will open the report, and also open a save as dialog box to export the reports query to excel. If the user clicks Cancel, the error 2501 will also occur, so it is prompting the same error message "Your filter did not have any records to display. Please change your filter."

    Is there anyway to not show it when it is the outputto run time error message and show it when it is the no data found error? It is strange they have the same error #

  8. #8
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You have to handle it in the event that caused it. So the key is figuring out which event is calling the dialog box and handling it there as well.

  9. #9
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    That may be part of the problem. The Export to Excel will also cancel the export/open report IF there are no records, hence 2501 and it will throw out the msg box "Your filter did not have any records to display. Please change your filter." That is good since the user needs to know when the filter is out of the report range. However, this is also going to prompt if they click cancel out of the Excel save as dialog box.

    My only idea is to not have the Export to Excel open the report also. If it only opens the Save as dialog, then I could change the error message to not display, unless you have a workaround.

  10. #10
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You should be able to have the 2501 handling on both events (just don't put Err.Clear on any of them).

  11. #11
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Sorry if I have not been clear.

    It is handling on both events, but it is displaying the same message since they are both err.Number 2501. I would prefer for it to not display anything when the OutputTO is cancelled, but display the msgbox that I created when there is no data in the report.

  12. #12
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    My suggestion -

    In a Standard Module put this in the General Declarations section:
    Code:
    Public blnDisplay2501 As Boolean
    And then in the event for the OutputTo put
    Code:
    blnDisplay2501 = False
    In the click event which opens the report put
    Code:
    blnDisplay2501 = True
    Then in the No Data event of the report put:
    Code:
    If blnDisplay2501 Then
       MsgBox ""There are no records for this filter"
    End If
    blnDisplay2501= False

  13. #13
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    The problem is that there is one event (click) that opens report AND exports to Excel in one event. If I put blndisplay2501 = false or true, wouldn't it will be the same for both?

    Here is another approach that works based on your suggestion:

    I added msgbox to the No data report so that it will always msgbox the error when there is no data

    In addition, I removed the msgbox from the VBA code for the on click procedure so that it does not display anything when the Excel is cancelled. It does not seem to have any problems.

    Thanks for the suggestions. You have been a big help Bob!

  14. #14
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Well, glad you got what you needed. Sometimes it gets hard envisioning exactly what you have and how they best relate. So at least you got where you needed to be.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 07-26-2012, 02:06 PM
  2. Replies: 9
    Last Post: 04-04-2011, 11:13 AM
  3. Error Handling
    By jgelpi16 in forum Programming
    Replies: 4
    Last Post: 09-14-2010, 12:17 PM
  4. Error Handling
    By trb5016 in forum Access
    Replies: 2
    Last Post: 08-10-2010, 08:37 AM
  5. #error handling
    By mws5872 in forum Access
    Replies: 4
    Last Post: 05-12-2010, 07:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums