P
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
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