Print Formulas

I have a report in Access 2007 and I would like to print out all the formulas used in the report without having to go through the many text boxes on the report individually and copying and pasting. How can I print out the formulas used in the entire report?
Barbara69Asked:
Who is Participating?
 
hnasrCommented:
Try this,
run the report  
code is run from report load event.
it creates a table: tblFormulas
check the table: tblFormulas.

You need to decide
where to put the code to retrieve the contol name and formula, here I put it in report load event
where to store results. Here I created a table to store results.
how to use it. From the table, you may create queries to check the formulas.
Vendor---Copy-2.accdb
0
 
IrogSintaCommented:
Run this in a module:
Sub ShowFormulas()
    Dim ctl As Control
    Dim rpt As Report
    
    DoCmd.OpenReport "NameOfReport", acViewDesign, , , acHidden
    
    Set rpt = Reports!NameOfreport
    For Each ctl In rpt.Controls
        Debug.Print ctl.Name
    Next
    Set rpt = Nothing
    DoCmd.Close acReport, "NameOfReport"
End Sub

Open in new window

0
 
hnasrCommented:
Try this: for a report named a

Private Sub Command7_Click()
    For Each ctl In Report_a.Controls  ' replace _a with _yourReportName
        If ctl.ControlType = acTextBox Then
            Debug.Print ctl.Name, ctl.ControlSource  
        End If
    Next
End Sub

Open in new window

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.

 
IrogSintaCommented:
Oops I copied the early code from a different module I was testing this on.  Here's the one I meant to post:
    Dim ctl As Control
    Dim rpt As Report
    
    DoCmd.OpenReport "NameOfReport", acViewDesign, , , acHidden
    
    Set rpt = Reports!NameOfreport
    For Each ctl In rpt.Controls
        If ctl.ControlType = acTextBox Then
            Debug.Print ctl.Name, ctl.ControlSource
        End If
    Next
    DoCmd.Close acReport, "NameOfReport"

Open in new window


Edit - Looks like hnasr has a shorter version.
0
 
Barbara69Author Commented:
Hi  IrogSinta,

I tried your code, but when I step through the code, it's skipping the Debug.Print line. Please advise.
0
 
Barbara69Author Commented:
Hi hnasr,

I also tried your code and I get nothing. Please advise.
0
 
hnasrCommented:
To see the result, code editor should be visible.
Press Alt+ F11, and run the code by clicking the button and watch the reult in the immediate window.
You can do some processing in the loop.
0
 
hnasrCommented:
To see the result, code editor should be visible.
Press Alt+ F11, and run the code by clicking the button and watch the reult in the immediate window.
You can do some processing in the loop, in place of the debug.print command and no need for code editor to be visible.
0
 
Barbara69Author Commented:
I did what you suggested and I still don't see any results.
0
 
hnasrCommented:
Try this sample database.
Open form and click button.
print-formulas.accdb
0
 
Barbara69Author Commented:
I'm unable to open it or save it to my computer.
0
 
Barbara69Author Commented:
I was able to save and open it. I opened the form and clicked on the button and pressed F8 to step through the code, but it didn't display any results.
0
 
IrogSintaCommented:
Can you upload a copy of your database with just the report?
0
 
hnasrCommented:
Try this mdb
print-formulas.mdb
0
 
Barbara69Author Commented:
hnasr, I still got nothing. I want to use the coding on a much larger report, but I'm testing the coding on the rptVendor1 report in the attached db.
Vendor---Copy.accdb
0
 
IrogSintaCommented:
Barbara,
I opened up your module called modFormulas and when I ran the code (F5 on the keyboard), I saw the control names and sources show up in the immediate window below.  So I'm not sure why you're not seeing them.
0
 
IrogSintaCommented:
I tried your code, but when I step through the code, it's skipping the Debug.Print line. Please advise.
It bypasses this part for controls that are not textboxes.  Keep stepping through and you'll see it go into this section.  Or click on F5 to run the whole code.
0
 
Barbara69Author Commented:
IrogSinta, I got it to work. I thought the immediate window would open automatically with the results. Did you try the command button on the report? It doesn't seem to be working, but since the module works, I guess it's no biggie? hnasr's button works on the db attached, but it won't work on my report. Just curious.
0
 
Barbara69Author Commented:
hnasr, is there a way to get the result (the dollar amounts) into the tblformulas as well?
0
 
hnasrCommented:
Barbara69,

Which amounts? and how you want them in the tblFormulas.
To make full use of this solution, I suggest you start a new question, uploading the database with this solution and listing the expected result. Explain which  control holds the values and how it looks in the table.

This is the content of the tblFormulas showing field name and field formula.

fieldName                           /                      fieldFoemula
--------------                           /                      ------------------
Grant                                  /                     Grant
Min-dteBudgetBeginDate  /  Min-dteBudgetBeginDate
Max-dteBudgetEndDate  /  Max-dteBudgetEndDate
Sum-numAwardedAmt  /  AwardedAmt
Sum-numApprovedAmt  /  ApprovedAmt
SumOfSumOfnumFedExpense        /  SumOfSumOfnumFedExpense
Text14                             /           =Now()
Text15                             /           "=""Page "" & [Page] & "" of "" & [Pages]"
AccessTotalsSum-numApprovedAmt  /  =Sum([ApprovedAmt])
AccessTotalsSum-numAwardedAmt  /  =Sum([AwardedAmt])

Where and how and from which field to add the required amount? This info is required when you start the new question.
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.