Results 1 to 12 of 12
  1. #1
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544

    Concatenate email addresses


    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:
    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:

    Code:
    =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

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Do you ever pass an argument to strWhere?

  3. #3
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    I know I've done it before - but for the life of me I can't find where. Should I google strwhere?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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).
    Code:
    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.

  5. #5
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    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. :-)

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You bet and glad to hear you have your IIf fixed.

  7. #7
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    Still having issues with this - concatenate still pulls all email addresses within the firm, not just those marked for "Invoice".

    Thanks!

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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?

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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?
    Code:
    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

  10. #10
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    "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
    Code:
    =IIf([subinvoice]=-1,ConcatRelated("subemail","subcontacts",Null))
    . And again, it's a continuous form.

  11. #11
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    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 :-)

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Email addresses
    By RBCC in forum Programming
    Replies: 1
    Last Post: 12-13-2015, 06:08 AM
  2. Send email using CDO, get email addresses from table
    By marvinac1 in forum Programming
    Replies: 3
    Last Post: 12-22-2014, 12:54 PM
  3. Replies: 3
    Last Post: 08-16-2013, 04:15 PM
  4. send email to email addresses in database?
    By cnstarz in forum Access
    Replies: 5
    Last Post: 03-02-2011, 09:46 PM
  5. Combining two Email Addresses
    By Frodo in forum Access
    Replies: 0
    Last Post: 09-16-2007, 07:07 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums