How to arrange columns in a report (visible/invisible)

[Martinchox]

Hello everyone, this is my first post in this forum. I've been searching everywhere for a solution for this but I haven't been able to find one. It should be said now that I'm beginner in VBA and haven't got much idea about it. I've accomplished some simpler things like hiding fields based on the data in another field but nothing very complex.
To the point: I have a report that shows data in both rows and columns, the report has a 7 columns, 3 of which could or could not contain data other than zero, in which case the column is not displayed. The problem I have is that it could be any of those three at any given time depending on what data is available for that report and what the user wants to see. Basically, if a given company has no data regarding column "A", then column A is hidden, but the main grouping is done through rows so all companies contain at least 0 for all columns (what I did is that, if a field sums the whole column returns 0, then the column is not visible). What I need to get working is the second part of this, have the columns rearrange themselves depending on which column is hidden (it could be that all 3 are hidden). How could I make this work? (btw, autoshrink is not usefull for this since it only shrinks vertically, not horizontally).

Thanks in advance for any help you can give me!

 

[CJ_London]

something VBA like

myControl.visible=dsum("someValue","rptQuery")<>0

not sure where to put it - probably the detail onformat event, depends on where your controls are. Probably need to repeatfor the column header

 

[Martinchox]

Hi CJ, thanks for the quick reply.
I'm not very sure I understand. I'm guessing "somevalue" would be the width? Could you expand a little if it's not too much trouble?

 

[CJ_London]

you haven't provided the detail so I'm just guessing - you said this

Basically, if a given company has no data regarding column "A", then column A is hidden,

'somevalue' is referring to whatever you call column A

 

[Martinchox]

Oh, sorry about that. Colum names are C1, C2 and C3, I also have a C0 column which is equal in size as the other 3. After a while of browsing I came up with something which I modified to this, but whenever I run it I get an error that I'm calling a function that is not in the database (btw I'm from Argentina so some stuff may show in spanish)

Private Sub SecciónEncabezadoDePágina_Format(Cancel As Integer, FormatCount As Integer, Optional C0 As Double, Optional C1 As Double, Optional C2 As Double, Optional C3 As Double)
Dim dblStart As Double
Dim dblLength As Double
Dim ctlControl As Control
Dim dblL As Double
Dim dblWidth As Double
Dim intWidth As Integer
' dblWidth = C1.Width
' If dblWidth = 0 Then dblWidth = dblMinWidth
dblL = C0.Left + C0.Width
C1.Left = C0.Left + C0.Width
dblL = C1.Width + dblL
C2.Left = C1.Left + C1.Width
dblL = C2.Width + dblL
C3.Left = C2.Left ' added in case c2 is invisible
intWidth = C2.Width
If C2.Visible Then ' add this to ea object to elim gaps for invisibles
' only change col2's position if it is visible
C3.Left = C2.Left + intWidth ' changed to intWidth from C1.width
intWidth = C3.Width ' add this to ea obj
dblL = C3.Width + dblL ' only increase add to the line's length if it is visible
End If

End Sub

 

[Martinchox]

And this is the code I have to make fields invisible:
Private Sub Report_Load()
If sumprod = 0 Then
Cprod.Visible = False
C1.Visible = False
sumprod.Visible = False
sumprodant.Visible = False
End If
If sumserv = 0 Then
Cserv.Visible = False
C2.Visible = False
sumserv.Visible = False
sumservant.Visible = False
End If
If sumded = 0 Then
Cded.Visible = False
C3.Visible = False
sumded.Visible = False
sumdedant.Visible = False
End If
End Sub

 

[CJ_London]

sorry, can't read your code, please indent properly and use the code tags to preserve the formatting

 

[Martinchox]

Private Sub SecciónEncabezadoDePágina_Format(Cancel As Integer, FormatCount As Integer, Optional C0 As Double, Optional C1 As Double, Optional C2 As Double, Optional C3 As Double)
 Dim dblStart As Double
 Dim dblLength As Double
 Dim ctlControl As Control
 Dim dblL As Double
 Dim dblWidth As Double
 Dim intWidth As Integer
 'dblWidth = C0.Width
dblL = C0.Left + C0.Width
C1.Left = C0.Left + C0.Width
dblL = C1.Width + dblL
C2.Left = C1.Left + C1.Width
dblL = C2.Width + dblL
C3.Left = C2.Left 'added in case c2 is invisible
intWidth = C2.Width
If C2.Visible Then 'add this to ea object to elim gaps for invisibles				
C3.Left = C2.Left + intWidth 'changed to intWidth from C0.width
intWidth = C3.Width 'add this to each obj
dblL = C3.Width + dblL ' only increase add to the line's length if it is visible
     End If
End Sub

 

[CJ_London]

not sure about it but some observations

1. in your function parameters you have C0, C1 etc declared as doubles, but the code implies they are a control - try changing them in the parameters to controls

2. left, width etc should be singles, not doubles (tho' may not matter) but you have declared intWidth as an integer

3. in your parameters, C0 etc is optional, which means if you don't pass a value, it will be null. You're code is not allowing for nulls

4. a much simpler code would be (this assumes your controls are called C0, C1 etc)

Private Sub SecciónEncabezadoDePágina_Format(Cancel As Integer, FormatCount As Integer)
dim I as integer
dim lft as single
 
    lft=C0.left
    for I=0 to 3
        if me("C" & I).visible then
            me("C" & I).left=lft  
            lft=lft+me("C" & I).width
        end if
    next I
 
end sub

 

[Martinchox]

Hi CJ, thanks for your reply. I will test it soon but it looks like that'd be the correct way to do it!