Multiple Values in One Record - Syntax Error

[philip.morgan]

I am following Dave Healey's code to report multiple values in one
record (http://groups.google.ca/group/microsoft.public.access.reports/
msg/dbea034deb75430e?dmode=source). I think I have a syntax error in
the ControlSource designation.

BACKGROUND
Specifically, my tblCI contains a field (CI) which is related to
tblClassesCI which contain the Classes for the CI. A standard report
indicates multiple Classes for each CI. The requirement is to separate
multiple records by a comma in one field.

Per Dave's specifications, I have a query named "qryCI" with a "CI"
field. My code is as follows:

Public Function ListClasses(CI As Integer) As String
Dim db As Database
Dim Qdef As QueryDef
Dim ProgSet As Recordset
Dim ReturnCode As String
Set db = CurrentDb
Set Qdef = db.QueryDefs("qryCI")
Qdef("CI") = CI
Set ProgSet = Qdef.OpenRecordset()
ReturnCode = ""
While ProgSet.EOF = False
If ReturnCode = "" Then
ReturnCode = ProgSet("Class")
Else
' Separate each class for the CI with a comma
ReturnCode = ReturnCode & Chr(13) & ProgSet("Class")
End If
ProgSet.MoveNext
Wend
ProgSet.Close
Set ProgSet = Nothing
Set Qdef = Nothing
Set db = Nothing
ListClasses = ReturnCode
End Function

PROBLEM (ControlSource syntax?)
Dave specified the ControlSource as:

"=ListProgrammers([Project Main.ProjectID])"

where [Project Main.ProjectID] is a field in the form's underlying
query."

When I enter "=ListClasses([qryCI.CI)]", Access converts it to this
"=ListClasses([qryCI].[CI])". When I run the report, Access prompts me
for the qryCI.

Any assistance would be greatly approciated.

Phil Morgan

 

[Duane Hookom]

I'm not sure about your code. There is a generic function for this at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
function allows you to use it in most situations where you want to
concatenate values.