Rookie question: In a report control, calling Function created within a module and it can't be found

Question

I am trying to use Allen Brown's ConcatRelated Finction (http://allenbrowne.com/func-concat.html) in a report control. I copied the code into a module that I called "ConcatRelated". The table the report uses is "t Individual Pieces", design pictured below.

In a report, I want to present a concatenated list of the field "Mediums" for all records that each "Artist ID" has, which may range from 1 record to 12 records. In this report, I have a field with the Control Source of "=ConcatRelated("[Medium]","[t Individual Pieces]","Artist ID=" & [Artist ID])". When I switch to Report view, it asks me to "Enter Parameter Value" for ConcatRelated. To make matters worse, after running many iterations of the Control Source, when I click on "OK" with no entry for the parameter value, it now has begun saying "Error 2465: Microsoft Access can't find the field '|1' referred to in your expression." I have no idea what that means as I find no "|1" in either my expression or the code.

I have tried recopying the code into the module, copying it into a new Module and deleting the old one, and doing "Compact and Repair Database", then closing Access and reopening it, in that order. Please help a frustrated novice who is obviously in over her head but dedicated to completing this project for a non-profit.

Answer 1

Dear katzen

Thank you for posting your question in the Microsoft Q&A forum.

If your VBA code module is currently named "ConcatRelated" (the same as the function name), Access will get confused and give you that parameter value error, so please try changing the module name to something else, such as "Module 01".

Also, because your field name "Artist ID" has a space in it, you need to update your Control Source formula to include brackets around it like this: =ConcatRelated("[Medium]", "[t Individual Pieces]", "[Artist ID]=" & [Artist ID])

Please give those two changes a try to see if it works as you expected and let me know the feedback. If you are still having trouble, feel free to send me the sample file via private message and I can take a look at this.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

The following function uses the highly efficient GetString method of the ADO Recordset object to concatenate values from a related table into a single string expression:

Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String

    Const NOCURRENTRECORD = 3021
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strList As String
    
    strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn
   
    Set rst = New ADODB.Recordset
    
    With rst
        Set .ActiveConnection = CurrentProject.Connection
        .Open _
            Source:=strSQL, _
            CursorType:=adOpenForwardOnly, _
            Options:=adCmdText
        
        On Error Resume Next
        strList = .GetString(adClipString, , strDelim, strDelim)
        .Close
        Select Case Err.Number
            Case 0
            ' no error so remove trailing delimiter
            ' and return string
            GetList = Left(strList, Len(strList) - Len(strDelim))
            Case NOCURRENTRECORD
            ' no rows in table so return
            ' zero length string
            Case Else
            ' unknown error
            GetList = "Error"
        End Select
    End With
    
End Function

The function would called like this for instance:

GetList("qryProducts","[Product]","[Product]",", ","[Customer ID] = " & [ID])

where qryProducts is the following query, using data from the old Northwind database to return a list of products ordered by each customer:

SELECT DISTINCT Orders.[Customer ID], 
[Product Name] & " - " & Format([Standard Cost],"Currency") AS Product
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] 
ON Orders.[Order ID]=[Order Details].[Order ID]) 
ON Products.ID=[Order Details].[Product ID];

Answer 3

Hi, Ken! Thanks for the reply. I'm all for an alternative if it's more efficient, especially since this will be used over a LAN that isn't always the fastest.

I just got around to trying this. I set up the query and made it the Record Source for the report. but when I switched to the report, I ended up in the debugger, with it saying "Compile Error. User-defined type not defined" and highlighted the line of code "Dim rst as ADODB.recordset."

If it helps any, every time I click "OK" and try to close the debugger, the message comes up again, and I finally had to force a closure of Access through Task Manager. I have about 50 records in the table, so maybe that's why it kept repeating...

I have no idea what the problem is as my coding experience does not extend to defining or working with defined Recordsets. Can you help, please?

Answer 4

Ken, FYI: I tried creating a table and using it instead of the query, but I received the same results.

Answer 5

Did you set a reference to the ADO library in your database?