Multiple entries concatenated in a field

I want to show all of the committees to which a club member belongs in a single field and visible on the form. I am trying to do it with a list box without much luck. Is there a better way? Help, please. Thank you.
lherouxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Have you ruled out the possibility of using a subform?

http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

These are designed to easily handle 1 to many relationships like you are describing.
0
 
jerryb30Commented:
Or:
Function Committees(vMember as string) as string
dim rs as dao.recordset
dim s as string
dim s2 as string
s =  "select Member, committee from tblMember where member = '" & vMember & "'"
set rs = currentdb.openrecordset(s)
rs.movefirst
do while not rs.eof
if s2 = "" then
s2 = rs!committee
else
s2 = s2 & vbCRLF & rs!committee
endif
rs.movenext
loop
Committees = s2
rs.close
end function

Set the recordsource of the form to:
Select member, committees(member) from tblMember
group by Member, committees(member)
0
 
Dale FyeCommented:
Or you could use a rowSource of your listbox, something like:

SELECT CommitteeName
FROM tblCommitteeMembers
WHERE MemberID = Forms!yourFormName.MemberID

Then, in the Form_Current event, requery your listbox.

Private sub Form_Current

    me.lst_MemberCommittees.Requery

End Sub
0
 
Patrick MatthewsCommented:
Similar approach to jerryb30's, but using a general purpose function to do the concatenation as described in my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

1) Add this UDF to a regular VBA module:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window


2) Use it in a query to make the record source for a form or report.  The exact query will depend on your schema.  Please post some details on how your tables are structured
0
 
lherouxAuthor Commented:
Yikes. Lot of effort to avoid occasional typing. I guess I'll stay with a text box. Thank you all for your responses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.