Create one string of multiple values from query

[cs581]

I am trying to create an export using a query that pulls "Event" and "Event Details." I have a one to many relationship from tblEvent to tblEventDetails. My export should show one record for each event, with a string field containing the event details. I'm trying to create a function that pulls one field for the "Event Details" as a string.

Here's what I've tried so far:

Public Function Operations As String
   Dim cnCurrent as ADODB.Connection
   Dim rsQuery1 as ADODB.Recordset
   Dim strSQL As string, strTypes as String

   strSQL="SELECT Type FROM Table1 WHERE (((IDNumber=" & PersonID & "));"
   strTypes = ""
 
   Set cnCurrent = CurrentProject.Connection
   Set rsQuery1 = New ADODB.Recordset
   
   With rsQuery1
     If .RecordCount>0 Then
     .MoveFirst
     Do Until .EOF
     If strTypes = " " Then
     strTypes = !Type
   Else
     strTypes = strTypes & !Type
   End If
   .Movenext
   Loop
   Operations = strTypes
   Else
   Operations = ""
   End If
   End With
   
   rsQuery1.Close
   cnCurrent.Close

End Function

Any help would be greatly appreciated! I've been struggling with this one...

 

[thornmastr]

I used the first 10 values from a table of states to test the following function which parallels yours, but it is DAO not ADO. I’m sure you can transpose between the two, and it did return one string of ten states (not very easy to read).

Public Function Operations() As String: Operations = ""
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT TOP 10 dbo_tblState.StateName FROM dbo_tblState"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
If .RecordCount = 0 Then
Else
.MoveFirst
While Not .EOF
If Operations = "" Then
Operations = !statename
Else
Operations = Trim(Operations) & !statename
End If
.MoveNext
Wend
.Close
End If
End With

Set rs = Nothing
Set db = Nothing

End Function
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[cs581]

Sorry for my delayed response. This project was on hold a while. Thank you for your input. I've almost got it. Now my only problem is hiding the individual fields that I now have combined into one field. I'll let you know how it goes!