use vba to create a report with a line in the Page Header section

[sharonchapman7]

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