Passing a Concatenated String to a Field on a Report

[Denis Bisson via AccessMonster.com]

Good day,

First, thanks to the numerous posts found within this forum, I was able to
build the function below. There is, however, one last thing that I am missing.


The function, as written works and returns the concatenated information that
I am seeking. What I now need to do is to pass that concatenated string
(strConcat) to a field in a report [Reports]![rptProbes]![txtDirAudited].

I am calling the function from the ControlSource of [Reports]![rptProbes]!
[txtDirAudited, but am not sure how to capture strConcat.

Can anyone help?

Thank you for your time and advice.

Denis

**************************************
Public Function ProbeDir()
Dim dbs As DAO.Database, rs As DAO.Recordset
Dim strSQL As String
Dim varItem As Variant
Dim Dir As String
Dim qdf As QueryDef

Dim strConcat As String ' return string

Set dbs = CurrentDb

strSQL = "SELECT tblPQAObservations.PQAObservationNoID, tblPQAO234.
Directorate " _
& "FROM tblPQAObservations " _
& "INNER JOIN tblPQAO234 ON tblPQAObservations.
PQAObservationNoID = tblPQAO234.PQAObservationID " _
& "WHERE (((tblPQAObservations.PQAObservationNoID)=(" & [Reports]
![rptProbes]![PROBEID] & ")));"

Set rs = dbs.OpenRecordset(strSQL)

rs.MoveLast

'Debug.Print "CAPs: " & rs.RecordCount
rs.MoveFirst

With rs
'.MoveFirst
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields("Directorate") & ", "
.MoveNext
Loop
End If
.Close
End With

'Debug.Print strConcat
Set rs = Nothing
Set dbs = Nothing


''remove the ", " from the end
If Len(strConcat) > 0 Then
strConcat = Left$(strConcat, Len(strConcat) - 2)
End If
Debug.Print strConcat

'strConcat = [Reports]![rptProbes]!txtDirAudited -- no success with
THIS line

End Function
*********************************

 

[Alex Dybenko]

Hi,
at the end of your function add:

ProbeDir=strConcat

and then in your report, in txtDirAudited control source type:
=ProbeDir()

also better idea can be to pass value of [Reports]![rptProbes]![PROBEID] as
a function argument

then control source will look like:
=ProbeDir([PROBEID])

 

[Denis Bisson via AccessMonster.com]

Thanks for the solution Alex. I will also implement your suggestion.

Cheers!
Denis