Collapse columns to left if visible = false

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
 
J

John Spencer

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

heidii

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

??
 
J

John Spencer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Resize columns on report to match form and move columns left toremove gaps 2
Change report width based on total columns widths 2
VBA Export to PDF 0
VBA works in orignal but fails in copy of database 1
Macro to Select text from a point given to a specific word 2
VBA - exporting serial letters to single documents 0
How to hide columns in a form in datasheet view if null 5
Multiselect Listbox 3

Top