Eliminate whitespace for hidden report columns (tabular format)

[Jenaccess]

Access Version: 2010

Experience Level: Beginning/Intermediate-Comfortable with basic vba coding, but need a bit more guidance with anything advanced, though I'm good at following examples.

Hi,

I've been researching this issue for two days straight, and while I've read some posts that were in the general vicinity of what I'm looking for, I didn't find an answer to my exact problem.

I have a large tabular report, 22" and 18 columns. In the report footer I have for each column a series of calculated fields, specific to that column. For example, one of my columns is NumberofCreditsEarned. The calculated fields at the bottom of it are min, max and avg. Another one is Gender, and the calculated fields at the bottom of that one count the males and females and give percentages of each.

I designed a basic form with 18 checkboxes, so users could select as many or as few columns as they want. My original idea was to use the checkboxes to show the desired columns (along with the calculations for those columns) and hide the rest.

The issue is that while the column may be hidden, the white space is not. If I can't find a way to get rid of it, my reporting tool won't be feasible to use. I've researched using "can grow" and "can shrink", but I don’t think they'd work on a tabular report like this, where all of the controls are the same height and width, and on the same line.

I'm open to pretty much any solution/workaround that will help me get rid of the white space so my reporting tool will work. Thank you for your time.

 

[Mihail]

It is doable by manipulating the .Left property for controls.
ControlName.Left = ControlName.Left - AmountOfSpace
Of course you should do the calculation for AmountOfSpace. Something like this:
AmountOfSpace = CountColumnsFromLeftThatShouldBeHidden * (ColumnWidth + GapsBetweenColumns).

Use the open event of the report.

Hope you understand the idea, because my English...

 

[Jenaccess]

Thank you for your advice Mihail. The .Left property suggestion has helped me make progress.

I just wanted to ask about this:

AmountOfSpace = CountColumnsFromLeftThatShouldBeHidden * (ColumnWidth + GapsBetweenColumns).

Is CountColumnsFromLeftThatShouldBeHidden a calculation also? Should I make that a separate function, or can that go in the Open Event as well?

Thank you for your time and help!

 

[Mihail]

Is CountColumnsFromLeftThatShouldBeHidden a calculation also?

Should be, isn't it ?
Say you have 7 fields (columns) in your report (all possible) and the user select like this:

Cn - Sel - CountColumnsFromLeftThatShouldBeHidden
C1 - Yes - Do not move
C2 - No - Hide
C3 - No - Hide
C4 - Yes - Move with 2 columns to the left
C5 - Yes - Move with 2 columns to the left
C6 - No - Hide
C7 - Yes - Move with 3 columns to the left

 

[Jenaccess]

That does make sense, and I'm trying to do something like that. I'm getting stuck on how to move each column relative to the others. For example, I have 18 columns that the user can choose from, and they are free to choose any combination of those columns. Let's say the user chooses Columns 3, 10, and 18.

In the scenario you've described (which is what I want), Column3 would be moved to Column1, Column10 would be moved to Column2, and Column18 would be moved to Column3 and so forth. I know there has to be a better way to do this than hard coding every possible combination with if/then statements, but I'm not sure how to go about it. Would a For/Next loop be appropriate here?

Again, thank you for taking the time to help me. I am making progress and learning.

 

[Mihail]

I know there has to be a better way to do this than hard coding every possible combination with if/then statements

Yep. It is. (Adapt blue names and values to your needs)

Private Sub Report_Open(Cancel As Integer)

Const MostLeftPosition As Long = [COLOR=Blue][B]3[/B][/COLOR]
Const ColumnsWidth As Long = [COLOR=Blue][B]25[/B][/COLOR] [I][B]'Including the space between columns[/B][/I]

Dim frm As Form
  Set frm = Forms![COLOR=Blue][B]YourFormName[/B][/COLOR]

Dim CountVisibleColumns
  CountVisibleColumns = 0 [I][B]'Not necessary but for maintenance[/B][/I]

  With frm
    If [B].[COLOR=Blue]chk_1[/COLOR][/B] Then [I][B]'chk = check box in the [U]form[/U][/B][/I]
      Me.[COLOR=Blue][B]Col_1[/B][/COLOR].Left = MostLeftPosition +CountVisibleColumns * ColumnsWidth
      CountVisibleColumns = CountVisibleColumns+1
    End If

[I][B] 'Copy - Paste previous [U]IF-End If[/U] as many time is necessary:[/B][/I]

    If [B].[COLOR=Blue]chk_2[/COLOR][/B] Then
      Me.[COLOR=Blue][B]Col_2[/B][/COLOR].Left = MostLeftPosition +CountVisibleColumns * ColumnsWidth
      CountVisibleColumns = CountVisibleColumns+1
    End If

[I][B] 'And so on ................[/B][/I]

  End With

End Sub

 

[Jenaccess]

I've adapted the code with my own form/field names, and when I go to open the report, no matter which checkboxes I check on the form, the report only shows the final field I checked, all the way over to the left. For example, if I check CollegeID, FullName, and Accuplacer, the only field I see on the report is Accuplacer, and it's at the Leftmost position of 0. Did I do something wrong, or am I misinterpreting something? Thanks so much for your patience as I try to learn.

Private Sub Report_Open(Cancel As Integer)

Const MostLeftPosition As Long = 3
Const ColumnWidth As Long = 25

Dim frm As Form
Set frm = Forms!frmChooseCustomFields

Dim CountVisibleColumns
CountVisibleColumns = 0 'Not necessary but for maintenance


With frm
If .chkCollegeID = True Then 'chk = check box in the form
Me.CollegeID.Left = MostLeftPosition + CountVisibleColumns * ColumnsWidth
CountVisibleColumns = CountVisibleColumns + 1
End If


If .chkFullName = True Then 'chk = check box in the form
Me.FullName.Left = MostLeftPosition + CountVisibleColumns * ColumnsWidth
CountVisibleColumns = CountVisibleColumns + 1
End If

If .chkAccuplacer = True Then 'chk = check box in the form
Me.Accuplacer.Left = MostLeftPosition + CountVisibleColumns * ColumnsWidth
CountVisibleColumns = CountVisibleColumns + 1
End If

If .chkCreditsAttemptedAll = True Then 'chk = check box in the form
Me.Attempted.Left = MostLeftPosition + CountVisibleColumns * ColumnsWidth
CountVisibleColumns = CountVisibleColumns + 1
End If

End With

End Sub

 

[Mihail]

1) If you will take a closer look to my code, will see that I never use If .chk = True.
Is not necessary.
2) Turn on the Option Explicit in your VBA editor.
Go to Tools -> Options > and check Require Variable Declaration
Eventually uncheck Auto Syntax Check.
3) For now, write Option Explicit on the top of your module (first line)
After you write this, go to Debug -> Compile. (menu bar)
The message box will say you that you give a wrong copy for the 3rd line from my code:
It is ColumnsWidth, not ColumnWidth. That "s" make the difference.

I see that you intend to use 3 and 25 as values.
I am huge doubts that you have exact this values on your report.
So:
Open the report in design view.
Select the most left control and read, from the Property Sheet it's left value.
Replace my "3" with this value.
Select next control.
Read again it's left value.
The difference between this value and the previous one is the distance between controls (my 25) so replace my "25" with this difference.
Is simple math.

Now should be OK.

 

[Jenaccess]

This works perfectly. Thank you so much for your help and patience!