Collapse columns to left if visible = false

  • Thread starter Thread starter heidii
  • Start date Start date
H

heidii

Hi there:

I have created a report that runs from a datasheet form. I can hide
and unhide the columns in my form and then generate report where it
then hides or unhides my columns the same as my form. But I don't
know how to make my columns that I have made invisible move to the
left instead of leaving a gap on my report.

I need help.

Here is the code that I am using now for making the columns invisible:
My code for all the labels is the same except different names.

Thanks in advance

Heidi

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Forms![frm-ranch_info_ranch_view]![LOT#].ColumnHidden Then

Me.[LOT#].Visible = False

Else
Me.[LOT#].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![RANCH].ColumnHidden Then

Me.RANCH.Visible = False
Else
Me.RANCH.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![BLOCKTYPE].ColumnHidden Then

Me.BLOCKTYPE.Visible = False
Else
Me.BLOCKTYPE.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![VARIETY].ColumnHidden Then

Me.VARIETY.Visible = False
Else
Me.VARIETY.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Acres].ColumnHidden Then

Me.Acres.Visible = False
Else
Me.Acres.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Spacing].ColumnHidden Then

Me.Spacing.Visible = False
Else
Me.Spacing.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![ROOTSTOCK].ColumnHidden Then

Me.ROOTSTOCK.Visible = False
Else
Me.ROOTSTOCK.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Trellis].ColumnHidden Then

Me.Trellis.Visible = False
Else
Me.Trellis.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Planted].ColumnHidden Then

Me.Planted.Visible = False
Else
Me.Planted.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Grafted].ColumnHidden Then

Me.Grafted.Visible = False
Else
Me.Grafted.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![STATUS].ColumnHidden Then

Me.STATUS.Visible = False
Else
Me.STATUS.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![WSDA SITE NUMBER].ColumnHidden
Then

Me.[WSDA SITE NUMBER].Visible = False
Else
Me.[WSDA SITE NUMBER].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![COMMODITY].ColumnHidden Then

Me.COMMODITY.Visible = False
Else
Me.COMMODITY.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![WSDACert#].ColumnHidden Then

Me.[WSDACert#].Visible = False
Else
Me.[WSDACert#].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![MASTER VARIETY].ColumnHidden
Then

Me.[MASTER VARIETY].Visible = False
Else
Me.[MASTER VARIETY].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Block].ColumnHidden Then

Me.Block.Visible = False
Else
Me.Block.Visible = True
End If

End Sub
 
You will have to use the left property of each of the controls in the report
and move the controls around that way.

You will need to track the the width (in twips) of each control and the
cumulative width used.

I'm thinking that the code MIGHT look like the following untested code snippet

Dim LLeft as Long

LLeft = FirstControl.Left

If FirstControl.Visible then
FirstControl.Left = LLeft 'Not really needed
LLeft = LLeft + FirstControl.Width
End If

If SecondControl.Visible Then
SecondControl.Left = LLeft
LLeft = LLeft +SecondControl.Width
End If

If ThirdControl.Visible then
ThirdControl.Left = LLeft
LLeft =LLeft +ThirdControl.Width
End if

Of course, that could probably be put into a loop if you had a way to ensure
the controls would be processed in left to right order.

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

I have created a report that runs from a datasheet form. I can hide
and unhide the columns in my form and then generate report where it
then hides or unhides my columns the same as my form. But I don't
know how to make my columns that I have made invisible move to the
left instead of leaving a gap on my report.

I need help.

Here is the code that I am using now for making the columns invisible:
My code for all the labels is the same except different names.

Thanks in advance

Heidi

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Forms![frm-ranch_info_ranch_view]![LOT#].ColumnHidden Then

Me.[LOT#].Visible = False

Else
Me.[LOT#].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![RANCH].ColumnHidden Then

Me.RANCH.Visible = False
Else
Me.RANCH.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![BLOCKTYPE].ColumnHidden Then

Me.BLOCKTYPE.Visible = False
Else
Me.BLOCKTYPE.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![VARIETY].ColumnHidden Then

Me.VARIETY.Visible = False
Else
Me.VARIETY.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Acres].ColumnHidden Then

Me.Acres.Visible = False
Else
Me.Acres.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Spacing].ColumnHidden Then

Me.Spacing.Visible = False
Else
Me.Spacing.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![ROOTSTOCK].ColumnHidden Then

Me.ROOTSTOCK.Visible = False
Else
Me.ROOTSTOCK.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Trellis].ColumnHidden Then

Me.Trellis.Visible = False
Else
Me.Trellis.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Planted].ColumnHidden Then

Me.Planted.Visible = False
Else
Me.Planted.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Grafted].ColumnHidden Then

Me.Grafted.Visible = False
Else
Me.Grafted.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![STATUS].ColumnHidden Then

Me.STATUS.Visible = False
Else
Me.STATUS.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![WSDA SITE NUMBER].ColumnHidden
Then

Me.[WSDA SITE NUMBER].Visible = False
Else
Me.[WSDA SITE NUMBER].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![COMMODITY].ColumnHidden Then

Me.COMMODITY.Visible = False
Else
Me.COMMODITY.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![WSDACert#].ColumnHidden Then

Me.[WSDACert#].Visible = False
Else
Me.[WSDACert#].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![MASTER VARIETY].ColumnHidden
Then

Me.[MASTER VARIETY].Visible = False
Else
Me.[MASTER VARIETY].Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Block].ColumnHidden Then

Me.Block.Visible = False
Else
Me.Block.Visible = True
End If

End Sub
Click to expand...
 
Does that mean I have to write this line mutiple times to designate
all the different text boxes and labels?

Example:

Dim LLeft As Long
LLeft=FirstControl.Left

&

Dim LLeft2 As Long
LLeft2 = SecondControl.Left

??
 
As far as I can tell the answer to your question is Yes. Remember I didn't
test this and I don't even know if it will work. I've never had the need to
do something like this.

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