Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Don't want report to open (XP)

    Hi,

    I have a report that is based on comments from an input form. The report is fine. What I'd like to do is in instances where there are no comments in the database, I don't want the report to open, but rather have a msgbox pop up telling the user there are no comments at this time. I can set up the msgbox however I'm not sure how to write the code so that if the query behind the report returns no comments, the report doesn't open.

    Thanks,
    Leesha

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Don't want report to open (XP)

    Reports have an "On No Data" event which is used precisely for the purpose you have described. Merely place your msgbox in this event code then immediately close the report (you can even do all this in a macro). If you open this report using Docmd.OPenReport from a form, be sure to have an active error handler in which you can check for (and ignore) the 2501 error.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't want report to open (XP)

    Hi Mark!

    Thanks for the info. I've rarely used events in the report themselves (I think Pat had me do this over a year ago and I'd forgotten it can be done). I'm a bit lost on how to approach it. The report opens via a form and I do use docmd.openreport......................... When I went to the on no data event portion of the report I put in

    If isnull(me.[other comments]) then
    docmd.close
    end if

    I get the following error "this action can't be carried out while processing a form or report event". It's error 2585. I'm not sure exactly what you want me to do or how the write the error handler as you mentioned. I've never actually written on of these on my own and they are only in the code that I generate from wizards and to be honest I was never quite sure as to their purpose! It's a wonder I'm able to creat as much as I do in this program with so little background knowledge! Thank God for the patient souls on this site.

    Thanks,
    Leesha

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

    Re: Don't want report to open (XP)

    If interested here is simple example of standard method I use for this (actual examples are more complicated as there are multiple report options, etc). Add following Report event procedure:

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

    This event triggered if report's RecordSource returns no records. I normally open reports from popup "Report Options" form with following procedures (there's a Preview & Print button on form):

    <code>Private Sub Preview_btn_Click()</code>
    <code> PrintReport acViewPreview</code>
    <code>End Sub</code>
    <code></code>
    <code>Private Sub Print_btn_Click()</code>
    <code> PrintReport acViewNormal</code>
    <code>End Sub</code>

    <code>Private Sub PrintReport(ViewType As AcView)</code>
    <code> On Error GoTo Err_Handler</code>
    <code> </code>
    <code> Dim strRpt As String</code>
    <code> Dim strMsg As String</code>
    <code> </code>
    <code> strRpt = "Report1" </code>
    <code> DoCmd.OpenReport strRpt, ViewType</code>
    <code> </code>
    <code> If ViewType = acViewPreview Then</code>
    <code> Me.Visible = False</code>
    <code> DoCmd.SelectObject acReport, strRpt</code>
    <code> DoCmd.Maximize</code>
    <code> DoCmd.RunCommand acCmdZoom100</code>
    <code> End If</code>
    <code> </code>
    <code>Exit_PrintReport:</code>
    <code> Exit Sub</code>
    <code>Err_Handler:</code>
    <code> If Err = 2501 Then</code>
    <code> Resume Exit_PrintReport</code>
    <code> Else</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> Beep</code>
    <code> MsgBox strMsg, vbInformation, "PRINT REPORT ERROR MSG"</code>
    <code> Resume Exit_PrintReport</code>
    <code> End If</code>
    <code>End Sub</code>

    If report has NoData, Err 2501 (Action Cancelled) error will result; error handler exits sub in such case. Note conditional instructions, if report opened in Print Preview (acViewPreview ) then report is maximized at 100% zoom, and popup form hidden (otherwise will stay on top of report preview screen). The report therefore has a Close event to unhide form when report closed:

    <code>Private Sub Report_Close()</code>
    <code> </code>
    <code> Dim strFrm As String</code>
    <code> strFrm = "Form1"</code>
    <code> </code>
    <code> If IsLoaded(strFrm) Then</code>
    <code> Forms(strFrm).Visible = True</code>
    <code> End If</code>
    <code> </code>
    <code>End Sub</code>

    This uses generic "IsLoaded" function:

    <code>Function IsLoaded(ByVal strForm As String) As Boolean</code>
    <code> On Error GoTo Err_Handler</code>
    <code> Dim ErrMsg As String</code>
    <code> </code>
    <code> IsLoaded = CurrentProject.AllForms(strForm).IsLoaded</code>
    <code> </code>
    <code>Exit_Function:</code>
    <code> Exit Function</code>
    <code>Err_Handler:</code>
    <code> ErrMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> Beep</code>
    <code> MsgBox ErrMsg, vbExclamation, "IS LOADED ERROR"</code>
    <code> Resume Exit_Function</code>
    <code> </code>
    <code>End Function</code>

    You may be able to adapt this for your project. Plz advise if questions.

    HTH

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't want report to open (XP)

    Hi Mark,

    This is "simple"! Thank you so much. I'm now going to go and study it, try to make sense of it and then adapt it to what I need. Fortunately I'm at the beach in NH, supposedly for a few days R&R!!!! I'll be studying tonight! I'm sure to have questions!

    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't want report to open (XP)

    Mark, here's another questions for you. I see that it is in clicking the button on the msgbox that comes up that triggers the other code (at least I think I see that). There will be instances where I don't want a message box, as in times where there are multiple reports openning at one time and I don't want the user to click OK each time there may be a report with no data. In those instances I simply don't want the report to open. Is there a way for this to happen without using a messagebox?

    Leesha

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Don't want report to open (XP)

    Just omit the MsgBox instruction:

    <code>Private Sub Report_NoData(Cancel As Integer)</code>
    <code> Cancel = True</code>
    <code>End Sub</code>

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

    Re: Don't want report to open (XP)

    As Hans noted just comment out or delete MsgBox statement on report NoData event. The MsgBox really doesn't trigger anything, it just halts code execution till user responds to msg. If opening report from form, still need error handler to suppress Err 2501 if no data.

    HTH

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't want report to open (XP)

    Thank you Hans, that worked beautifully. But, as Mark said, since I'm opening the report from a form I am getting that 2501 error message. I've tried variations of error handler code to try to get past it with no luck. This is another new area for me!

    Any suggestions?

    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Don't want report to open (XP)

    Mark's code in <post#=377264>post 377264</post#> shows how to prevent this error from being displayed: his PrintReport procedure traps this error and ignores it. Although I recommend using his code, here is a simplistic example. It assumes a command button cmdShowMyReport on a form that opens a report rptReport in preview mode:

    Private Sub cmdShowMyReport_Click()
    On Error GoTo ErrHandler

    DoCmd.OpenReport "rptMyReport", acViewPreview
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Ignore cancelling the report - do nothing
    Else
    ' Report other errors
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't want report to open (XP)

    Hi Hans,

    I just signed on to report that I kept playing with Mark's code and GOT IT!! I'll save this thread in case I run into problems and therefore have yours as well! As always I'm simply tickled with the possibilities this opens up!

    Thanks,
    Leesha

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't want report to open (XP)

    Hi Hans & Mark,

    Ok here's the next question/situation. I'm using the following code to keep the report from opening and it works fine:

    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptProcessOutcomeClass1AllOfficeReport", acViewPreview

    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Ignore cancelling the report - do nothing
    Else
    ' Report other errors
    MsgBox Err.Description, vbExclamation

    End If


    My problem is that there are a variety of reports that need to be opened from the cmdbutton on the form. Only the reports containing data should open. In the test that I've been doing, the reports with data are not opening. I've tried listing all the reports one after another and that doesn't work, and I've tried doing the above code individually for each report but I get errors. What would you suggest?

    Thanks,
    Leesha

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

    Re: Don't want report to open (XP)

    The sample code you're using should be modified slightly to something like this:

    <code>Private Sub PreviewReport_Click()</code>
    <code> On Error GoTo ErrHandler</code>
    <code></code>
    <code> DoCmd.OpenReport "rptMyReport", acViewPreview</code>
    <code> </code>
    <code>Exit_Proc:</code>
    <code> Exit Sub</code>
    <code></code>
    <code>ErrHandler:</code>
    <code> If Err = 2501 Then</code>
    <code> ' Ignore cancelling the report - do nothing</code>
    <code> Else</code>
    <code> ' Report other errors</code>
    <code> MsgBox Err.Description, vbExclamation</code>
    <code> End If</code>
    <code> Resume Exit_Proc</code>
    <code>End Sub</code>

    Note addition of "Exit_Proc:" label and Resume Exit_Proc statement. Otherwise this code should open the specified report, unless the report name is not spelled correctly. If you are getting errors, unless you can specify when exactly error occurs, error no., error description, etc, then would not be able to determine possible cause. Also, what code is used for reports' NoData event?

    HTH

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Don't want report to open (XP)

    Hi Mark,

    Two things. First so I "understand it", what does the the exit_proc and resume exit_proc stand for / do?

    Second, the code works fine if I am only trying to open one report at a time. If I am trying to open two or more reports (one of which I know contains data) nothing opens. Here is the code as it stands now. BTW, the report names are correct.

    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptProcessOutcomeClass1AllOfficeReport", acViewPreview
    DoCmd.OpenReport "rptProcessOutcomeClass2AllOfficeReport", acViewPreview
    DoCmd.OpenReport "rptProcessOutcomeClass3AllOfficeReport", acViewPreview

    Exit_Proc:
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Ignore cancelling the report - do nothing
    Else
    ' Report other errors
    MsgBox Err.Description, vbExclamation

    End If
    Resume Exit_Proc


    The code on the report in the on no data event is cancel = true

    Thanks!

    Leesha

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Don't want report to open (XP)

    The code as it stands now will quit as soon as one of the reports is canceled. To make it go on to the next report, replace

    Resume Exit_Proc

    by

    Resume Next

    As the "Next" indicates, this means that code execution will continue on the line after the one that caused an error (by being canceled or otherwise).

Page 1 of 2 12 LastLast

Posting Permissions

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