User-Defined Type Error Message (1 Viewer)

Hamdard

New member
Local time
Today, 07:17
Joined
Feb 22, 2015
Messages
14
Hi all,

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

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.
1704297462392.png


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.
Code:
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.
Code:
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?

1704297896056.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:47
Joined
Sep 21, 2011
Messages
14,528
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.
 

ebs17

Well-known member
Local time
Today, 04:47
Joined
Feb 7, 2020
Messages
2,002
Code:
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:
Code:
Static rs As Object
If rs Is Nothing Then Set rs = CreateObject("ADODB.Recordset")
 
Last edited:

Hamdard

New member
Local time
Today, 07:17
Joined
Feb 22, 2015
Messages
14
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:
Code:
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];

1704311183250.png
 

Hamdard

New member
Local time
Today, 07:17
Joined
Feb 22, 2015
Messages
14
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.
Thank you. Will get to this if my current coding that is in progress didn't yield a result.
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:47
Joined
Sep 21, 2011
Messages
14,528
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

Well-known member
Local time
Today, 04:47
Joined
Feb 7, 2020
Messages
2,002
You also have to be able to write the query.
SQL:
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]

Code:
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.
 
Last edited:

Hamdard

New member
Local time
Today, 07:17
Joined
Feb 22, 2015
Messages
14
You also have to be able to write the query.
SQL:
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.
 
Last edited:

Users who are viewing this thread

Top Bottom