Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Limiting report contents based on subreport (XP)

    I have a report based on a query that combines broad Goals from tblGoals and specific Objectives from tblObjectives. Even more specific info is found in sister queries based on tblActions and tblIndicators. The main report has two subreports--one based on Actions, one based on Indicators. The Actions and Indicators are selective; the corresponding queries produce only the desired records. Problem is some Goals and Objectives (in main report) have no related Actions or Indicators in subreport, and I'd like those not to appear in the report. Is there a way to eliminate those Goals and Objectives that do not have a match in either subreport?
    Fields include:
    tblGoals - goalname, goalnumber
    tblObjectives - goalnumber, objectivenumber
    tblActions - goalnumber, objectivenumber, actionnumber
    tblIndicators - goalnumber, objectivenumber, indicator number
    queries link tables via the number fields shown above

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Limiting report contents based on subreport (XP)

    You can use the HasData property of the subreport to set the Visible property of the headings to false when needed.

    Put the code in the OnFormat event of the Report section (Detail I imagine) that contains the relevant controls.

    If me.Subreportcontrol.Report.HasData then
    me.labelsomething.visible =true
    else
    me.labelsomething.visible =false
    end if

    subreportontrol is the name of the subreport as it appears on the main report (which may not be the actual name of the report.)
    Labelsomething is the label you want to show/hide.
    Regards
    John



  3. #3
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Limiting report contents based on subreport (XP)

    That worked, thanks. Now I want to eliminate as much white space as possible in the Detail (Objectives) and GroupHeader0 (Goals) sections. I tried the following code without success.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If (Me.SUBREPactions2.Report.HasData) Or (Me.SUBREPORTindicators.Report.HasData) Then
    Me.txtObjNameLong.Visible = True 'SUBREPactions2
    Me.lblObj.Visible = True
    Me.txtObjNumber.Visible = True
    Me.Detail.Height = 0.3417

    Else
    Me.txtObjNameLong.Visible = False
    Me.lblObj.Visible = False
    Me.txtObjNumber.Visible = False
    Me.Detail.Height = 0.2

    End If

    End Sub

    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

    If (Me.SUBREPactions2.Report.HasData) Or (Me.SUBREPORTindicators.Report.HasData) Then

    Me.lblGoal.Visible = True
    Me.txtGoalNameLong.Visible = True
    Me.txtGoalNumber.Visible = True
    Me.GroupHeader0.Height = 1.4167
    Else

    Me.lblGoal.Visible = False
    Me.txtGoalNameLong.Visible = False
    Me.txtGoalNumber.Visible = False
    Me.GroupHeader0.Height = 0.02

    End If

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Limiting report contents based on subreport (XP)

    Have not tested this, but have you tried just setting the CanShrink properties of all the relevant bits to Yes.
    Regards
    John



  5. #5
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Limiting report contents based on subreport (XP)

    I had not changed CanShrink properties, but after doing so for GroupHeader0 and Detail, there is no change--still a lot of white space. One other unexpected result--Goal-related fields and label located in the GroupHeader0 section do not appear except for the first one.

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

    Re: Limiting report contents based on subreport (XP)

    Have you also set the Can Shrink property of the relevant controls in those sections to Yes?

  7. #7
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Limiting report contents based on subreport (XP)

    I had not. However, I have now shrunk everything in sight in report and subreports. I also changed Group property for Keep Together to With First Detail (had been Whole Group on subreports). However, results unchanged: still getting huge white space, and goal fields--other than Goal 1--still do not show.

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

    Re: Limiting report contents based on subreport (XP)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Limiting report contents based on subreport (XP)

    File attached
    Attached Files Attached Files

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

    Re: Limiting report contents based on subreport (XP)

    Thanks. In the attached version, I took a slightly different approach: instead of making controls visible/invisible, I suppress the entire section if there are no data. For the header section, you can't just look at the subreports, for that applies to the first detail section only. I counted the number of relevant records instead:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = Not ((Me.SUBREPactions2.Report.HasData) Or _
    (Me.SUBREPORTindicators.Report.HasData))
    End Sub

    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = DCount("*", "qry_GoalsObjsIndsURQRM", "GoalNumber=" & _
    Me.GoalNumber) = 0 And DCount("*", "qry_GoalsObjsActionsURQRM", _
    "GoalNumber=" & Me.GoalNumber) = 0
    End Sub
    Attached Files Attached Files

Posting Permissions

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