Concatenating values to a memo field returns few results

[MinishZelda]

I've got the following problem with M$ Access:

I have many tables, all of them with only 2 columns (one of the columns works as a PK to identify the rows - In this example, it will be the column "NAME") and i need to concatenate every value from every column into one field in another table, something like:

TABLE 1:

        Name      |    Number
    --------------------------
        Charlie   |      1
        Charlie   |      2
        James     |      3
        James     |      4
        Michelle  |      5
        Michelle  |      6

TABLE 2:

        Name      |      Country
    ------------------------------
        Charlie   |      Brazil
        Charlie   |      France
        James     |      Japan
        Michelle  |       USA

TABLE 3 - TABLES that totalizes the concatenation:

        Name      |    Number   |     Country
    ----------------------------------------------
        Charlie   |     1,2     |  Brazil,France
        James     |     3,4     |      Japan
        Michelle  |     5,6     |       USA

Until now, I was able to create an append that uses a function in VBA to concatenate these values to the fields in Table 3. The function is avaiable in: http://allenbrowne.com/func-concat.html, as below:

Option Explicit
 
Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSql As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
 
    'Initialize to Null
    ConcatRelated = Null
 
    'Build SQL string, and get the records.
    strSql = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSql = strSql & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
 
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
 
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If
 
Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function
 
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

The problem is: Even using a Memo field in Table 1, Table 2 and Table 3, my concatenation doesn't let me concatenate too many values, and I dont get every value needed into table 3.

Can someone please help me?

[zmbd]

Would you please do the basic troubleshooting as outlined here: > Before Posting (VBA or SQL) Code

After you can compile everything without error, then it would help if you would show us your work to date:
Just open your code,
Copy
Paste here in a post
Select the inserted text
Click on the [CODE/] in the toolbar to format.

What we find is that there are often typos.

Please no attachments at this point in time. (^_^)

[MinishZelda]

I did the changes. Sorry for the unexperiencie!

[zmbd]

No need to applogize. (^_^)

Do you have everything working now?

one of the columns works as a PK to identify the rows - In this example, it will be the column "NAME"

Yet in the example data you have two records where the field [name]="Michelle" so this field can not be a primary key.

Another thing I should have pointed out to you is to not use reserved words as field names:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access