Dynamically change object in Access 2007 Report

Found out that in Access 2003, a report's TEXT boxes control source and other settings CANNOT be changed by using code inside the report - it must be in a function

I could do this in Access 2003, but doesn't seem to work with Access 2007.


Before the report "Summary - pg1" is opened, I call the following function:

Function SummaryReportHdr(town)
     Reports![Summary - pg1]![Text222].ControlSource = "='For: ' & town"
End Function

Now, I get a error: Run-Time error '2451'
The report name 'Summary - pg1' you entered is misspelled or refers to a report that isn't open or doesn't exist.

How can I programmatically change a report's textbox's ControlSource WITHOUT opening it?
shelbyinfotechAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I have a report based on a query, but only the header text is changed.  Instead of creating 9 almost exact same querys and reports, I created one query/report and programmatically changed the query/report 9 times.>>

  I would leave the report based on the query and include the required header text in the query.  Then before doing the report with output to, I would modify the SQL of the query:

  Dim db as DAO.Database
  Dim qdf as DAO.querydef

  Set db = CurrentDB()
  Set qdf = db.OpenQuerydef("<my query>")
 
  strSQL = "SELECT *, 'Summary - pg1' as HeaderTitle FROM..."
  qdf.SQL = strSQL
 
  qdf.Close
  Set qdf = nothing
  Set db = nothing

 DoCmd.OutputTo acOutputReport, "Summary - pg1", "PDF", FullPath

  and I've never checked so I'm not 100%, but I would be amazed if the OnOpen event didn't fire for the report with the OutputTo action.

JimD.


 
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<How can I programmatically change a report's textbox's ControlSource WITHOUT opening it? >>

  You can't.  In fact I don't understand how you managed that in 2003.

  Generally any changes you want to make in a report you do in the OnOpen event.

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
...are you quite sure you have the name spelled correctly...?

Summary - pg1
Summary-pg1
Summary - page1
Summary  - pg1
Sumary - pg1
...etc...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
Was this a temp/runtime change to the report?
I can't see how this would get changed...

In other words, what the reason for doing this in the first place?


...besides, what would be the disadvantage to opening the report....
0
 
shelbyinfotechAuthor Commented:
I have to create a report based on 9 exactly structured tables.

I have a report based on a query, but only the header text is changed.  Instead of creating 9 almost exact same querys and reports, I created one query/report and programmatically changed the query/report 9 times.

If I created 1 query and 1 report, that's 18 more objects to deal with.  And I have another report that I have to do the same thing to.  That's 36 more objects.  I'd rather have 2 querys and 2 reports that are modified via code and printed 18 times.

The report is printed to PDF automatically with no user intervention to screw it up.

Calling a function:
DoCmd.OutputTo acOutputReport, "Summary - pg1", "PDF", FullPath

... does not seem to trigger any report events - or I would put the code to change the header in the report's code

I can always programmtically open the report, make the change, close the report, and then output the report to PDF - but that feels clunky to me.  It was much easier when I could change the report's text directly.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW,

Where you calling this:

Function SummaryReportHdr(town)
     Reports![Summary - pg1]![Text222].ControlSource = "='For: ' & town"
End Function

 from your query in 2003?

JimD.
0
 
shelbyinfotechAuthor Commented:

>>>>
Where you calling this:

Function SummaryReportHdr(town)
     Reports![Summary - pg1]![Text222].ControlSource = "='For: ' & town"
End Function

 from your query in 2003?
>>>>
No, I was calling it from a Macro that was triggered when the user hit the "print report" button on the form
0
 
shelbyinfotechAuthor Commented:
>>>
  I would leave the report based on the query and include the required header text in the query.  Then before doing the report with output to, I would modify the SQL of the query:

  Dim db as DAO.Database
  Dim qdf as DAO.querydef

  Set db = CurrentDB()
  Set qdf = db.OpenQuerydef("<my query>")
 
  strSQL = "SELECT *, 'Summary - pg1' as HeaderTitle FROM..."
  qdf.SQL = strSQL
 
  qdf.Close
  Set qdf = nothing
  Set db = nothing

 DoCmd.OutputTo acOutputReport, "Summary - pg1", "PDF", FullPath

  and I've never checked so I'm not 100%, but I would be amazed if the OnOpen event didn't fire for the report with the OutputTo action.

