We help IT Professionals succeed at work.

Set value of unbound textbox in Report header in Access 2007

deeburton
deeburton used Ask the Experts™
on
I have a report that has 3 parameters to be entered when running the report: start date, end date, and an option to enter 1-7 to pull data for a specific utility. I can get the dates to appear on the report header just fine. The specific utility data option returns a value of 1-7 and I want to run code so that if the value is 1 then the unbound field shows ABC or if the value is 2 it shows XYZ, etc. I have an Event Procedure in the report OnFormat that does what I want, but when the report is viewed in Access 2007 it doesn't show up. This works just fine if I open the report in Access 2003, but not in Access 2007. Obviously, something has changed in how this works and I'd appreciate someone helping me figure out what I need to do differently.

I have the Utility field captured in the Report Header section, but not visible. Then I have the following code in the OnFormat of the Report Header:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

If Reports!UtilityRequests_rpt!Utility = "1" Then
        Me.ElecUtil = "Progress Energy"
ElseIf Reports!UtilityRequests_rpt!Utility = "2" Then
        Me.ElecUtil = "Duke Energy"
ElseIf Reports!UtilityRequests_rpt!Utility = "3" Then
        Me.ElecUtil = "Electric Cooperatives"
End If

End Sub

ElecUtil is the name of the unbound textbox and Utility is the field name that has the value of 1-7 in it which is captured in the Report Header, but not visible.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If the db was created in A 2003 then opened in A 2007, first try to decompile and recompile the db in A 2007.

1) ALWAYS CREATE A BACKUP OF THE DATABASE BEFORE DECOMPILING. (Just in case)

2) decompile: in run: msaccess "dbPathName.mdb" /decompile
   Compact/repair:  in run: msaccess "dbPathName.mdb" /compact
   compile: in VB editor: debug> compile
   Compact/repair
   http://www.granite.ab.ca/access/decompile.htm

Author

Commented:
I followed these steps, but the report still does not work as expected in Access 2007. It did reduce the size of the database by compacting it, but didn't fix my problem.
<Utility field captured in the Report Header section>
What do you mean by "captured"?

<but not visible>
Is it's visible property set to "No"? You are not changing that in the code sample.

Try moving the code to the Open or activate events.

Just throwing things out to try.

Author

Commented:
I moved the code to the OnPrint event and it works when you actually print preview the report, but not when you just view it. So then I moved the code to the OnLoad event and it works like a charm.  Thanks so much for your help!
Go figure. My standard statement for these kinds of problems:

One time I picked up an aircraft radio after a repair, I asked the tech, "What was wrong with it?"  He replied, "Probably something in the FM circuit."  Confused, I asked,"Don't aircraft radios use AM?"  He smiled and replied, "The 'M' stands for magic."

You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson