User-Defined Type Error Message (1 Viewer)

[Hamdard]

Hi all,

I have the following table in which orders are stored by each product type in a row.

However, I want to bring all product types in one cell 'field' in a query by each product type as shown in the following snapshot.

To achieve this, I copied over the following ADO function in a standard module with the help of the internet. But I keep getting an error message related to the user-defined type not found.

Function ConcatADO(strSQL As String, strColDelim, _
   strRowDelim, ParamArray NameList() As Variant)

   Dim rs As New ADODB.Recordset
   Dim strList As String

   On Error GoTo Proc_Err

       If strSQL <> "" Then
           rs.Open strSQL, CurrentProject.Connection
           strList = rs.GetString(, , strColDelim, strRowDelim)
           strList = Mid(strList, 1, Len(strList) - Len(strRowDelim))
       Else
           strList = Join(NameList, strColDelim)
       End If

       ConcatADO = strList

   Exit Function

Proc_Err:
       ConcatADO = "***" & UCase(Err.Description)
End Function

And then, I create the following SQL language in a query.

SELECT tblOrders.[Order Number], ConcatADO("SELECT [Product Types] FROM tblOrders
                     WHERE [Order Number]=" & [Order Number],",","; ") AS [All Products Combined]
FROM tblOrders
GROUP BY tblOrders.[Order Number];

When I run the query, I get the following error message. Could you advise a solution?

 

[Gasman]

You would need to add the ADODB reference for that code. However not really needed DAO works just as well.
Look here for a ready made function.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

 

[ebs17]

Dim rs As New ADODB.Recordset

This is early binding, meaning you need a reference to the corresponding library:
Microsoft ActiveX Data Objects 6.1 Library

Or alternatively use late binding:

Static rs As Object
If rs Is Nothing Then Set rs = CreateObject("ADODB.Recordset")

 

[CJ_London]

you might also want to investigate the concatrelated function

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[Hamdard]

Static rs As Object If rs Is Nothing Then Set rs = CreateObject("ADODB.Recordset")

Thank you and I did both. Tick marked MS ActiviX Control 6.1 from the reference and I also replaced early binding with late binding. Now I get the following message in the qery. I tried my best to solve the missing operator error but wasn't able to. Could you recommend a fix for this?

Below is my query language:

SELECT tblOrders.[Order Number], ConcatADO("SELECT [Product Types] FROM tblOrders
                     WHERE [Order Number]=" & [Order Number],",","; ") AS [All Products Combined]
FROM tblOrders
GROUP BY tblOrders.[Order Number];

 

[Hamdard]

you might also want to investigate the concatrelated function

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

Thank you.

 

[Hamdard]

You would need to add the ADODB reference for that code. However not really needed DAO works just as well.
Look here for a ready made function.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

Thank you. Will get to this if my current coding that is in progress didn't yield a result.

 

[Gasman]

Why not just use something that works?
I was also going to offer @cjs link, but feel my link is simpler to use, if not as extensive.
I have used AB's concatenated in one of my dbs in the past, but unless I needed all the bells and whistles, I would now opt for that from @theDBguy

 

[ebs17]

You also have to be able to write the query.

SELECT
   O.[Order Number],
   ConcatADO("SELECT [Product Types] FROM tblOrders
                     WHERE [Order Number] = '" & O.[Order Number] & "'", ",", "; ") AS [All Products Combined]
FROM
   tblOrders AS O
GROUP BY
   O.[Order Number]

WHERE [Order Number]=" & [Order Number]

Identical names lead to a circular relationship. The table alias as a qualification of the field of the outer query mitigates this.
In addition, the text data type (according to the contents specified above) was not taken into account here.

 

[Hamdard]

You also have to be able to write the query.

SELECT
   O.[Order Number],
   ConcatADO("SELECT [Product Types] FROM tblOrders
                     WHERE [Order Number] = '" & O.[Order Number] & "'", ",", "; ") AS [All Products Combined]
FROM
   tblOrders AS O
GROUP BY
   O.[Order Number]

Identical names lead to a circular relationship. The table alias as a qualification of the field of the outer query mitigates this.
In addition, the text data type (according to the contents specified above) was not taken into account here.

Amazing and thanks. It worked. However, it worked when I used the early binding. With the late binding, it will display a message that 'the operation is not allowed when the object is open'. But it works perfectly now with the early binding.

Thank you @ebs17 . You are a genius.