Using ConcatRelated In VBA

[jo15765]

Greetings - I am attempting to use the ConcatRelated Function by Allen Browne in a recordset. My syntax is not throwing any errors - however nothing is being input either. Am I double quoting incorrectly? What should I do in order for this to execute succesfully?

rsInsert![ProfencyScore] = ConcatRelated("" & userIDField & "", "SchoolData", "" & luid & " = " & rsSelect(luid).Value)

[pbaldy]

Try

ConcatRelated(userIDField, "SchoolData", "luid = " & rsSelect(luid).Value)

[pbaldy]

Or if the field argument is the actual field name (I was thinking it was a variable):

ConcatRelated("userIDField", "SchoolData", "luid = " & rsSelect(luid).Value)

[jo15765]

Or if the field argument is the actual field name (I was thinking it was a variable):

ConcatRelated("userIDField", "SchoolData", "luid = " & rsSelect(luid).Value)

In my VBA this gives me an error of Compile Error: Expected: list sperator or )

[pbaldy]

Hard to tell without the rest of the code. Is luid a variable or a field name? If the latter:

ConcatRelated("userIDField", "SchoolData", "luid = " & rsSelect!luid)

[June7]

You are referencing a recordset rsSelect for an input parameter? Try:

rsSelect("luid")

or

rsSelect!luid

And you are updating a field in recordset rsInsert? Why would you save this calculated result to table?

Post your complete procedure if you need more help.

[jo15765]

Hard to tell without the rest of the code. Is luid a variable or a field name? If the latter:

ConcatRelated("userIDField", "SchoolData", "luid = " & rsSelect!luid)

Let me try to elaborate

userIDField is a field in my table
SchoolData is the table name
luid is a field in my table
rsSelect!luid is a variable

[pbaldy]

I'll get out of the way.

[jo15765]

You are referencing a recordset rsSelect for an input parameter? Try:

rsSelect("luid")

or

rsSelect!luid

And you are updating a field in recordset rsInsert? Why would you save this calculated result to table?

Post your complete procedure if you need more help.

That removes the error, but no data is insert into the table

Full on syntax

DoCmd.RunSQL ("Create Table [_Vendor1] (loggedinuserid text,  profencyScoretext);")
Dim rsSelect As DAO.Recordset
Dim rsInsert As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rsSelect = db.OpenRecordset("Select DISTINCT " & luid & " FROM [SchoolData]")
Set rsInsert = db.OpenRecordset("_Vendor1")
Do While Not rsSelect.EOF
	rsInsert.AddNew
	rsInsert!loggedinuserid = rsSelect(luid).Value
	rsInsert![ProfencyScore] = ConcatRelated("" & userIDField & "", "SchoolData", "" & luid & " = " & rsSelect(luid).Value)
	rsInsert.Update
	rsSelect.MoveNext
Loop

[June7]

Still wonder why saving to table.

Code shows incorrect syntax - not corrected as already suggested.

rsInsert!loggedinuserid = rsSelect!luid
rsInsert!ProfencyScore = ConcatRelated("userIDField", "SchoolData", "luid = " & rsSelect!luid)

Is fieldname really spelled [ProfencyScore] and not [ProficiencyScore]?

Or instead of using recordset objects, consider:

CurrentDb.Execute "INSERT INTO [_Vendor1](loggedinuserid, ProfencyScore) SELECT DISTINCT luid, ConcatRelated('userIDField', 'SchoolData', 'luid = ' & [luid]) AS PScores FROM SchoolData"

Tested, it works.

[jo15765]

@June07 - I was attempting to go the recordset route as the ConcatRelated() function truncates at 255 characters and I have run into a few instances where the text excceeds that limit. So I was trying to come up with an alternative to continue using the same function, but change syntax to handle larger characters.

[June7]

According to Allen, the truncation occurs if you use the function in a query and then use that query in another query. So I can see how the SQL statement I suggested might fail.

Maybe committing the data to a memo field in table is the best approach or maybe you should use the function in a later stage of a process. Depends on what you are really trying to accomplish. The function could be called from a textbox on a report.

For more than 255 characters the field must be memo type. Your code is creating table with only text type. So your recordset approach and saving to table does nothing to resolve the issue of more than 255 characters.

Following code works:

DoCmd.RunSQL ("Create Table [_Vendor1] (loggedinuserid text,  profencyScore memo);")
Dim rsSelect As DAO.Recordset
Dim rsInsert As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rsSelect = db.OpenRecordset("Select DISTINCT luid FROM [SchoolData]")
Set rsInsert = db.OpenRecordset("_Vendor1")
Do While Not rsSelect.EOF
    rsInsert.AddNew
    rsInsert!loggedinuserid = rsSelect!luid
    rsInsert![ProfencyScore] = ConcatRelated("userIDField", "SchoolData", "luid = " & rsSelect!luid)
    rsInsert.Update
    rsSelect.MoveNext
Loop

However, I recommend not using CREATE TABLE. Create the table manually and make it permanent then instead delete records at beginning of the Sub:

CurrentDb.Execute "DELETE FROM [_Vendor1]"