Display/hide totals

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

Hi,

Using Access 2003. I have a report with 5 columns, 3 of which have a Sum in
the report footer. There may be need at times to hide one or more of the
columns, and the sum total for that column. To handle this I created a form
with check boxes where the user can select what columns to show/hide. The
underlying VBA will then generate the report and show/hide the various parts.
All of that works except for hiding the individual total fields.

If the user selects the "hide totals" box, the report correctly hides all 3
of the sum fields with the code

Reports!Total_Billing.Controls!txtSumChars.Visible = False
Reports!Total_Billing.Controls!txtSumTotal.Visible = False
Reports!Total_Billing.Controls!txtSumReports.Visible = False

However, if the user selects "Hide Price", the deisred outcome is to hide
the [BillTotal] field in detail, and the [txtSumTotal] (the sum of
[BillTotal]) field. What happens is that only the detail field is hidden,
not the sum total field in the footer. However, the vba code is the same.

Reports!Total_Billing.Controls!BillTotal.Visible = False
Reports!Total_Billing.Controls!BillTotal_Label.Visible = False
Reports!Total_Billing.Controls!txtSumTotal.Visible = False

In other words, I can hide all of the sum total fields by setting their
individual Visible property to False, but I can't hide just one of them.
This happens regardless of which of the 3 fields I try to hide.

What would cause this?
 
Use the events of the report to show/hide the text boxes.
Otherwise the code runs too late.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Const strcForm = "Form1" 'Put your form name in the quotes.

If CurrentProject.AllForms(strcForm).IsLoaded Then
With Forms(strcForm)
Me.txtSumChars.Visible = Nz(!chkShowSumChars.Value, False)
Me.txtSumTotal.Visible = Nz(!chkShowTotal.Value, False)
'etc.
End With
End If
End Sub
 
Do you have code that makes the visible property TRUE anywhere? If so,
could that code be resetting the visible property of the control?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John,

Thanks! That was it.

Allen - next time I think I'll try it your way, as I can see what you are
referring to with the code running too late.

John Spencer said:
Do you have code that makes the visible property TRUE anywhere? If so,
could that code be resetting the visible property of the control?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Al said:
Hi,

Using Access 2003. I have a report with 5 columns, 3 of which have a Sum
in
the report footer. There may be need at times to hide one or more of the
columns, and the sum total for that column. To handle this I created a
form
with check boxes where the user can select what columns to show/hide. The
underlying VBA will then generate the report and show/hide the various
parts.
All of that works except for hiding the individual total fields.

If the user selects the "hide totals" box, the report correctly hides all
3
of the sum fields with the code

Reports!Total_Billing.Controls!txtSumChars.Visible = False
Reports!Total_Billing.Controls!txtSumTotal.Visible = False
Reports!Total_Billing.Controls!txtSumReports.Visible = False

However, if the user selects "Hide Price", the deisred outcome is to hide
the [BillTotal] field in detail, and the [txtSumTotal] (the sum of
[BillTotal]) field. What happens is that only the detail field is hidden,
not the sum total field in the footer. However, the vba code is the same.

Reports!Total_Billing.Controls!BillTotal.Visible = False
Reports!Total_Billing.Controls!BillTotal_Label.Visible = False
Reports!Total_Billing.Controls!txtSumTotal.Visible = False

In other words, I can hide all of the sum total fields by setting their
individual Visible property to False, but I can't hide just one of them.
This happens regardless of which of the 3 fields I try to hide.

What would cause this?
Click to expand...
Click to expand...
 
Back
Top