Updating control source for report header textbox (1 Viewer)

JoeBruce · Oct 6, 2018

Howdy -

I have several dynamic reports which use a form to filter the results. One such filter is the date range of the report. I set up a nifty way so the top of the report displays the date range entered (or a default date range if dates are not used in the filter). However, once I close that filter form, the dates in the report header no longer work (because they were pulling from the opened form).

Is there a way I can store those dates differently, so they can still be used after the form closes? Is there a better way to set the control source for the header textbox? I played with a few options yesterday but could not figure it out. Some code below:

Current code that sets the control source: (Before yesterday, I just had this entered directly as the control source in the properties of the report.)

Code:

Reports!rptProgramStatsAll.Header_Label.ControlSource = "='Program Statistics between ' & GetStartAll() & ' and ' & GetEndAll()"

Function referenced in the above code ("GetEndAll" is similar):

Code:

Public Function GetStartAll()    

    If CurrentProject.AllForms("frmFilterProgStats").IsLoaded Then
        If IsNull(Forms!frmFilterProgStats.[StartDate]) Then
            GetStartAll = GetFirstDate()
        Else
            GetStartAll = Forms![frmFilterProgStats]![StartDate]
        End If
    Else
    'I had some code here, but deleted it so now it just returns nothing if the form is not loaded.    
    End If
            
End Function

One of my other efforts, which creates a string when the "Apply Filter" button is pressed on the filter form, and then attempts to use that string to set the control source. However, I just get one of those "enter parameter" boxes pop up when I do this. Maybe I'm missing something in the syntax? Or is it not possible to use a string like this in my control source?

Code:

strHeaderLabel = "='Program Statistics between ' & strStartDate & ' and ' & strEndDate"

Reports!rptProgramStatsAll.Header_Label.ControlSource = strHeaderLabel

Thanks for any input. I am not a coder whatsoever and most of the VBA I use was taken from various sources, so please include example codes or detailed explanations of what those commands and whatnot all mean!

isladogs · Oct 6, 2018

In many ways, you were on the right lines BUT ...
Labels don't have a control source - they have a caption.
Also you should have used double rather than single quotes

One way of doing this is as follows:

1. In a standard module, create a global / public variable

Code:

Public strDateRange As String

2. On your form e.g. on the cmdPrint_Click event add this line:

Code:

strDateRange = "Program Statistics between " & Me.StartDate & " and " & Me.EndDate

3. In your report, add this code to the format event of the section where your label is located e.g. ReportHeader_Format event

Code:

Me.Header_Label.Caption=strDateRange

If you do it like this you don't need any of your other code

Others may suggest using a function e.g.

Code:

Public Function GetDateRange()
   GetDateRange = strDateRange
End Function

If so then

Code:

Me.Header_Label.Caption=GetDateRange()

JoeBruce · Oct 6, 2018

Thanks for the tips, isladogs.

I will test this out a little bit but it might be a few days before I get around to it. One follow up to your post though: despite the fact that it is called "Header_Label" it is actually a text box (forgive the bad naming convention). So it does have a control source.

Would this work more smoothly if I switched it to a label?

EDIT: Okay, I had a few minutes and made suggested changes. Also, I think the answer to my question immediately above is "yes."

I still have a little refining to do, but initial results are good. Thanks for the help!

isladogs · Oct 6, 2018

In my opinion yes. A label caption loads much faster than using a textbox.

I've used code similar to this for years.
It's also very adaptable for more complex situations where you filter for several criteria
Notice how concise the code is compared to yours

Updating control source for report header textbox (1 Viewer)

JoeBruce

Registered User.

isladogs

MVP / VIP

JoeBruce

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread