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...
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
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.