Code to make a comma list from related table (subform)

[Guest]

I know it used to be out there somewhere...

I need the code or query or whatever it is to get a comma value list from a
subreport. So, if my main record is a person, I can list her favorite
magazines in a comma separated list: Vogue; Teen; People; Newsweek instead
of having to list them all out:
Vogue
Teen
People
Newsweek

Thanks!!

 

[Allen Browne]

See:
Return a concatenated list of sub-record values'
at:
http://www.mvps.org/access/modules/mdl0004.htm

 

[Guest]

I am getting no results, but I’m sure I have something wrong…

I created a form1 to test the code. I have a text0 and command5. This is the
code attached to the command5 button: Text0 =
fConcatChild("tbl_activityaffiliate", "activityID", "affiliateID", "Long",
[ActivityID])

Here is the code that I copied and pasted (and changed)
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

Dim dbscurrent As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set dbscurrent = CurrentDb
strSQL = "Select [" & affilateid & "] From [" & tbl_activityAffilate & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & ActivityID & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & ActivityID & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

<<I STOPPED THE CODE HERE TO SEE IF ANY OF THE ABOVE OBJECTS HAD ANY VALUES-
AND THEY WERE ALL NULL
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(affilate) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

Any suggestions? Thank you!

 

[Allen Browne]

If all your variables are zero-length strings at the point you indicated,
the values are not being passed into the function, or the code is being
reset.

(I assume they are ZLSs, as strings cannot be Null.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am getting no results, but I'm sure I have something wrong.

I created a form1 to test the code. I have a text0 and command5. This is
the
code attached to the command5 button: Text0 =
fConcatChild("tbl_activityaffiliate", "activityID", "affiliateID", "Long",
[ActivityID])

Here is the code that I copied and pasted (and changed)
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

Dim dbscurrent As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set dbscurrent = CurrentDb
strSQL = "Select [" & affilateid & "] From [" & tbl_activityAffilate &
"]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & ActivityID & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & ActivityID & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

<<I STOPPED THE CODE HERE TO SEE IF ANY OF THE ABOVE OBJECTS HAD ANY
VALUES-
AND THEY WERE ALL NULL
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(affilate) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

Any suggestions? Thank you!

See:
Return a concatenated list of sub-record values'
at:
http://www.mvps.org/access/modules/mdl0004.htm