Report w/ Query as Datasource

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • chris
    #1

    Report w/ Query as Datasource

    I'm trying to set-up a message box when my report is open that will
    say there is no data for the report if there are no records in the
    query. I've been able to do this for a form, but it will not work
    with my report. Here is the code I use on the Open Event of a form:

    If Me.RecordsetClo ne.RecordCount = 0 Then
    MsgBox "There are no Open SCAR's", vbInformation
    DoCmd.Close
    Exit Sub
    Else
    DoCmd.RunMacro "max"
    End If

    What do I need to do for this to work on a report?

    Thank you!

    Chris

    Tags: None
    • Allen Browne
      #2
      Re: Report w/ Query as Datasource

      Just cancel the NoData event of the report.

      --
      Allen Browne - Microsoft MVP. Perth, Western Australia
      Tips for Access users - http://allenbrowne.com/tips.html
      Reply to group, rather than allenbrowne at mvps dot org.

      "chris" <chrisvettese@y ahoo.comwrote in message
      news:1170259939 .319889.8270@k7 8g2000cwa.googl egroups.com...
      I'm trying to set-up a message box when my report is open that will
      say there is no data for the report if there are no records in the
      query. I've been able to do this for a form, but it will not work
      with my report. Here is the code I use on the Open Event of a form:
      >
      If Me.RecordsetClo ne.RecordCount = 0 Then
      MsgBox "There are no Open SCAR's", vbInformation
      DoCmd.Close
      Exit Sub
      Else
      DoCmd.RunMacro "max"
      End If
      >
      What do I need to do for this to work on a report?

        Comment

        • chris
          #3
          Re: Report w/ Query as Datasource

          Thank you! I actually didn't see that Event. I added the message box
          code and close command to the event. The trouble I have now is that I
          get an error telling me that I can't close the report because I have
          code running under the detail section on the On Format Event. How can
          I skip or cancel this code if there is no data?


          On Jan 31, 11:23 am, "Allen Browne" <AllenBro...@Se eSig.Invalid>
          wrote:
          Just cancel the NoData event of the report.
          >
          --
          Allen Browne - Microsoft MVP. Perth, Western Australia
          Tips for Access users -http://allenbrowne.com/tips.html
          Reply to group, rather than allenbrowne at mvps dot org.
          >
          "chris" <chrisvett...@y ahoo.comwrote in message
          >
          news:1170259939 .319889.8270@k7 8g2000cwa.googl egroups.com...
          >
          >
          >
          I'm trying to set-up a message box when my report is open that will
          say there is no data for the report if there are no records in the
          query. I've been able to do this for a form, but it will not work
          with my report. Here is the code I use on the Open Event of a form:
          >
          If Me.RecordsetClo ne.RecordCount = 0 Then
          MsgBox "There are no Open SCAR's", vbInformation
          DoCmd.Close
          Exit Sub
          Else
          DoCmd.RunMacro "max"
          End If
          >
          What do I need to do for this to work on a report?- Hide quoted text -
          >
          - Show quoted text -

            Comment

            • Tim Marshall
              #4
              Re: Report w/ Query as Datasource

              chris wrote:
              If Me.RecordsetClo ne.RecordCount = 0 Then
              IMO, much better to use:

              if Me.RecordsetClo ne.eof then
              What do I need to do for this to work on a report?
              Use the "On No Data Event" on the report properties.

              --
              Tim http://www.ucs.mun.ca/~tmarshal/
              ^o<
              /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
              /^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

                Comment

                • chris
                  #5
                  Re: Report w/ Query as Datasource

                  RecordsetClone is not working with the report

                  On Jan 31, 11:25 am, Tim Marshall
                  <TIM...@PurpleP andaChasers.Moe rtheriumwrote:
                  chris wrote:
                  If Me.RecordsetClo ne.RecordCount = 0 Then
                  >
                  IMO, much better to use:
                  >
                  if Me.RecordsetClo ne.eof then
                  >
                  What do I need to do for this to work on a report?
                  >
                  Use the "On No Data Event" on the report properties.
                  >
                  --
                  Tim http://www.ucs.mun.ca/~tmarshal/
                  ^o<
                  /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
                  /^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

                    Comment

                    • Tim Marshall
                      #6
                      Re: Report w/ Query as Datasource

                      chris wrote:
                      Thank you! I actually didn't see that Event. I added the message box
                      code and close command to the event.
                      Do this instead of the close command:

                      Private Sub Report_NoData(C ancel As Integer)

                      MsgBox "The criteria you have selected has yielded no results."

                      Cancel = True

                      End Sub

                      --
                      Tim http://www.ucs.mun.ca/~tmarshal/
                      ^o<
                      /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
                      /^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

                        Comment

                        • Tim Marshall
                          #7
                          Re: Report w/ Query as Datasource

                          chris wrote:
                          RecordsetClone is not working with the report
                          I did not recommend using it. I should have made it clearer my
                          preferred method was to do with forms, sorry.

                          See my response after your post following Allen's response.
                          --
                          Tim http://www.ucs.mun.ca/~tmarshal/
                          ^o<
                          /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
                          /^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

                            Comment

                            • chris
                              #8
                              Re: Report w/ Query as Datasource

                              Your previous message worked great. Thank you!


                              On Jan 31, 1:12 pm, Tim Marshall
                              <TIM...@PurpleP andaChasers.Moe rtheriumwrote:
                              chris wrote:
                              RecordsetClone is not working with the report
                              >
                              I did not recommend using it. I should have made it clearer my
                              preferred method was to do with forms, sorry.
                              >
                              See my response after your post following Allen's response.
                              --
                              Tim http://www.ucs.mun.ca/~tmarshal/
                              ^o<
                              /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
                              /^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

                                Comment

                                Previous template Next
                                Working...