1

I have a table Contacts in Access and a search form. After the user specifies the search criteria, the table only shows records that meet the criteria

Is their a way to retrieve all email addresses of searched contacts as semi-colon separated list so that i can just copy and paste in new email's To field.

Any help is appreciated

Roshan.

Roshan Tated
  • 11
  • 1

1 Answers1

0

You might as well make use of an UDF, just keeping it out of the procedure. Get the criteria by which you filter the result. Then you simply pass the criteria. I am not sure how you have built your criteria. A stab in the dark is get all email of people named "Paul". So your code will be.

Public Sub getEmailString(FieldName As String, Tablename As String, Criteria As String)
    Dim tmpRS As DAO.Recordset
    Dim tmpStr As String, retStr As String

    tmpStr = "SELECT " & FieldName & " FROM " & Tablename & " WHERE " & Criteria

    Set tmpRS = CurrentDB.OpenRecordset(tmpStr)

    If tmpRS.RecordCount > 0 Then
        Do While Not tmpRS.EOF
            retStr = retStr & tmpRS.Fields(0) & ";"
            tmpRS.MoveNext
        Loop
    End If 
    getEmailString = retStr
    Set tmpRS = Nothing
End Sub

To use it, you simply use.

Dim someEmailString As String
someEmailString = getEmailString("EmailFieldName", "ContactsTableName", "FirstName = 'Paul'")

If you have something it should return,

paul.someone@somedomain.com;paul.someoneelse@somenewdomain.co.uk;

Hope this helps.

PaulFrancis
  • 5,748
  • 1
  • 19
  • 36
  • HI Paul, thanks for the response. The problem is that 'Form text fields' are used as argument for running the query. When I am running the code you have suggested on the query results. It gives the error 'Arguments required. Argument number x'. How can this problem be tackled? – Roshan Tated Jun 01 '15 at 16:31
  • Please update your original post with what you have done so far. – PaulFrancis Jun 01 '15 at 16:35