>>>>
I'd rather create a "Global Variable", Set the report's header text to the value of the variable, and then set it's value in a macro or programmatically BEFORE outputting the report.  Still kinda kludgy....

The OnLoad, OnActivate, OnCurrent, and OnGotFocus are not triggered when using DoCmd.OutputTo.
0
 
Nick67Commented:
<Found out that in Access 2003, a report's TEXT boxes control source and other settings CANNOT be changed by using code inside the report - it must be in a function>

<gronk>
This works perfectly fine in my reports in Access 2003
 
Private Sub Report_Open(Cancel As Integer)
If Nz(Me.OpenArgs, False) = "Final" Then
    Me.RecordSource = "qryEngRevenueFinal"
    Me.txtEngRevenue.ControlSource = "EngRevenue"
    Me.txtTotalEngRevenue.ControlSource = "=FinalStats()"
Else
    Me.RecordSource = "pthrEngRevenueRawByMonth"
    Me.txtEngRevenue.ControlSource = ""
    Me.txtTotalEngRevenue.ControlSource = "=TotalEngRevenueAndStats()"
End If

Me.Caption = "EngRevenue" & Forms!frmEngRevenue!StartDate & "to" & Forms!frmEngRevenue!EndDate

end sub

Open in new window

So the idea that you cannot change control sources inside the report in Access 2003 is demonstrably false
</gronk>

Now
<DoCmd.OutputTo acOutputReport, "Summary - pg1", "PDF", FullPath>
Perhaps an OutputTo is different than a preview or print event
Try
DoCmd.OpenReport "Summary - pg1", acPreview, , ,acHidden
DoCmd.OutputTo acOutputReport, "Summary - pg1", "PDF", FullPath
DoCmd.Close acReport, "Summary - pg1",  acSaveNo


0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<No, I was calling it from a Macro that was triggered when the user hit the "print report" button on the form >>

  You must have been opening it in someway before that call then.  You can't change a closed object.

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Private Sub Report_Open(Cancel As Integer)
If Nz(Me.OpenArgs, False) = "Final" Then
    Me.RecordSource = "qryEngRevenueFinal"
    Me.txtEngRevenue.ControlSource = "EngRevenue"
    Me.txtTotalEngRevenue.ControlSource = "=FinalStats()"
Else
    Me.RecordSource = "pthrEngRevenueRawByMonth"
    Me.txtEngRevenue.ControlSource = ""
    Me.txtTotalEngRevenue.ControlSource = "=TotalEngRevenueAndStats()"
End If

Me.Caption = "EngRevenue" & Forms!frmEngRevenue!StartDate & "to" & Forms!frmEngRevenue!EndDate

end sub
>>

 So:

a. the report is open

b. Why are you messing with the controlsource?  Just leave it unbound and set the value directly:


    Me.txtEngRevenue = "EngRevenue"
    Me.txtTotalEngRevenue = FinalStats()

JimD.

0
 
shelbyinfotechAuthor Commented:
>>>
<<No, I was calling it from a Macro that was triggered when the user hit the "print report" button on the form >>

  You must have been opening it in someway before that call then.  You can't change a closed object.

JimD.
>>>
Well...been working that way for over a year with Access 2003.  Maybe it was a flaw that I was able to capitalize on and they "fixed" it in 2007
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW on this:

<<I'd rather create a "Global Variable", Set the report's header text to the value of the variable, and then set it's value in a macro or programmatically BEFORE outputting the report.  Still kinda kludgy....>>

 I do that all the time.  in the past, reports did not have an OpenArgs, so that was the only way to pass in data.

  I had a global array, with Get/put functions.  I'd place all the values, call the report, and the report would call GetParam(x).

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

<<Well...been working that way for over a year with Access 2003.  Maybe it was a flaw that I was able to capitalize on and they "fixed" it in 2007 >>

  No, you were doing it in the Open event.  Calling the code there, the report is already open.  As I said, I would have been shocked if OnOpen was fired even with the output to action.

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and yes, 2010 did tighten up on a lot of things that you used to be able to do, but should not have been able to, and now can no longer.

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<No, you were doing it in the Open event.  Calling the code there, the report is already open.  As I said, I would have been shocked if OnOpen was fired even with the output to action.>>

 Make that "not fired"

