Column Design -VBA

[toleafs]

Hello...I am using this code that produces reports in tabular that I would like to modify so that I produces a Column style any ideas. Thanks for your help.
Currently:
ActNo
Year1
Year2
Year3
I would like to report it like this:
ActNo Year1 Year2 Year3

_____________
Function CreateDynamicReport(strSQL As String)
Dim db As DAO.Database ' database object
Dim rs As DAO.Recordset ' recordset object
Dim fld As DAO.Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String 'holds title of report

'set the title
title = "Title for the Report"

' initialise position variables
lngLeft = 0
lngTop = 0

'Create the report
Set rpt = CreateReport

' set properties of the Report
With rpt
.Width = 8500
.RecordSource = strSQL
.Caption = title
End With

' Open SQL query as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

' Create Label Title
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Title", 0, 0)
lblNew.FontBold = True
lblNew.FontSize = 8
lblNew.SizeToFit

' Create corresponding label and text box controls for each field.
For Each fld In rs.Fields

' Create new text box control and size to fit data.
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acDetail, , fld.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit

' Create new label control and size to fit data.
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit

' Increment top value for next control
lngTop = lngTop + txtNew.Height + 25
Next

' Create datestamp in Footer
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageFooter, , Now(), 0, 0)

' Create page numbering on footer
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
txtNew.SizeToFit

' Open new report.
DoCmd.OpenReport rpt.Name, acViewPreview

'reset all objects
rs.Close
Set rs = Nothing
Set rpt = Nothing
Set db = Nothing

End Function

 

[xenou]

Why not create the report the "ordinary" way, then just set its record source at runtime. As far as report-writing code, I'm afraid I've never had occasion to do it.

ξ

 

[revans]

Look into a crosstab query. Whenever I need to change columns to rows that's where I start.

hth,

Rich