use ms access vba to write a report and put a line in the page header

[sharon.chapman7]

Hi, I am using VBA to create a report and then write my data from my recordsource from. Does anyone know how I would write a line in the Page Header section? I'm using MS Access 2010. here is my code for my Page Header and I want to put a line at the bottom of the Page Header:

Private Sub RunReport_Click()
Dim db As Database ' database object
Dim rs As Recordset ' recordset object
Dim sSQL As String
Dim fld As Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim lblSub 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
Dim lngBlack As Long
Dim rptData As String

lngBlack = RGB(0, 0, 0)

'set the title
title = "Activity Summary Report"

' initialise position variables
lngLeft = 0
lngTop = 0

'Create the report
Set rpt = CreateReport

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

sSQL = "SELECT Submission_Date, Nbr_Files_In_Set, Motion_filesize, Motion_DatePeriodFrom, Motion_DatePeriodTo " _
& "From dbo_Motion_Imagery;"

' Open SQL query as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

rptData = "Motion Imagery"

' Create Report Header Title
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Activity Summary Report", 0, 0)
With lblNew
.FontBold = True
.FontSize = 14


.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Header Sub Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , rptData, 0, 600)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Submission", 200, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date", 600, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Classification", 1700, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With


' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Nbr of Files", 3400, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Filesize", 4900, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date Period", 5950, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date Period", 7500, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "From", 6300, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "To", 8000, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
'Set lblSub = CreateReportControl(rpt.Name, acLabel, _
'acPageHeader, , "-", 0, 1500, 1500)
'With lblSub
' .BorderStyle = Solid
' .BorderWidth = 1
' .BorderColor = ingBlack
'End With

' 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, 1500, 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

Cleanup:
' Cleanup all objects - close and exit form/Report
'resume next on errors
On Error Resume Next

Set rs = Nothing
rs.Close
Set rpt = Nothing
Exit Sub

'Error Handler Routine
RunReport_ErrorHandler:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description
End Select

GoTo Cleanup

End Sub

[June7]

Why are you using VBA to build a report on-the-fly? Why not a saved Access report object?

You want to create a graphic line in the report header? Reference http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[sharon.chapman7]

Thank you for the tip. I looked at the link and it looks like better code than I'm using. I'll give it a try. My goal is to show a form that lets the user select a start and stop date. Then they click run report. The form checks the data for the correct user selected date and then prints the report with the data that fits date the criteria.

Sharon

[June7]

Still don't understand why you are using VBA to build report.

Filter criteria can be passed to an Access report object several ways. One is the WHERE CONDITION argument of DoCmd.OpenReport that references controls on form.

DoCmd.OpenReport "report name", acViewPreview, ,"datefieldname BETWEEN #" & Me.tbxStart & "# AND #" & Me.tbxEnd & "#"

[sharon.chapman7]

I decided to try using the report writer to write my report. Going the other way involves too much coding.

Sharon

[sharon.chapman7]

I'm just not sure how I call the report and feed all the data into the detail section. Any suggestions? In the past I wrote all the data from the form into a table and then had the report access the table and print the data.

[June7]

Yes, data must be in a table. Set the report RecordSource to a table, query, or SQL statement. Create textboxes that are bound to fields of the RecordSource. I already gave example of one way to pass criteria to the report.

Building report is basic Access functionality and Access Help has guidelines. Also lots of tutorials on web.