JimD.
0
 
Nick67Commented:
@JimD.

Don't mix my code posting with the author's.
I change stuff in OnOpen --> therefore I know it can be done, despite the author's assertion to the contrary.
As for why I don't leave it unbound entirely, a default is a handy mnemonic

Nick67
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Oops, guess I did and I was confused enough already<g>.

Here's the arg passing code I use.

JimD.
Private arrParameter(10)

Public Sub SetParam(ByVal InputVal, ByVal ParamID)

    arrParameter(ParamID) = InputVal

End Sub
Public Function GetParam(ByVal ParamID)

    GetParam = arrParameter(ParamID)

End Function

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
Then if I am understanding your situations here, I would simply add a "Header Text" field to the tables.
Then add this field to the Report as the "Header" (whatever this means in your Report)
Then I would change the report RecordSource for each table...

Most Experts here agree that making changes to your objects at runtime can cause issues with your DB becoming uncompiled.


On the other hand, if you must take this approach and your system does not work, ...and opening the report does work....
hmmmmmm
;-)

0
 
Nick67Commented:
<The OnLoad, OnActivate, OnCurrent, and OnGotFocus are not triggered when using DoCmd.OutputTo. >

Most of those don't exist for reports in Access 2003!
Forms, yes
Reports, no
0
 
Nick67Commented:
It's been fast and furious!
<No, I was calling it from a Macro that was triggered when the user hit the "print report" button on the form >

NOW, we get to the heart of the matter!
I don't think any of us has a clue what goes on in the black heart of a macro.
And what MS may have changed in macro execution between versions.

I don't think any of us responders are macro dudes, but post your macro anyway.
We are all thinking that your macro is doing something like
'open the report in preview
'execute some functions
'outputto pdf
'close the report

Because unless the report is open in preview or design, or you have code in the OnOpen event, you CAN'T change the properties in our experience
0
 
Nick67Commented:
You were saying you had 9 reports and queries
Now, the OnOpen can and does change RecordSource and ControlSource.
Pass in an OpenArgs and have a Select  Case Structure

Private Sub Report_Open(Cancel As Integer)
select case Me.OpenArgs
    case "MyTown"
        me.recordsource = "Select * from sometable where " & me.openargs
        Me.[Text222].ControlSource = "For:" & me.openargs
    'all your other cases
    ............
    case else
        me.recordsource = "Select * from sometable;"
        Me.[Text222].ControlSource = "For: All"
end select

Me.Caption = "Summary for " & iif(nz(me.openargs,"")="","all",me.openargs)

end sub

It WILL work, and be fairly elegant.
0
 
shelbyinfotechAuthor Commented:
Finally got it working...

Macro line:
RunCode ExportReports("A")

Function ExportReports(TownCode)

    Select Case TownCode
        Case "A" 'export tbl Town_Data_Export
            DoCmd.OutputTo acOutputReport, "Summary - pg1", acFormatPDF, "C:\Certs\Town\Town.pdf"
            MsgBox "Town Report Exported"
        Case Else
            MsgBox "Invalid 'Towncode' Check 'Export Cert Rpts' Macro"
    End Select

End Function

This function DOES trigger the Report's  OnOpen event:

Private Sub Report_Open(Cancel As Integer)
    Call SummaryReportHdr(DLookup("Town", "TownRpt"))
End Sub

Function SummaryReportHdr(town)

    Select Case town
        Case "1"
            Reports![Summary - pg1]![Label7].Caption = "For:Town1"
        Case "2"
            Reports![Summary - pg1].[Label7.Caption = "For: Town2"
        Case "3"
            Reports![Summary - pg1].Label7.Caption = "For: Town3"
        etc....

        Case Else
            MsgBox "TownCode not valid"
    End Select

End Function
0
 
Nick67Commented:
Glad you got it working!

Nick67
0
 
shelbyinfotechAuthor Commented:
Since it was a combination of experts that solved my problem, I used my final comment to provide other readers and the experts involved in the discussion an overview of the complete solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.