Change Report and/or Page Header with Filter

[sbdeaver]

I have a macro that allows for an option frame to filter my report in different ways (Company, Northern, Southern & Western divisions). I would like to have the filter be 'applied' to the report and/or page header to let people know which report has been printed. Does anyone have suggestions on how to do this? I'm thinking it would need to be VB coded, but I'm stuck as to how.

Thanks,
Sharon

 

[THWatson]

Sharon
There are a number of ways to do this. It depends upon how you are populating the report.

Since you are using an option group, you might do this...
1. In your form, put an unbound text box.
2. On the After Update event for the option group, put VBA code that fills the text box with the corresponding report name that you want, depending upon what option you selected. Let's call the text box txtChoice. Make that text box invisible so that it doesn't appear on the form.
3. In the report header, put an unbound text box. Put the control source for this text box as...
=Forms!YourFormName!txtChoice

Hope that helps.

Tom

 

[sbdeaver]

Tom,

Thanks. I think that will work, but I'm not a pro at VBA coding. Could you give me an example of what I should put in the After Update event that would fill the text box?

Thanks,
Sharon

 

[THWatson]

Sharon
Here's an example. Change things to suit your own option group name, etc.

Say you have an Option Group that is called "Frame0" and it has 3 selections - apples, tomatoes, beets. On the AfterUpdate event for the Option Group, you would put...

Private Sub Frame0_AfterUpdate()
Select Case Frame0
    Case Is = 1
    Me.txtChoice = "apples report"
    Case Is = 2
    Me.txtChoice = "tomatoes report"
    Case Is = 3
    Me.txtChoice = "beets report"
End Select
End Sub

Hope that helps.

Tom

 

[sbdeaver]

Tom,

The coding worked perfectly!

Thanks for your help.

Sharon