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

[lonerjohn]

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.

    Dim filterContract As String
    Dim filterCounty As String
    Dim filterPriority As String
    Dim filterFields As String
    Dim strFilter As String
    Dim strSQL As String
    Dim i As Integer
    Dim idx As Variant
    Dim lst As Access.ListBox
    Dim andor As String
 
 
    'Put code here to make it an AND or OR
    If Me.optAndOr = 1 Then
        andor = " AND "
    Else
        andor = " OR "
    End If
 
    'Check for contract entry
    Set lst = Me.lstContractType
    For Each idx In lst.ItemsSelected
        If filterContract = "" Then
            filterContract = "'" & lst.ItemData(idx) & "'"
        Else
            filterContract = filterContract & ", '" & lst.ItemData(idx) & "'"
        End If
 
    Next idx
 
    If filterContract <> "" Then
        filterContract = "ContractType IN (" & filterContract & ")" & andor
    End If
 
    'Check for counties section
    Set lst = Me.lstCounties
    For Each idx In lst.ItemsSelected
        If filterCounty = "" Then
            filterCounty = "'" & lst.ItemData(idx) & "'"
        Else
            filterCounty = filterCounty & ", '" & lst.ItemData(idx) & "'"
        End If
    Next idx
 
    If filterCounty <> "" Then
        filterCounty = "County.value IN (" & filterCounty & ")" & andor
    End If
 
 
    'Check for priority category
    Set lst = Me.lstPriorityCategory
    For Each idx In lst.ItemsSelected
        If filterPriority = "" Then
            filterPriority = "'" & lst.ItemData(idx) & "'"
        Else
            filterPriority = filterPriority & ", '" & lst.ItemData(idx) & "'"
        End If
    Next idx
 
    If filterPriority <> "" Then
        Debug.Print filterPriority
        filterPriority = "PriorityCategory.value IN (" & filterPriority & ")" & andor
    End If
 
    Set lst = Me.lstFields
    For Each idx In lst.ItemsSelected
        If filterFields = "" Then
            filterFields = lst.ItemData(idx)
            temptotals = lst.ItemData(idx)
            strSQL = "SELECT * from tblmain "
        Else
            filterFields = filterFields & ", " & lst.ItemData(idx)
            temptotals = temptotals & " + " & lst.ItemData(idx)
        End If
    Next idx
 
    If filterFields <> "" Then
        Debug.Print filterFields
 
        strSQL = "SELECT PayeeName, ContractNum, ContractType, County, PriorityCategory, " & filterFields & " FROM tblmain "
 
 
    End If
 
    strFilter = filterContract & filterCounty & filterPriority
    If strFilter <> "" Then
        strFilter = " WHERE " & strFilter
        strFilter = Left(strFilter, Len(strFilter) - Len(andor))
        strSQL = strSQL & strFilter
    End If
        Debug.Print strSQL
 
    CreateAutoReport (strSQL)
 
End sub

The code below is the code that twinnyfo provided for my auto report:

Option Compare Database
Option Explicit
 
Private appAccess   As New Access.Application
 
Public Sub CreateAutoReport(strSQL As String)
    Dim rpt         As Access.Report
    Dim rptReport   As Access.Report
    Dim strCaption  As String
    Dim lblReport   As Access.Label
    Dim txtReport   As Access.TextBox
 
    CurrentDb.QueryDefs("qryDummy").SQL = strSQL
    'Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase (CurrentDb.Name)
 
    ' Open dummy query to invoke NewObjectAutoReport command on it
    ' Put the report created to design view to make properties editable
    With DoCmd
        .OpenQuery "qryDummy", acViewNormal
        .RunCommand acCmdNewObjectAutoReport
        .Close acQuery, "qryDummy"
        .RunCommand acCmdDesignView
    End With
 
    ' Get reference to just created report
    For Each rpt In Reports
        If rpt.RecordSource = "qryDummy" Then Set rptReport = rpt
    Next
 
    With rptReport
 
        ' Create title control
        With CreateReportControl(.Name, acLabel, _
                acPageHeader, , "Title", 0, 0)
            .FontBold = True
            .FontSize = 12
            .SizeToFit
        End With
 
        ' Create timestamp on footer
        CreateReportControl .Name, acLabel, _
            acPageFooter, , Now(), 0, 0
 
        ' Create page numbering on footer
        With CreateReportControl(.Name, acTextBox, _
            acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", _
            .Width - 1000, 0)
            .SizeToFit
        End With
 
        ' Detach the report from dummy query
        .RecordSource = strSQL
 
        ' Set the report caption to autogenerated unique string
        strCaption = GetUniqueReportName
        If strCaption <> "" Then .Caption = strCaption
 
    End With
 
    DoCmd.RunCommand acCmdPrintPreview
 
    Set rptReport = Nothing
 
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.

[twinnyfo]

Well, I'm able to add a summing control in the footer with this:

        ' Create a Totals Control on footer
        With CreateReportControl(.Name, acTextBox, _
            acPageFooter, , "=Sum([YourFieldName])", _
            .Width - 1000, 0)
            .SizeToFit
        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.

[lonerjohn]

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.

[twinnyfo]

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.

[lonerjohn]

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.

[twinnyfo]

You do realize that as a data guy, my only response is, "AAARRRGGGHHH!!!"

[lonerjohn]

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.

[twinnyfo]

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!

:-)