Concatenate email addresses

[Gina Maylone]

Good morning!

I need to create a string of email addresses to be used in GMail. Some people within an organization need to receive an invoice and others receive a status report. I have check boxes on my form to indicate who gets what. I am using Allen Browne's concatenate function, which is awesome! But the results I am getting are not what I want. In the attached db you will see that the string that is produced includes everyone (not just those who are supposed to get an invoice).

Here's the main concat code:

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

And the control source for my unbound text box to hold the addresses:

=IIf([subinvoice]=True,ConcatRelated("subemail","subcontacts","ConID = " & [ConID] And [subid]=[subid]),Null)

Any thoughts will be greatly appreciated! AND HAPPY FRIDAY ALL! (SetupContacts is the main form, sbbcontacts is the form where my problem is)

Database8.zip

[ItsMe]

Do you ever pass an argument to strWhere?

[Gina Maylone]

I know I've done it before - but for the life of me I can't find where. Should I google strwhere?

[ItsMe]

You stated that the recordset you are iterating through contains more records than you desire (additional email addresses). The function you posted has an optional Parameter (strWhere).

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant

It seems passing an argument to the parameter will add criteria the SQL statement that your DAO recordset depends on.

[Gina Maylone]

That's what
"ConID = " & [ConID]

is (the strwhere) Sorry, I misunderstood your first question. And I removed "And [subid]=[subid]"Thank you for taking the time. :-)

[ItsMe]

You bet and glad to hear you have your IIf fixed.

[Gina Maylone]

Still having issues with this - concatenate still pulls all email addresses within the firm, not just those marked for "Invoice".

Thanks!

[orange]

Tell us about the ones marked for Invoice.
What is the field involved??

Is CondID a lookup field?? Is it a number or text?

What is the criteria you use to find the records marked for Invoice?

[ItsMe]

Since you are working in a form, why not use complete statements in the form's VBA module instead of using an expression within a control?

Dim strEmails As String

    If Me![subinvoice] = True Then
        strEmails = ConcatRelated("subemail", "subcontacts", "ConID = " & Me![ConID] & " And subid = " & Me![subid])
    Else
        strEmails = ""
    End If
    
Debug.Print strEmails
Me.TextControlName.Value = strEmails

[Gina Maylone]

"subinvoice" is a yes/no field. conid (contactid) is a number field, my criteria is "ConID = " & [ConID]. My control source in the field "invoiceemails" is

=IIf([subinvoice]=-1,ConcatRelated("subemail","subcontacts",Null))

. And again, it's a continuous form.

[Gina Maylone]

Hi ItsMe - I tried that as well, I still get all of the sub-email addresses instead of just those with invoice marked yes. Thanks again :-)

[ItsMe]

It seems the issue is the value of your fields. It seems obvious you are not adding a WHERE clause to the ConcatRelated function. So that means that there is an issue with the argument. Looking at what has been tried, that leaves the fields.

If you have a control on a continuous form, you may not be getting the Value you expect.
Me.subinvoice.Value

The following is looking at the field, as it pertains to the Form's Recordset
Me![subinvoice]

If you desire to get the Value of a control, you need to understand which field the control is bound to. Then, which Record is the Current Record. The record that has focus on a continuous form will be the Current Record. The one control (Me.subinvoice.Value) will have one value. The value of the Current Record's field.