Hello
Thanks to MajP and Duane last year I was able to create a report made up of multiple subreports in Access 2003. It was more difficult than expected because the report had to be hardcoded for 12 months i.e. if the start date on the dialog box was 9/1/2011 then that was the first data in the report and the other months followed for a total of 12 months.
The parameters for the report were based on user input on a form (frmReportDialog).
On the On Open event of the subreport is the following code for the labels:
CODEPrivate Sub Report_Open(Cancel As Integer)
startDate = [Forms]![frmReportDialog]![txtStartDate]
Me.RecordSource = "qryALCDays_C2"
Me.lblM1.Caption = Format(startDate, "MMM")
Me.lblM2.Caption = Format(addMonth(startDate, 1), "MMM")
Me.lblM3.Caption = Format(addMonth(startDate, 2), "MMM")
Me.lblM4.Caption = Format(addMonth(startDate, 3), "MMM")
Me.lblM5.Caption = Format(addMonth(startDate, 4), "MMM")
Me.lblM6.Caption = Format(addMonth(startDate, 5), "MMM")
Me.lblM7.Caption = Format(addMonth(startDate, 6), "MMM")
Me.lblM8.Caption = Format(addMonth(startDate, 7), "MMM")
Me.lblM9.Caption = Format(addMonth(startDate, 8), "MMM")
Me.lblM10.Caption = Format(addMonth(startDate, 9), "MMM")
Me.lblM11.Caption = Format(addMonth(startDate, 10), "MMM")
Me.lblM12.Caption = Format(addMonth(startDate, 11), "MMM")
End Sub
Note that addMonth is a function:
CODEOption Compare Database
Option Explicit
Public startDate As Date
Public Unit As String
Public Function getFirstOfMonth(dtmDate As Variant) As Variant
If Not IsNull(dtmDate) Then
getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)
End If
End Function
Public Function addMonth(dtmDate As Variant, MonthsToAdd As Integer) As Variant
Dim I As Integer
If Not IsNull(dtmDate) Then
addMonth = getFirstOfMonth(dtmDate)
addMonth = DateAdd("M", MonthsToAdd, addMonth)
End If
End Function
Public Function getStartDate()
Dim startDate As Date
startDate = Nz(Forms("frmReportDialog").txtStartDate, 0)
If startDate = 0 Then
getStartDate = getFirstOfMonth(Date)
Else
getStartDate = getFirstOfMonth(startDate)
End If
End Function
This worked well when the report was in Access 2003 but now there are two subreports that don't print these labels for all months. There is data for these months but the labels don't show up and it's not always the same month label that isn't visible.
Can anyone suggest what the issue might be or where I should look? I've already ensured that each subreport has the same properties and they are all the same so it isn't that. Thanks very much.
|
|
Talk To Other Members
Read More Threads Like This One