How to display parameter dates on a report

[Jeff H]

I’m opening a report from a parameter form where the user selects the months to display (always full calendar months).

I’m trying to pass a label to unbound textboxes on the report to show what the parameters are. But I’m finding that the unbound textboxes will show up sometimes and not other times in report view and/or print preview. And I have tried the Display When property. (I haven’t tried actually printing.)

It worked fine for a single month when I was using a field in the underlying query to format the parameter month. I just bound the textboxes to that field in the query. But now it’s a more complicated format because if the Start and End months are the same (i.e. reporting 1 month) it should be “mmmm yyyy” but if they are different it should be “mmm-yyyy to mmm-yyyy”.

So the method I ended on constructs the appropriate report label right on the parameter form, then I pass that value to the unbound textboxes in the report header and page footer. Unfortunately I’m still getting the same results.

Here’s the code I’m using in the parameter form:

Code: Select all

sRptLabel = Me.txtReportLabel
sWhere = "SvcMonth Between " & dStart & " And " & dEnd
DoCmd.OpenReport ReportName:="rptMonthly", View:=acViewReport, _
    WhereCondition:=sWhere
Reports!rptMonthly.txtLabel = sRptLabel
Reports!rptMonthly.txtFooterLabel = sRptLabel
Reports!rptMonthly.Requery
DoCmd.Close acForm, Me.Name

[HansV]

You can pass the text to be displayed in the OpenArgs argument of DoCmd.OpenReport:

Code: Select all

sRptLabel = Me.txtReportLabel
sWhere = "SvcMonth Between " & dStart & " And " & dEnd
DoCmd.OpenReport ReportName:="rptMonthly", View:=acViewReport, _
    WhereCondition:=sWhere, OpenArgs:=sRptLabel
DoCmd.Close acForm, Me.Name

Then use OpenArgs in the On Open event of the report:

Code: Select all

Private Sub Report_Open(Cancel As Integer)
    If Not IsNull(Me.OpenArgs) Then
        Me.txtLabel = Me.OpenArgs
        Me.txtFooterLabel = Me.OpenArgs
    End If
End Sub

When you open the report by itself, OpenArgs will be Null, so the 'labels' will remain empty.

[Jeff H]

Well, I think I put this in correctly, but I'm getting the error "you can't assign a value to this object". I will play with it a bit and get back to you. I've got to do some other things right now.

Thanks,
- Jeff

[HansV]

Sorry, my mistake - copy/paste error. I will correct my previous reply.

[Jeff H]

Yes, I see that makes more sense, but I'm still getting the same error.

"OpenArgs" is showing the correct value when the procedure crashes, but I don't see any reason the textboxes should be blocked. I looked through all the properties and didn't see anything that seemed to exclude data.

[HansV]

Alternatively: remove the On Open event procedure, and change the Control Source of the text boxes to

=[Forms]![NameOfYourForm]![txtReportLabel]

where NameOfYourForm is the name of the form from which you open the report.

[Jeff H]

Yes, that seems to be working. That was one of the things I tried earlier, but I notice now that the parameter form has to remain open for it to work. So I think I can hide it instead of closing it. I'll test that out tomorrow.

Thanks again, Hans.

[Jeff H]

It seems a little crazy that I can't just populate a textbox, but changing a label's caption seems to work. Here's the code. Am I missing something here, or does this seem like a reasonable solution?

Code: Select all

sRptLabel = Me.txtReportLabel
sWhere = "SvcMonth Between " & dStart & " And " & dEnd

DoCmd.OpenReport ReportName:="rptMonthly", View:=acViewReport, _
    WhereCondition:=sWhere
With Reports!rptMonthly
    .lblPeriod1.Caption = sRptLabel
    .lblPeriod2.Caption = sRptLabel
    .Requery
End With
DoCmd.Close acForm, Me.Name

[HansV]

That was one of the other things I was going to suggest.

[Jeff H]

I must admit I'm still curious about why OpenArgs doesn't work. It seems like it's made for the job. But anyway, the labels will do quite nicely.

[HansV]

If you attach a zipped copy of the database (or send it to me) I will take a look.

[Jeff H]

Thanks, Hans, but I don't want to abuse your generosity. I greatly appreciate having you available to get me unstuck and to show me the right way to do things. In this case, the labels do just what I need.