[AccessD] Concatenating data into lists

Hi David

OK, here's a simple ADO-only version - with example in-line SQL code for Arthur's recipe/ingredient tables ...

<code>

Public Function ConcatenateRecords( _
  ByVal lngSource, _
  ByVal lngKey As Long, _
  Optional ByVal strSeparator As String = ";") _
  As String

' Concatenates values from source lngSource from all
' records using parameter value lngKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
'
' 2005-12-07. Cactus Data ApS, CPH.

  ' List possible sources.
  Const cstrSQL01 As String = "" & _
    "SELECT tblIngredients.IngredientName " & _
    "FROM tblRecipeIngredients " & _
    "INNER JOIN tblIngredients " & _
    "  ON tblRecipeIngredients.IngredientID = tblIngredients.IngredientID " & _
    "WHERE tblRecipeIngredients.RecipeID = [lngKey] " & _
    "ORDER BY tblIngredients.IngredientName;"
  
  ' Add other sources.
  ' Const cstrSQL02 as string = "" & _

  ' Keyword to be replaced by actual key.
  Const cstrKey = "[lngKey]"
  
  Dim cnn         As New ADODB.Connection
  Dim rst         As New ADODB.Recordset
    
  Dim booPluralis As Boolean
  Dim strFields   As String
  Dim strSource   As String
  
  On Error GoTo Err_ConcatenateRecords
  
  Select Case lngSource
    Case 1
      strSource = cstrSQL01
    ' Case 2 ' etc.
  End Select

  If Len(strSource) = 0 Then
    ' Nothing to do.
  Else
    Set cnn = CurrentProject.Connection
    strSource = Replace(strSource, cstrKey, CStr(lngKey))
     
    rst.Open strSource, cnn, adOpenDynamic
    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(.Fields(0).Value)
        booPluralis = True
        .MoveNext
      Wend
      .Close
    End With
  End If
  
  Set rst = Nothing
  Set cnn = Nothing
  
  ConcatenateRecords = strFields
  
Exit_ConcatenateRecords:
  Exit Function
  
Err_ConcatenateRecords:
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecords
  
End Function

</code>

/gustav