Variable Number of Fields in Reports

Question

What's the best way to handle a report (tabular) that has variable number of fields (columns) which is based on the output of a query?

Most often I do not know how many fields will be displayed on the report, so what I do is create as many textfields as possible and set their visible property to false. If a column is needed, I just assign it a value and set visible to true. This makes the report not only big but hard to maintain as well...

Answer

Raymon,

We use similar solution but we keep all controls shifted to the left. Btw, you can generate as many controls as you want using template control by something like this function:

Sub smsCloneRptCtls()
    Dim rpt As Report
    Dim ctlTemplate As TextBox
    Dim ctl As TextBox
    Dim prp As Property
    Dim i As Integer
    
    DoCmd.OpenReport "rptTst", acViewDesign
    
    Set rpt = Reports![rptTst]
    Set ctlTemplate = rpt![txtTemplate]
    
    For i = 0 To 100
        Set ctl = CreateReportControl(rpt.Name, acTextBox, , , , _
                                    ctlTemplate.Left, _
                                    ctlTemplate.Top, _
                                    ctlTemplate.Width, _
                                    ctlTemplate.Height)
      
       For Each prp In ctlTemplate.Properties
          On Error Resume Next
          ctl.Properties(prp.Name) = ctlTemplate.Properties(prp.Name)
       Next
       
       ctl.Visible = False
       ctl.Name = "txt" & i
    Next i
    
End Sub

The same way you can generate corresponding column labels. Then on Report_Open you can shift controls to the right/set their controlsource/make them visible based on report's recordsource:

Private Sub Report_Open(Cancel As Integer
    
    Dim rst As Recordset
    Dim i As Integer
    '
    Set rst = CodeDb().OpenRecordset(Me.RecordSource, dbOpenSnapshot)
    
    For i = 0 To rst.Fields.Count - 1
        Me("txt" & i).ControlSource = rst(i).Name
        Me("txt" & i).Visible = True
        If i <> 0 Then
            Me("txt" & i).Left = Me("txt" & CStr(i - 1)).Left + _
                                 Me("txt" & CStr(i - 1)).Width
        End If
    Next i
End Sub