Display plural records in one text box 1

[zoglchaim770]

Here is one question

I have 2 related tables
a) Parent has fields "Address", "Phone", etc
b) Children has fields "DOB", "Gender", etc.

When making mailing labels addressing it to children I need that children’s names should appeare in one line(one text box) maybe separated by comma if they plural.
I know that I need to write code function, and I am not a code writer
Could you help me?

 

[THWatson]

zoglchaim770
Here's one approach...

1. Create a module with the following structure. Call the module something such as "ModuleForChildNames"

Function Concat _
    (aRSet As String, _
     aField As String, _
     aCondition As String) As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strRes As String
    If aCondition <> "" Then
        strSQL = " AND (" & aCondition & ")"
    End If
    If strSQL <> "" Then
        strSQL = " WHERE" & Mid$(strSQL, 5)
    End If
    strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "]" & strSQL
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    While Not rst.EOF
        strRes = strRes & ", " & rst(aField)
        rst.MoveNext
    Wend
    If strRes <> "" Then
        strRes = Mid$(strRes, 3)
    End If
    Concat = strRes
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

2. Create a query to pull out the Children's names for each Parent record. Call it qryChildren

3. Have a separate line in your label with the following expression...

=Concat("qryChildren","ChildName","MemberID = " & [UniqueID])[code]

Tom

 

[THWatson]

zoglchaim770
Sorry. I pushed the Submit button too quickly, and didn't make sure I ended the code.

Step 3 should read...

=Concat("qryChildren","ChildName","MemberID = " & [UniqueID])

Tom

 

[dhookom]

There is similar code with sample usage in the generic concatenate function found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[THWatson]

Duane
Good examples! Thanks for sharing.

Tom

 

[zoglchaim770]

thank you very much everyone, it was very helpfull