Concatenate e-mail addresses from query into string

[AChir]

I have a query, qryFindEMails with a field containing e-mail addresses. I want to combine these e-mails into a string:

email1-at-wherever.com; email2-at-wherever.com; email3-at-wherever.com

... and then append this string to a textbox on a form. (I have removed all the @ symbols hereas they kept turning my example string into a series of hyperlinks)

I have tried doing it by populating a multi-select listbox using the query but can't get it to work and, in any case, I want every item in the list so why have to select them all? I have trawled through the boards at length, searching with various combinations of words, and can't find anything which has allowed me to solve the problem. I have found stuff which looks as if it might help but my VBA just isn't good enough, sadly

Any help very gratefully received

Thanks

 

[DCrake]

Public Function GrabEmailAddresses() As String

Dim Rs As DAO.Recordset
Dim eAdr As String

Set Rs = CurrentDb.Openrecordset("YourQueryName")

If Not Rs.EOF And Not Rs.BOF  Then

   Do Until Rs.EOF
       eAdr = eAdr & " ; "
       Rs.MoveNext
   Loop
   Rs.Close
End If

Set Rs = Nothing

eAdr = Left(eAdr,Len(eAdr)-2) ' Drop the last semi colon and space

GrabEmailAddresses= eAdr

End Function

If you have a query that contains all the valid email addresses then refer to this query as your recordset. Access will open it up and turn it from vertical to horizontal using a semi-colon as a seperator.

David

 

[namliam]

It should be pretty easy just to loop thru your query and append all the emails....

Lets see... Aircode:

Dim rs as dao.recordset
dim str as string
set rs = currentdb.openrecordset("Your query of emails")
Do while not rs.eof
    str = str & ";" & rs!YourEmailField
    rs.movenext
loop
str = mid(str,2)
msgbox str
rs.close
set rs = nothing

Good luck!

 

[namliam]

eAdr = eAdr & " ; " & rs!YourEmailField

LOL David beat me to it.... you are however missing one important thing in above line... the bolded part...

and you will want to remove the first not the last semi column I think...

 

[AChir]

Thanks very much to you both for such a speedy reply.
When it gets to
Set rs = CurrentDb.OpenRecordset("qryFindEMails")
I get error message 3061. "Too few parameters. Expected 1"

David's function doesn't seem to use a parameter... Not sure how to proceed

 

[DCrake]

Thanks mailman for the ommision, as I said aircode rules.

back to the last post you can call a function without arguments but still return a value, unlike subs where you can call a sub but not return a value.

It looks like your query is expecting a parameter (argument) but my example is assuming this has been passed prior to the command line.

Can you open the query by normal methods with out the error?

David

 

[AChir]

Yes - it opens fine.

I have tried searching for the error message and have played with this code based on one of the postings:

Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb
Dim vabQuery As String
Dim para1 As QueryDef

vabQuery = "FindEMails"
 
Set para1 = db.QueryDefs(vabQuery)
para1![Forms!frmMain!AdmissionNo] = Forms![frmMain]![SelectStudent]

' Presumably I need something here to fish out the email address corresponding to the admissionNo?

Set rs = db.OpenRecordset(vabQuery, dbOpenDynaset)
 
Do While Not rs.EOF
    str = str & ";" & rs!EMail
    rs.MoveNext
Loop
str = Mid(str, 2)
 
rs.Close
Set rs = Nothing

The form has a listbox called SelectStudent - the value chosen is used as the AdmissionNo field in my query, so I don't know if the para 1 line is the right way round (it certainly doesn't work)

 

[namliam]

Queries you open from code cannot have references to forms in them... then you get this error message....

You can use something like your suggesting... but the syntax would be...
para1.Parameters(1) = Forms![frmMain]![SelectStudent]

And you should really stick to your naming convention... frmMain but...FindEMails ?? that then should be qryFindEMails?? No??

 

[DCrake]

As mailman says you cannot have form references in a query that is not called by the said form. To get around this I have attached a document to show you a more constructive approach to using public variables. This should do the trick


David

 

[AChir]

Huge thanks to you both... your document was really helpful, David, and it now works like a dream using a combination of the ideas you suggested. I have learnt something along the way, too. I really appreciate your patience and help

 

[DCrake]

glad to be of help