Resize columns on report to match form and move columns left toremove gaps

H

heidii

Hello All:

Here is what I have:

1. Form in datasheet view that I use to filter records and hide /
unhide columns. Has button on custom toolbar to open report.
2. Report that opens from datasheet form above that show my fiiltered
records and removes my columns if they are hidden on my form.

I have pasted the code below that I used to hide / unhide columns on
my report based off the visibility of columns on my form. That all
works great.

I need help writing the code to grab the column width property of the
columns on the form and apply the same column width to the columns on
my report, then move those columns to the left so there are not
gaps.

Any help greatly accepted.

Thanks

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

Me.txt1.Visible = False

Else
Me.txt1.Visible = True
End If

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

Me.txt2.Visible = False

Else
Me.txt2.Visible = True
End If

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

If Forms![frm-ranch_info_ranch_view]![LPMA DATE].ColumnHidden Then

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

End Sub
 
M

Marshall Barton

heidii said:
Here is what I have:

1. Form in datasheet view that I use to filter records and hide /
unhide columns. Has button on custom toolbar to open report.
2. Report that opens from datasheet form above that show my fiiltered
records and removes my columns if they are hidden on my form.

I have pasted the code below that I used to hide / unhide columns on
my report based off the visibility of columns on my form. That all
works great.

I need help writing the code to grab the column width property of the
columns on the form and apply the same column width to the columns on
my report, then move those columns to the left so there are not
gaps.


If Forms![frm-ranch_info_ranch_view]![LOT#].ColumnHidden Then
Me.txt1.Visible = False
Else
Me.txt1.Visible = True
End If

If Forms![frm-ranch_info_ranch_view]![Block].ColumnHidden Then
Me.txt2.Visible = False
Else
Me.txt2.Visible = True
End If


See John Spencer's reply in your other post with the same
question.

The column width can be changed by adding;
Me.txt1.Width=Forms![frm-ranch_info_ranch_view]![LOT#].ColumnWidth

Note that you can save yourself a LOT of typing amd make the
code easier to read ny using the With statement and
combining the If and assignment statements like this:

With Forms![frm-ranch_info_ranch_view]
Me.txt1.Visible = Not ![LOT#].ColumnHidden
Me.txt2.Visible = Not ![Block].ColumnHidden
. . .
End Width
 

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

Top