Columns in report...

[Chatbox]

I'm trying to make a report where sometimes there'll be 2 columns, sometime will be 5 columns...or maybe 4 columns. (User select which columns they'd like to show in the report/printout).

The question is, how do I make these columns all pack towards the right hand side of the page?

Here's an example:

Some info....          Column 1     Column 2     Column 3     Column 4

Or sometimes it'll be

Some info....                                    Column 2     Column 4

In the first one, user selected/wanted to show 4 columns of data. In the second one, user picked Columns 2 and 4. And I want 2 and 4 to pack towards the right side.

How can I do this?

 

[Chatbox]

Anyone know a solution?

 

[ChrisO]

As the report opens you can set the Left property of the text boxes to slide then into place.

For example: -

Me.txtColumn4.Left = 6000
Me.txtColumn2.Left = 4000

You may also need to set the Visible property as well.

Hope that helps.

Regards,
Chris.

 

[Chatbox]

But how would the report known when to do the "sliding"?

I'm soooo new to reports...sorry.

When does a query carry out when a report is open?
Also, when a report opens, is there some kind of before/after[action] that I can run the self check (such as...if column 3 is empty/null then shift column 2)?

The other thing is, the .mdb is going to be read only, can this "changing/shifting at run-time" still occur?

 

[ChrisO]

With textboxes named txtColumn1 to txtColumn5 you could use something like this: -

Option Explicit
Option Compare Text
Option Base 0

Dim lngLeftArray() As Long


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngSuffix      As Long
    Dim lngElement     As Long
    Dim strControlName As String
    
    For lngSuffix = 5 To 1 Step -1
        strControlName = "txtColumn" & CStr(lngSuffix)
        If Nz(Me(strControlName), "") = "" Then
            Me(strControlName).Visible = False
        Else
            Me(strControlName).Visible = True
            Me(strControlName).Left = lngLeftArray(lngElement)
            lngElement = lngElement + 1
        End If
    Next lngSuffix

End Sub


Private Sub Report_Open(Cancel As Integer)

    ReDim lngLeftArray(4)
    
    lngLeftArray(0) = 5000
    lngLeftArray(1) = 4000
    lngLeftArray(2) = 3000
    lngLeftArray(3) = 2000
    lngLeftArray(4) = 1000

End Sub

When does a query carry out when a report is open?
I think that the report data is not available until the ReportHeader_Format event or perhaps the Detail_Format event.
The "changing/shifting at run-time" will occur even in an MDE file.

I think I would need to look at the query with a little ‘insensitive’ data to go much further.

If you could post a little A97 demo we could have a look at it.

Regards,
Chris.

 

[Chatbox]

Thank you so much! That's all the info I need really. I totally appreciate your help in this matter.

 

[ChrisO]

You’re welcome.

You may want to speed things up and/or slide the header column labels as well.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngSuffix  As Long
    Dim lngElement As Long
    Static blnDone As Boolean
    
    If Not (blnDone) Then
        For lngSuffix = 5 To 1 Step -1
            If Len(Me("txtColumn" & CStr(lngSuffix))) Then
                Me("txtColumn" & CStr(lngSuffix)).Visible = True
                Me("lblColumn" & CStr(lngSuffix)).Visible = True
                Me("txtColumn" & CStr(lngSuffix)).Left = lngLeftArray(lngElement)
                Me("lblColumn" & CStr(lngSuffix)).Left = lngLeftArray(lngElement)
                lngElement = lngElement + 1
            Else
                Me("txtColumn" & CStr(lngSuffix)).Visible = False
                Me("lblColumn" & CStr(lngSuffix)).Visible = False
            End If
        Next lngSuffix
        blnDone = True
    End If

End Sub

Hope that helps.

Regards,
Chris.