Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    the report shows error if there are no sales (Access 2000)

    I have a report that has two fields with sums for sales in the reporrt header and the report footer.
    These sums are put in the ControlSource of the controls as follows:
    =Sum([liters]).
    The name of the first control is called [liters Grand Total Sum]
    When there are sales, it is OK, but if there are no sales, then the report shows the words /= error
    which is ugly. What can i do not to open the report at all, when there are no sales and these fields are

    blank? And also to have the following message " This client has no sales yet !"

    I have made the following code in the OnOnOpen event of the report:


    If Me![liters Grand Total Sum] = "" Then
    MsgBox " This client has no sales yet! "
    Exit Sub
    End If

    However i receive the following response:

    " you entered an expression that has no value"


    How can i overcome this problem ?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: the report shows error if there are no sales (Access 2000)

    The error "you entered an expression that has no value" indicates there are no records & therefore there is no value that can be evaluated by expression. Recommend use Report NoData event to handle this situation. Example:

    Private Sub Report_NoData(Cancel As Integer)
    Beep
    MsgBox "No records exist for the criteria you specified.", vbCritical, "NEGATIVE REPORT"
    Cancel = True
    Forms!<!t>[MyForm]<!/t>.Visible = True
    End Sub

    Assuming you are opening report from a form, you need to add error handling to sub that opens report or if report cancelled you will get an annoying msgbox saying something like, the OpenReport action was cancelled, etc (Err 2501). Example of sub that opens report:

    Private Sub PrintReportTest(strRpt As String, intPrintOpt As Integer)
    On Error GoTo Err_Handler

    ' strRpt = name of report
    ' intPrintOpt 0 = Print, 2 = Preview
    Dim strErrMsg As String

    ' Hide form if print preview
    If intPrintOpt = 2 Then
    Me.Visible = False
    End If

    DoCmd.OpenReport strRpt, intPrintOpt

    Exit_PrintReport:
    Exit Sub
    Err_Handler:
    If Err = 2501 Then ' Action cancelled
    Resume Next
    Else
    strErrMsg = "Error No " & Err.Number & ": " & Err.DESCRIPTION
    Beep
    MsgBox strErrMsg, vbExclamation, "PRINT REPORT ERROR"
    End If
    Resume Exit_PrintReport

    End Sub

    In above example, pop-up form is used to select report options & open report. Form is hidden if report opened in print preview. When report closed or cancelled, report NoData or Close event makes pop-up form visible again so user can run another report if desired.

    HTH

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: the report shows error if there are no sales (Access 2000)

    Thank you very much for your advice.It works, but as you said, the message ":The Open report action was cancelled" appears
    and is annoying.You have advised me to put the ErrorHandler, i have tried but could not succeed.Will you be so kind as to add on the ErrorHanlder
    to my code below:

    Private Sub Report_NoData(Cancel As Integer)
    Beep
    MsgBox "No records exist for the criteria you specified.", vbCritical, "NEGATIVE REPORT"
    Cancel = True
    Exit Sub
    End Sub

    I will be so grateful for you help

    Best regards

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: the report shows error if there are no sales (Access 2000)

    You put the error handling in the routine that calls the report. you can't suppress that error message from within the report itself.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: the report shows error if there are no sales (Access 2000)

    Look at Mark's PrintReportTest code and you will see the error handling (this should be with the same code as your DoCmd.OpenReport command), starting with:

    On Error GoTo Err_Handler

    At the end of this code you will see the rest of the error handling, viz:

    Exit_PrintReport:
    Exit Sub
    Err_Handler:
    If Err = 2501 Then ' Action cancelled
    Resume Next
    Else
    strErrMsg = "Error No " & Err.Number & ": " & Err.DESCRIPTION
    Beep
    MsgBox strErrMsg, vbExclamation, "PRINT REPORT ERROR"
    End If
    Resume Exit_PrintReport

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •