?
Solved

Dynamically change object in Access 2007 Report

Posted on 2011-05-09
25
Medium Priority
?
797 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:shelbyinfotech
  • 10
  • 6
  • 6
  • +1
25 Comments
 
LVL 58
ID: 35721975
<<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35722276
...are you quite sure you have the name spelled correctly...?

Summary - pg1
Summary-pg1
Summary - page1
Summary  - pg1
Sumary - pg1
...etc...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35722316
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:shelbyinfotech
ID: 35722606
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
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1200 total points
ID: 35722867
<<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
 
LVL 58
ID: 35722879
BTW,

Where you calling this:

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

 from your query in 2003?

JimD.
0
 

Author Comment

by:shelbyinfotech
ID: 35722930

>>>>
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
 

Author Comment

by:shelbyinfotech
ID: 35722983
>>>
  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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 800 total points
ID: 35722986
<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
 
LVL 58
ID: 35722988
<<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
 
LVL 58
ID: 35723021
<<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
 

Author Comment

by:shelbyinfotech
ID: 35723024
>>>
<<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
 
LVL 58
ID: 35723051
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
 
LVL 58
ID: 35723067

<<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
 
LVL 58
ID: 35723089
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
 
LVL 58
ID: 35723101
<<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
 
LVL 26

Expert Comment

by:Nick67
ID: 35723124
@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
 
LVL 58
ID: 35723138
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35723146
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
 
LVL 26

Expert Comment

by:Nick67
ID: 35723149
<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
 
LVL 26

Expert Comment

by:Nick67
ID: 35723233
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
 
LVL 26

Expert Comment

by:Nick67
ID: 35723400
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
 

Assisted Solution

by:shelbyinfotech
shelbyinfotech earned 0 total points
ID: 35730408
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
 
LVL 26

Expert Comment

by:Nick67
ID: 35730570
Glad you got it working!

Nick67
0
 

Author Closing Comment

by:shelbyinfotech
ID: 35763140
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question