?
Solved

Print Formulas

Posted on 2012-08-16
20
Medium Priority
?
316 Views
Last Modified: 2012-08-20
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?
0
Comment
Question by:Barbara69
  • 8
  • 7
  • 5
20 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38303212
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38303220
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38303233
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:Barbara69
ID: 38306737
Hi  IrogSinta,

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

Author Comment

by:Barbara69
ID: 38306741
Hi hnasr,

I also tried your code and I get nothing. Please advise.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38306784
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38306789
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
 

Author Comment

by:Barbara69
ID: 38306841
I did what you suggested and I still don't see any results.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38307120
Try this sample database.
Open form and click button.
print-formulas.accdb
0
 

Author Comment

by:Barbara69
ID: 38307146
I'm unable to open it or save it to my computer.
0
 

Author Comment

by:Barbara69
ID: 38307163
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38307224
Can you upload a copy of your database with just the report?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38307385
Try this mdb
print-formulas.mdb
0
 

Author Comment

by:Barbara69
ID: 38308233
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38308243
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38308255
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
 

Author Comment

by:Barbara69
ID: 38308413
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
 
LVL 31

Accepted Solution

by:
hnasr earned 2000 total points
ID: 38308720
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
 

Author Comment

by:Barbara69
ID: 38312270
hnasr, is there a way to get the result (the dollar amounts) into the tblformulas as well?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38313929
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

Featured Post

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.

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…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

850 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