Dynamic Footnote Reference / Footnote (1 Viewer)

[April15Hater]

Hey guys,

In my report's detail section, I need to display footnote references for each detail record that correspond to a footnote below.

The report's purpose is to display records from imported transactions in two tables from two different systems, and display the difference.

The report query aggregates the transactions by a set of criteria . On each import, a MatchCriteria Table is updated for each unique combination of the criteria (Date, BusinessUnit, Product, and Account). An update query then assigns the Match Criteria primary key to the transaction tables.

The particular report I'm working only aggregates on the date criteria. However if there is a difference, we need to be able to add an explanation by way of a footnote. I'd like to have a footnote reference number on the detail line that matches up to the footnote text at the bottom of the report. The same footnote text may apply to multiple detail records as well, so I would need it to display the footnote reference multiple times in the detail with only one instance of the footnote text. The UI currently stores the footnotes based by Date.

FWIW, I thought I had this working; I built a disconnected ADO RS in VBA, populated the report's contents, looped the records and filtered by each Footnote text, and assigned an incrementing number to all in filtered set if no Footnote reference had been applied. Then thinking I could just do a

Set me.Recordset = rstReportData

in my report module, I was blasted with the "This feature is only available in an ADP." error..... Not a nice way to disconver that you can bind a disconnected ADO to a form but not a report.

Here's the code I have to assign the Footnote Reference to the Disconnected Recordset:

Private Function mrstFNReference() As ADODB.Recordset
    Dim rstExceptions As ADODB.Recordset
    Dim lngFNRefNum As Long
    Dim lngA As Long
    Set rstExceptions = grstExceptions
 
    'Procedure loops all records in the Exception Recordset.  Checks existence of text
    'in the FootnoteText field. If Footnote text exists, checks to see if there is a
    'Reference Number assigned.  If no reference number assigned, filters recordset on
    'the FootnoteText and assigns the same sequential number to all records in the
    'filtered recordset.  The idea here is to assign one unique reference number for each
    'unique FootnoteText string to prevent duplicates footnotes on the report.
    With rstExceptions
        For lngA = 1 To .RecordCount
            .Move lngA - 1, adBookmarkFirst
            If .Fields.Item("FootnoteText").Value <> 0 Then
                .Filter = "FootnoteText = '" & .Fields.Item("FootnoteText").Value & "'"
                .MoveFirst
                If .Fields.Item("FootnoteReference").Value = vbEmpty Then
                    lngFNRefNum = lngFNRefNum + 1
                    Do Until .EOF
                        .Fields.Item("FootnoteReference").Value = lngFNRefNum
                        .Update
                        .MoveNext
                    Loop
                    .MoveFirst
                End If
                .Filter = adFilterNone
            End If '.Fields.Item("FootnoteText").Value <> 0
        Next lngA
    End With 'rstExceptions
    Set mrstFNReference = rstExceptions
End Function

Thought about just sending the rs to a temp table and calling it a day, but I just try to avoid temps at all costs. Was hoping somebody could offer an idea or two to straighten this out.

Thanks,

Joe