Hiding columns in report

[Guest]

Is there any way to simply hide columns in a report at runtime and move up
the remaining columns to tidy up the report. I am trying to remove the need
to duplicate the report x times with all the various column combinations
coded.

For example, a report has 10 columns defined, the user selects only 6
columns to be shown on this particular report request, layout required would
therefore be :-

Col 1 Col 3 Col 4 Col 6 Col 7 Col 10
xxx xxx xxx xxx xxx xxx

I have done this using other software but am not hopeful such can be
achieved on Access 2003.

Thanks for any input you may have.
Cheers,
Steve

 

[Marshall Barton]

Is there any way to simply hide columns in a report at runtime and move up
the remaining columns to tidy up the report. I am trying to remove the need
to duplicate the report x times with all the various column combinations
coded.

For example, a report has 10 columns defined, the user selects only 6
columns to be shown on this particular report request, layout required would
therefore be :-

Col 1 Col 3 Col 4 Col 6 Col 7 Col 10
xxx xxx xxx xxx xxx xxx

I have done this using other software but am not hopeful such can be
achieved on Access 2003.

The usual way of doing this sort of thing is to make the
optional text boxes (and their column labels?) invisible at
design time.

If the columns a all the same width, then use the report's
Open event procedure to make the needed one visible and set
their ControlSource property to the field name for the
column. In this situation, it helps make the code shorter
if you name the text boxes (and labels?) with a common
prefix and sequential number suffix. You never said how
users select the fields they want to see, but the code might
be little like this:

For K = 1 To numberof fields
Me("textbox" & K).ControlSource = fieldsarray(K)
Me("textbox" & K).Visible = True
Next K

or, if the columns are different widths, then name the
controls with a control type prefix followed by the bound
field's name:

Dim pos As Long
pos = firstposition
For K = 1 To numberof fields
Me("txt" & fieldsarray(K)).Left = pos
Me("txt" & fieldsarray(K)).Visible = True
pos = pos + Me("txt" & fieldsarray(K)).Width
Next K

 

[Guest]

Marsh,
Thanks for the reply, it appears that I can do all required with a few lines
of code. I haven't as yet decided or been informed of how the user will
select the required columns, but I would assume on calling the report with
various selection parameters a pop-up screen displaying all columns would be
displayed, then allow the user to de-select columns as required and then
enter the report. The report would then interpret the column selection passed
to it via Openargs and display columns as required. I am hopeful that all
columns could be displayed with the same width, but I expect I will need to
use your second option and have variable width columns (user has not yet
specified which columns are required on the report).
Thanks for your time on this, it is much appreciated.
Cheers,
Steve