VBA to Change Report Header - Inside the report? 1

[MattGreer]

I have a database that I use to schedule musical performances. I go through and select a list of songs and then output a report to send to the band members.

Inside the report, in the Report Header, I have a text box with large text that has the date of the performance. I have to manually go in and change that date whenever I print the report.

I've currently devised a way to print the report to PDF in a directory so I can just one-click my way to the report:

Private Sub cmdPrintSongSet_Click()
On Error GoTo Err_cmdPrintSongSet_Click

    Dim stReportName As String
    Dim stDocName As String
    Dim stDate As String

    stReportName = "Song Set"
    stDate = InputBox("Please enter the date of the performance...", "Date of Performance", "2021-01-01")
    stDocName = "C:\OneDrive\Documents\Band Song Sets\" & stDate & " Song Order.pdf"
    
    DoCmd.OutputTo acOutputReport, stReportName, acFormatPDF, stDocName

Exit_cmdPrintSongSet_Click:
    Exit Sub

Err_cmdPrintSongSet_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintSongSet_Click

End Sub

Is there a way I could use VBA to take the date I enter in the InputBox and change the Report Header text accordingly?

Thanks!!


Matt

 

[MattGreer]

OK, I figured this out. Just need to search and search and search some more, heh.

Seems like you can't edit a report until it's been opened, and you can't edit a report unless it's opened in Design view, so the trick is to open the report in design view, make the change, and then close it. THEN run the printing routine.

So something like this:

    DoCmd.OpenReport stReportName, acViewDesign
    
    Reports(0)!ReportHdr.Caption = "Performance Date - " & stDate
    
    DoCmd.Close acReport, stReportName, acSaveYes

Setting the Window Mode to "acHidden" will prevent screen flashes. At first it didn't work but I was opening it in acViewNormal so perhaps that had something to do with it.

Thanks!!


Matt

 

[dhookom]

Did you attempt to simply change the text box value in the On Format event of the section containing the text box? Also rather than prompt the user for a date, I would add a text box txtReportDate on your form for the user to enter the date. Then your vba can reference the date control like:

stDocName = "C:\OneDrive\Documents\Band Song Sets\" & Me.txtReportDate & " Song Order.pdf"

You should be able to use the same txtReportDate to supply the text box in the report.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

No, I didn't try that. Didn't even know the 'On Format' event existed. Seems like that would pop up every time I opened the report manually and I really wouldn't want that. If I'm opening and editing the report manually, I cant foresee the need for an automatic date entry; HOWEVER, I'll tuck that little nugget away for a rainy day.

One question, somewhat related. How does Access know what "Me" is? I ask because the button that triggers the code is sitting in a separate form, so wouldn't "Me" refer to the form, and not the report? The form is open, and the VBA to open the report is part of the form.

Thanks!!


Matt

 

[dhookom]

I just noticed you stated "I have a text box with large text" but your code references a Caption "Reports(0)!ReportHdr.Caption". Text boxes don't have caption properties. Are we to assume the control is actually a Label control rather than and Text Box?

The first post had code:

Private Sub cmdPrintSongSet_Click()

Which I assume was run from a form that should host the date control. I wouldn't assume the users would enter the correct date or format which is possible with an InputBox().

You could even create a column in the reports record source with a field property like
ReportDate:[Forms]![frmYourFormNameHere]![txtReportDate]

Then just use this as the control source for a text box.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

Man, I love Access. There's so many ways to accomplish a task, so flexible, but with the help from you and others I'm always finding simpler ways to get the job done. I used to know so much more but went 10 years without really using it.

You're absolutely right of course, it's a Label, not a text box.

At this point in time I'm the only user, and I entered some default text in the Input box to remind me of the correct format: yyyy-mm-dd so I keep consistent. As you suggested, a big improvement would be to call up a calendar control to enter a date into the form; fantastic suggestion. That would streamline things a decent amount to have that sitting on the form already. Thank you for that suggestion!

Thanks!!


Matt

 

[MattGreer]

Hey Duane, I searched but haven't found anything quite yet. Do you know how to use VBA to get a calendar control? There's another thing I'm working on in this database, and when I open the report (directly, not from a button) I want a calendar to pop up asking me, the user, for a date. Is there a native way in Access 365, or will I need to find an alternative way out there on the Interwebs?

Thanks!!


Matt

 

[dhookom]

I'm not sure why you don't use a date on the form that opens the report. I believe the only way to use a calendar control is on a form.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016