By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Adding a calculated control to an auto-generated report using input from user

P: 23
Let me preface this by saying I completely understand and acknowledge that the way this database was built is not efficient. However, my office wants to keep this setup.

I have a reports form (frmReports2) to generate reports based on information selected by the user. frmReports2 also contains a multi-value field list box to choose among specific fields to add to the report. All this works fine. My problem, however, is that 9 of the fields contain financial values. If the user selects one or more of these fields (FY1415, FY1516, FY1617, FY1718, FY1819, FY1920, FY2021, FY2122, FY2223) for the report, I need to have a calculated control on the report for each record that will sum up the values of the fields the user chose to populate on the report. So if the user chose to show values of FY1415, FY1516, FY1617 and FY1718, I would need to add those to the report (which is already done) but also add an additional field to calculate the sum of those (in this instance, 4) fields that the user chose.

The code below takes all of the user input and creates the SQL string. This all works properly.

Expand|Select|Wrap|Line Numbers
  1.     Dim filterContract As String
  2.     Dim filterCounty As String
  3.     Dim filterPriority As String
  4.     Dim filterFields As String
  5.     Dim strFilter As String
  6.     Dim strSQL As String
  7.     Dim i As Integer
  8.     Dim idx As Variant
  9.     Dim lst As Access.ListBox
  10.     Dim andor As String
  11.  
  12.  
  13.     'Put code here to make it an AND or OR
  14.     If Me.optAndOr = 1 Then
  15.         andor = " AND "
  16.     Else
  17.         andor = " OR "
  18.     End If
  19.  
  20.     'Check for contract entry
  21.     Set lst = Me.lstContractType
  22.     For Each idx In lst.ItemsSelected
  23.         If filterContract = "" Then
  24.             filterContract = "'" & lst.ItemData(idx) & "'"
  25.         Else
  26.             filterContract = filterContract & ", '" & lst.ItemData(idx) & "'"
  27.         End If
  28.  
  29.     Next idx
  30.  
  31.     If filterContract <> "" Then
  32.         filterContract = "ContractType IN (" & filterContract & ")" & andor
  33.     End If
  34.  
  35.     'Check for counties section
  36.     Set lst = Me.lstCounties
  37.     For Each idx In lst.ItemsSelected
  38.         If filterCounty = "" Then
  39.             filterCounty = "'" & lst.ItemData(idx) & "'"
  40.         Else
  41.             filterCounty = filterCounty & ", '" & lst.ItemData(idx) & "'"
  42.         End If
  43.     Next idx
  44.  
  45.     If filterCounty <> "" Then
  46.         filterCounty = "County.value IN (" & filterCounty & ")" & andor
  47.     End If
  48.  
  49.  
  50.     'Check for priority category
  51.     Set lst = Me.lstPriorityCategory
  52.     For Each idx In lst.ItemsSelected
  53.         If filterPriority = "" Then
  54.             filterPriority = "'" & lst.ItemData(idx) & "'"
  55.         Else
  56.             filterPriority = filterPriority & ", '" & lst.ItemData(idx) & "'"
  57.         End If
  58.     Next idx
  59.  
  60.     If filterPriority <> "" Then
  61.         Debug.Print filterPriority
  62.         filterPriority = "PriorityCategory.value IN (" & filterPriority & ")" & andor
  63.     End If
  64.  
  65.     Set lst = Me.lstFields
  66.     For Each idx In lst.ItemsSelected
  67.         If filterFields = "" Then
  68.             filterFields = lst.ItemData(idx)
  69.             temptotals = lst.ItemData(idx)
  70.             strSQL = "SELECT * from tblmain "
  71.         Else
  72.             filterFields = filterFields & ", " & lst.ItemData(idx)
  73.             temptotals = temptotals & " + " & lst.ItemData(idx)
  74.         End If
  75.     Next idx
  76.  
  77.     If filterFields <> "" Then
  78.         Debug.Print filterFields
  79.  
  80.         strSQL = "SELECT PayeeName, ContractNum, ContractType, County, PriorityCategory, " & filterFields & " FROM tblmain "
  81.  
  82.  
  83.     End If
  84.  
  85.     strFilter = filterContract & filterCounty & filterPriority
  86.     If strFilter <> "" Then
  87.         strFilter = " WHERE " & strFilter
  88.         strFilter = Left(strFilter, Len(strFilter) - Len(andor))
  89.         strSQL = strSQL & strFilter
  90.     End If
  91.         Debug.Print strSQL
  92.  
  93.     CreateAutoReport (strSQL)
  94.  
  95. End sub
