Display/hide totals

[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?

 

[Allen Browne]

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

 

[John Spencer]

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]

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.

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
..

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?