Hide (visible/invisible) Columns in a report based on user selection

[Incolor]

I want to hide columns (controls) in a report based on a user's
selections. For example, I have a dialog form where the user can select
what controls to hide. I am not a programmer, just getting into it
recently. How would I use the visible property to do this? Is my
thinking process correct? Should I start with all the columns and then
make them invisible based on the user selections? Can someone suggest a
better way? I am coding in MS Access 2003.

Sorry for all the questions.

thank you in advance,

maribel

 

[Guest]

Use the Format event of the section of the report the controls are in.
For example, let's assume there is a Check Box on the form the user makes
the selection from to either show or hide a control on the report and the
user has to check the box for the control to be visible. It would be:

Me.txtSomeTextBox.Visible = Forms!MyFormName!chkMyCheckBox

So if the check box is checked, it will return True, which will make the
control's visible property true.

 

[Incolor]

Thanks Klatuu for replying. I am not sure I understand fully. Here are
some more details. Like I said I do have a dialog form. I have the list
of the fields (columns) in a simple multi-select list box. The user can
choose one to all of the fields to display.

The report is modified in print preview based on the user selections in
the dialog form. The user can see all the fields in print preview. The
first selection the user would have is to choose to "undisplay" the
columns he/she does not need on the report. Then based on his/her
selections he/she would then select to filter the results by let's say
employee, analyte, location etc.

I just can't put the code together so that when the user makes the
field list selections he/she does not want displayed for them to not be
visible in the report preview.

Thanks for your help.

 

[Guest]

You can't do this while the report is open regardless of whether it is in
preview or not. The hiding and showing of specific controls on the report is
done while the report is running. To hide specific controls, you can use the
dialog form to do that. What I posted is how it would work.
Each section of a report has an On Format event which is used for just this
sort of thing. For example purposes, let's assume your column is a text box
control on your report in the detail section. To know whether to hide the
text box or not, the Format event of the detail section of the report needs a
reference to your selection. That selection is made with a check box on your
form. That check box has a name. The code in the detail section format
event looks at the value in the check box on the form to know whether to hide
the text box on the report. It does it like this:

Me.txtSomeTextBox.Visible = Forms!MyFormName!chkMyCheckBox

txtSomeTextBox is the name of the text box on the report you want to show or
hide.
It's visible property determines whether it is shown or hidden.
MyFormName is the name of the form where the check box is.
chkMyCheckBox is the name of the check box.
Check boxes return either True if checked or False if not checked.
A text box's Visible property is either True (Show it) or False (Hide it),
so the code above set's the report's text box to the same value as the form's
check box.
You will need one check box for each column in the report you want the
option to show or hide, and one statement in the format event for eacho of
the columns.
Remember, the form must remain open while the report is running for this to
work.

 

[Incolor]

Thank you Klatuu. Very good explanation. This is what I have been
looking for. I did not know if it was possible. I kept trying
different things...no wonder.

Thaks again! Have a good weekend!

 

[Incolor]

I've tried this and it doesn't work. I never thought this would be so
hard. All I wanted to do was mimic a select query in a dialog form
which would include what fields to display. Any other ideas on what I
may be doing wrong?

thanks!