The code below is the code that twinnyfo provided for my auto report:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private appAccess   As New Access.Application
  5.  
  6. Public Sub CreateAutoReport(strSQL As String)
  7.     Dim rpt         As Access.Report
  8.     Dim rptReport   As Access.Report
  9.     Dim strCaption  As String
  10.     Dim lblReport   As Access.Label
  11.     Dim txtReport   As Access.TextBox
  12.  
  13.     CurrentDb.QueryDefs("qryDummy").SQL = strSQL
  14.     'Set appAccess = CreateObject("Access.Application")
  15.     appAccess.OpenCurrentDatabase (CurrentDb.Name)
  16.  
  17.     ' Open dummy query to invoke NewObjectAutoReport command on it
  18.     ' Put the report created to design view to make properties editable
  19.     With DoCmd
  20.         .OpenQuery "qryDummy", acViewNormal
  21.         .RunCommand acCmdNewObjectAutoReport
  22.         .Close acQuery, "qryDummy"
  23.         .RunCommand acCmdDesignView
  24.     End With
  25.  
  26.     ' Get reference to just created report
  27.     For Each rpt In Reports
  28.         If rpt.RecordSource = "qryDummy" Then Set rptReport = rpt
  29.     Next
  30.  
  31.     With rptReport
  32.  
  33.         ' Create title control
  34.         With CreateReportControl(.Name, acLabel, _
  35.                 acPageHeader, , "Title", 0, 0)
  36.             .FontBold = True
  37.             .FontSize = 12
  38.             .SizeToFit
  39.         End With
  40.  
  41.         ' Create timestamp on footer
  42.         CreateReportControl .Name, acLabel, _
  43.             acPageFooter, , Now(), 0, 0
  44.  
  45.         ' Create page numbering on footer
  46.         With CreateReportControl(.Name, acTextBox, _
  47.             acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", _
  48.             .Width - 1000, 0)
  49.             .SizeToFit
  50.         End With
  51.  
  52.         ' Detach the report from dummy query
  53.         .RecordSource = strSQL
  54.  
  55.         ' Set the report caption to autogenerated unique string
  56.         strCaption = GetUniqueReportName
  57.         If strCaption <> "" Then .Caption = strCaption
  58.  
  59.     End With
  60.  
  61.     DoCmd.RunCommand acCmdPrintPreview
  62.  
  63.     Set rptReport = Nothing
  64.  
  65. End Sub

I'm assuming the code for the calculated control will need to go into the CreateAutoReport module.

Unfortunately, I've taught myself I know in Access, and I've exceeded my knowledge.
Jan 29 '19 #1
Share this Question
Share on Google+
7 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,054
Well, I'm able to add a summing control in the footer with this:

Expand|Select|Wrap|Line Numbers
  1.         ' Create a Totals Control on footer
  2.         With CreateReportControl(.Name, acTextBox, _
  3.             acPageFooter, , "=Sum([YourFieldName])", _
  4.             .Width - 1000, 0)
  5.             .SizeToFit
  6.         End With
That would be inserted at Line 52 above. However, you would have to insert Code to find out if you have any of those fields, determine where they fall and then line up those fields in the footer.

Although the following concept is a huge project--and probablay well beyond your current capabilities--I think what really needs to happen is to build the Report based upon the user's input.

I could probably achieve that, but the amount of work required is too excessive at this point.

One question I would ask here is, "What have they been doing currently?" My initial thought is that there should be quite a few options in creating statis reports that simply use a user's criteria--knowing that the report will not be "exactly" what they are looking for, but may fulfill 95% of what they need. It is typically very rare for a user to request the specific fields from an Access DB for a Report. However, because some of the fields are named "FY1415, FY1516, FY1617, FY1718, FY1819, FY1920, FY2021, FY2122, FY2223" it also leads me to believe that this project could use a serious overhaul to the structure--again, you are an admitted novice--but one of the best ways to learn is by doing (and I don't say that flippantly).

You have your work cut out for you in the near future. We may have to take baby steps first.

======================
Added:
I guess I should explain that I don't think that the AutoReport feature of Access has the flexibility you need for such a complex activity. At first glance, I thought it might allow you some flexibility, which perhaps it does, but trying to capture all aspects of your design may take more time trying to get the AutoReport to fit than just committing yourself to having your code build your report on the fly.
Jan 29 '19 #2

P: 23
Basically, they've been using Excel sheets. A lot of users are tentative with Excel and are petrified of Access. I inherited the database in its current state and they wanted to improve a few things, queries being one of them. I don't disagree it needs an overhaul. A big one. The problem is the last thing they wanted (at least for now) was to have the total on the report. They aren't too keen on having me start over from scratch to revamp it (despite it being more efficient long term and being incredibly difficult to work with programmatically in its current state.)

They need to be able to store multiple pieces of data in several fields. That's partially where my lack of knowledge comes in. People have suggested using multiple one-to-many relationships for those fields, but I've only used a single one-to-many relationship across a database and not multiple of them.

So basically, it's a nightmare. And I'm trying to accomplish the goals under the limitations I'm placed under.
Jan 29 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,054
I understand limitations. Are the Spreadsheets static and historical or are these constantly updated? If they are static for the most part, we may be able to use that to your advantage and build tables based upon the spreadsheets (not just a linked spreadsheet)--but that may have to do for now.
Jan 29 '19 #4

P: 23
The sheets are updated anytime a contract's details have been updated. So a record would be updated several times. Part of the initial problem is that the spreadsheet was also stored with multiple entries per field (i.e. county). They also stored the address as one big field instead of separate fields for A1, A2, City, State and Zip.
Jan 30 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 3,054
You do realize that as a data guy, my only response is, "AAARRRGGGHHH!!!"
Jan 30 '19 #6

P: 23
Believe me, I'm right there with you. I'm actually contemplating working on this from home to start it from scratch, even though I'd have to do it all in my personal time. I was really hoping to avoid that.
Jan 30 '19 #7

twinnyfo
Expert Mod 2.5K+
P: 3,054
Although the present discussion is a bit off topic, one of your challenges may be "breaking the paradigm" of them using spreadsheets.

Excel was simply not designed for the purposes many people use it for. Oh, for a more universal understanding of the necessity and power of a good database!

:-)
Jan 30 '19 #8

Post your reply

Sign in to post your reply or Sign up for a free account.