Solved Assistance running Allen Browne's ConcatRelated Function (1 Viewer)

pooldead

Registered User.
Local time
Today, 00:26
Joined
Sep 4, 2019
Messages
136
I am trying to use Allen Browne's ConcatRelated function but I am having some trouble. My code is
Code:
sqlStr = "INSERT INTO Transsend_IP (Roles) SELECT ConcatRelated("Roles", "Transsend_UL", "Transsend_IP.APP_USERNAME = '" & Transsend_UL.APP_USERNAME & "'", , ";") FROM Transsend_UL;"

For background, I have a table, Transsend_UL, that contains a list of usernames and roles. It appears as:
User1 Role1
User1 Role2
User2 Role1
etc.
I have a second table, Transsend_IP, that I am trying to update with these roles, however I need each user's roles concatenated into one string. I attempted this with the code above, but I get an error "Expected: end of statement" at the
Code:
ConcatRelated("Roles"
section.

I have never used this function before, so any advise on how to clean this up would be greatly appreciated!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:26
Joined
May 7, 2009
Messages
19,169
if you like, VBA:
Code:
private sub test()
dim rs as dao.recordset
dim db as dao.database
dim sText as string
set db=currentdb
set rs= db.openrecordset("Transsend_IP", dbopendynaset)

with rs
	.movefirst
	while not .eof
		sText=ConcatRelated("Roles","Transsend_UL","APP_USERNAME='" & !APP_USERNAME & "'") & ""
		.edit
		!role = sText
		.update
		.movenext
	wend
	.close
end with
set rs=nothing
set db=nothing
end sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:26
Joined
Oct 29, 2018
Messages
21,358
Hi. Just to clarify, are you trying to update existing records or create new ones?
 

pooldead

Registered User.
Local time
Today, 00:26
Joined
Sep 4, 2019
Messages
136
Hi. Just to clarify, are you trying to update existing records or create new ones?

My Transsend_IP table populates a bunch of info from AD, and I need to update this table's existing records with the concatenated Roles. This way every userID will list all their roles and AD information in one record.
 

pooldead

Registered User.
Local time
Today, 00:26
Joined
Sep 4, 2019
Messages
136
if you like, VBA:
Code:
private sub test()
dim rs as dao.recordset
dim db as dao.database
dim sText as string
set db=currentdb
set rs= db.openrecordset("Transsend_IP", dbopendynaset)

with rs
	.movefirst
	while not .eof
		sText=ConcatRelated("Roles","Transsend_UL","APP_USERNAME='" & !APP_USERNAME & "'") & ""
		.edit
		!role = sText
		.update
		.movenext
	wend
	.close
end with
set rs=nothing
set db=nothing
end sub

I'm going to try this and see how it works. I'll let you know.
 

pooldead

Registered User.
Local time
Today, 00:26
Joined
Sep 4, 2019
Messages
136
if you like, VBA:
Code:
private sub test()
dim rs as dao.recordset
dim db as dao.database
dim sText as string
set db=currentdb
set rs= db.openrecordset("Transsend_IP", dbopendynaset)

with rs
	.movefirst
	while not .eof
		sText=ConcatRelated("Roles","Transsend_UL","APP_USERNAME='" & !APP_USERNAME & "'") & ""
		.edit
		!role = sText
		.update
		.movenext
	wend
	.close
end with
set rs=nothing
set db=nothing
end sub

So I ran this after changing !role to !roles and it spat out the error: "Run-time error 3163: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Is there a workaround for this?
 

June7

AWF VIP
Local time
Yesterday, 23:26
Joined
Mar 9, 2014
Messages
5,424
Why would you want to save concatenated data?

Is the field a memo (long text) type?
 

pooldead

Registered User.
Local time
Today, 00:26
Joined
Sep 4, 2019
Messages
136
Why would you want to save concatenated data?

Is the field a memo (long text) type?

I'm using it to compare against a list of variant roles, however it can't cycle through when the same user is listed twice. So I need to concatenate the roles down into a single cell tied to just one user record instead of multiple.
 

June7

AWF VIP
Local time
Yesterday, 23:26
Joined
Mar 9, 2014
Messages
5,424
But why save concatenated value and not just use query for your analysis? How is concatenated string used in analysis?
 

sxschech

Registered User.
Local time
Today, 00:26
Joined
Mar 2, 2010
Messages
791
Are you looking to remove duplicates from your concat list?
Before:
"User1 Role1, User1 Role2, User2 Role1, User1 Role2"

After:
User1 Role1,User1 Role2,User2 Role1

Code:
Function RemoveDupes2(ByVal txt As String, Optional delim As String = " ") As String
'Remove duplicate repeating multiple words from string
'https://www.extendoffice.com/documents/excel/2133-excel-remove-duplicate-characters-in-string.html#a2
'20190102
    Dim i
    'Updateby20140924
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each i In Split(txt, delim)
            If Trim(i) <> "" And Not .Exists(Trim(i)) Then .Add Trim(i), Nothing
        Next
        If .Count > 0 Then RemoveDupes2 = Join(.Keys, delim)
    End With
End Function
 

Users who are viewing this thread

Top Bottom