Automatic Report Creation

[exaccess]

I have a form which produces an SQL query based on a main table and a number of lookup tables. This query is displayed on the screen and then can be executed or produced as a report. If it is produced as a report the user mentions on another input screen the columns of the main table to be printed and the sort orders. A couple of other parameters needed for the report such as its title, date, font family and font size are also given. Then the report should be automatically generated. For this purpose I am using CreateReport and CreateReportControls methods. But the parameters of these two methods are not very clear. I need a report header, a page header and details of the records one on each row. Do I have to use the CreateReportControls method three times? First one only once, Second one on each page, third one on every line? Does anyone have an example of such a report or point at an example? Help is appreciated.

 

[exaccess]

After some research I have found code on Internet that can be used. In fact I am using the following code for my problem.
Public Sub StaticReportGen(SQLStr As String, title As String, layout As String)
Dim strReportName As String
Dim rpt As Report
Dim FieldName As Field
Dim rs As Recordset
Dim intI As Integer
Dim ctrl As Control
Dim ColWidth As Integer
Dim FirstCol As Boolean
Dim TextWidth As Integer
Dim TextCol As Boolean
Dim TextBoxes As Collection
Dim Labels As Collection
Dim rsLengthCheck As Recordset
Dim objConn As Connection

On Error GoTo rptErrHandler

ColWidth = 0
TextWidth = 0
TextCol = True
FirstCol = True

Set rpt = CreateReport()
strReportName = rpt.Name
rpt.Caption = title

DoCmd.RunCommand acCmdDesignView
DoCmd.Save acReport, strReportName
DoCmd.Close acReport, strReportName, acSaveNo
DoCmd.Rename title, acReport, strReportName
DoCmd.OpenReport title, acViewDesign
Set rpt = Reports(title)

'set printer stuff
rpt.Printer.BottomMargin = 360
rpt.Printer.LeftMargin = 360
rpt.Printer.RightMargin = 360
rpt.Printer.TopMargin = 360

If layout = "Landscape" Then
rpt.Printer.Orientation = acPRORLandscape
Else
rpt.Printer.Orientation = acPRORPortrait
End If
Dim I As Integer
Set rs = CurrentDb.OpenRecordset(SQLStr)
rpt.RecordSource = SQLStr
I = rs.Fields.Count

'create label on pageheader
For Each FieldName In rs.Fields
CreateReportControl title, acLabel, acPageHeader, , FieldName.Name, 0, 0
CreateReportControl title, acTextBox, acDetail, , FieldName.Name, 0, 0

Next FieldName
'arrange fields
For Each ctrl In rpt.Controls

Select Case ctrl.ControlType
Case acTextBox
If TextCol Then
ctrl.Name = ctrl.ControlSource
ctrl.Move TextWidth, 0, ctrl.Width, ctrl.Height
TextWidth = TextWidth + ctrl.Width
Else
ctrl.Name = ctrl.ControlSource
ctrl.Move TextWidth, 0, ctrl.Width, ctrl.Height
TextWidth = TextWidth + ctrl.Width
End If
TextCol = False

Case acLabel
If FirstCol Then
ctrl.Name = "lbl" & ctrl.Caption
ctrl.Move ColWidth, 0, ctrl.Width, ctrl.Height
Else
ctrl.Name = "lbl" & ctrl.Caption
ctrl.Move TextWidth, 0, ctrl.Width, ctrl.Height
End If
ctrl.FontSize = 10
ctrl.FontWeight = 700
FirstCol = False

Case Else

End Select

Next ctrl
'create line
CreateReportControl title, acLine, acPageHeader, , , 0, 300, rpt.Width

'create title
CreateReportControl title, acLabel, acHeader, , title, 0, 0
CreateReportControl title, acTextBox, acHeader, , Chr(61) & Chr(34) & "Printed on: " & Chr(34) & "& Date() ", 0, 300

For Each ctrl In rpt.Controls

Select Case ctrl.ControlType
Case acTextBox

If ctrl.Section = 1 Then
ctrl.FontWeight = 700
ctrl.FontSize = 14
ctrl.Height = 350
ctrl.Width = 3500
ctrl.Top = 400
End If

Case acLabel

If ctrl.Section = 1 Then
ctrl.FontSize = 16
ctrl.FontWeight = 700
ctrl.Height = 350
ctrl.Width = 3500
End If
End Select

Next ctrl

'size fields correctly
For Each ctrl In rpt.Controls

Select Case ctrl.ControlType

Case acTextBox
For Each FieldName In rs.Fields
If ctrl.Name = FieldName Then

End If
Next FieldName

Case acLabel

End Select

Next ctrl

DoCmd.Save acReport, title
DoCmd.OpenReport title, acViewPreview

Exit Sub

rptErrHandler:
Select Case Err.Number
End Select

Debug.Print Err.Number
Debug.Print Err.Description
Exit Sub
End Sub
This code creates the report automatically. Except that it puts one line on each page and does not print the title of the report. I tried all kinds of combinations to separate the page header and the values of the fields, but in vain. Could any one help me please?