Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: force subreport to appear

    Is there any way to force a subreport to appear even though it is based on a query that doesn't provide any results? I just want the Header and Footer to appear. The footer in this case would have a sum function that I would want to appear as 0.00.

  2. #2
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15

    Re: force subreport to appear

    in the report's Open event see if the underlying recordsource has any records:


    (assuming the underlying recordsource's name is "MyQuery")

    Private Sub Report_Open(Cancel As Integer)

    set db = currentdb()
    set rs1 = db.openrecordset("MyQuery")
    if rs1.recordcount = 0 then

    'set the visible property of the sections of the report you don't want displayed to false
    me![section1].visible = false
    me![section2].visible = false
    me![detail].visible = false

    'and plug in a value to the total control
    me![total].ControlSource = "='Total = $0.00' "

    else

    '. . . or set the visible property of the sections of the report you don't want displayed to true
    me![section1].visible = true
    me![section2].visible = true
    me![detail].visible = true

    'set the control source of the control that displays the total
    me![total].ControlSource = "=dsum(amt, myquery)"


    end if

    rs1.close


    End Sub

  3. #3
    Join Date
    Oct 2003
    Posts
    8
    Thanks very much for your help

    I might be a bit out of my depth but by my question you'll probably know for sure. When I try to open subreport with Visual Basic I get error 3061 Too few parameters: Expected 2
    I don't really know what I did wrong.

  4. #4
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15
    Originally posted by roller
    Thanks very much for your help

    I might be a bit out of my depth but by my question you'll probably know for sure. When I try to open subreport with Visual Basic I get error 3061 Too few parameters: Expected 2
    I don't really know what I did wrong.
    you don't need to do anything with the subreport. the code example I gave you should be in the report's code module.

    Try this: open the report in design view, then select view-->properties to open the properties window. Make sure it's the report's properties you're looking at. Find the OnOpen property and click the the " . . . " button to the right (if a box comes up asking you what to do, select "Code Builder"). THis will take you into the Visual Basic editor, where you'd put that code I gave you.


    as far as that error goes, it means there's something wrong with the underlying query.

  5. #5
    Join Date
    Oct 2003
    Posts
    8
    Thanks again,

    The query is based on values in a form and as such the code seems to have difficulty with it (this time I put it in the right place, sorry). I figured I can just use labels instead of subreport headers and then refer to the subreports Total (made invisible in supreport) as a text box in the details area of the main report. Would this be an easier problem to solve as instead of #error where no result is available 0.00 could somehow replace it? Or am I still going to get caught because the query is still not in a usable form?

  6. #6
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15
    Originally posted by roller
    Thanks again,

    The query is based on values in a form and as such the code seems to have difficulty with it (this time I put it in the right place, sorry). I figured I can just use labels instead of subreport headers and then refer to the subreports Total (made invisible in supreport) as a text box in the details area of the main report. Would this be an easier problem to solve as instead of #error where no result is available 0.00 could somehow replace it? Or am I still going to get caught because the query is still not in a usable form?
    I was assuming the header/footer you were talking about was the main report's header/footer.

    Anyway I think I need to know more about your report before I can give any more advice. Are you launching the report from the form you're referring to? And is the query you're talking about in the "Query" objects in the database window?

  7. #7
    Join Date
    Oct 2003
    Posts
    8
    Yes to both questions and one of the criteria for the reports is based on two values that are on the form.

  8. #8
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15
    Originally posted by roller
    Yes to both questions and one of the criteria for the reports is based on two values that are on the form.
    Gotcha. It sounds like you need to have a textbox (or label) somewhere in the main report to display the results of the query. Again, I'm assuming that this textbox displays the sum of some field in the query. So you might want to remove that textbox/label from the subreport and put it in the main report.

    let's assume it's a textbox, and call it MyTextbox, and let's assume that you're summing one field in MyQuery

    ------------------------------------

    Private Sub Report_Open(Cancel As Integer)

    set db = currentdb()
    set rs1 = db.openrecordset("MyQuery")
    if rs1.recordcount = 0 then

    'set the visible property of the subreport to false
    me![subreport].visible = false
    'note that you can set the format property of the textbox to whatever (euro, dollar, etc)
    me![mytextbox] = 0

    else

    me![subreport].visible = true
    me![mytextbox] = dsum(rs1(field1))


    end if

    rs1.close


    End Sub

    ------------------------------------

    Hope this helps!

Posting Permissions

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