Recordset loop question

[Guest]

Hi. I have a query, and I want to collect all of the email addresses from it
into one String at the push of a command button. The query (and I need to
use the results from the query, rather than from a table), say TheQuery, has
many fields, one of which being "Email Address".

I have seen many postings here about recordset and DAO/DOA, but I am not
sure how to adapt those codings for my own use. I want to loop through every
email address field in each record and keep appending the email address onto
a string, adding a " , " between each email address.

Ideally, the string with results would than be exported as the "to:" field
in Lotus Notes, or into a textbox.

 

[Dirk Goldgar]

Hi. I have a query, and I want to collect all of the email addresses
from it into one String at the push of a command button. The query
(and I need to use the results from the query, rather than from a
table), say TheQuery, has many fields, one of which being "Email
Address".

I have seen many postings here about recordset and DAO/DOA, but I am
not sure how to adapt those codings for my own use. I want to loop
through every email address field in each record and keep appending
the email address onto a string, adding a " , " between each email
address.

Ideally, the string with results would than be exported as the "to:"
field in Lotus Notes, or into a textbox.

Make sure you have a reference set to the Microsoft DAO Object Library
(version 3.6 if you're using Access 2000 or later). Then use code along
these lines:

Dim rs As DAO.Recordset
Dim strAddresses As String

Set rs = CurrentDb.OpenRecordset("TheQuery")

With rs
Do Until .EOF
If Not IsNull(![Email Address]) Then
strAddresses = strAddresses & ", " & ![Email Address]
End If
.MoveNext
Loop
.Close
End With

Set rs = Nothing

' Trim off the first comma+space.
If Len(strAddresses) > 0 Then
strAddresses = Mid(strAddresses, 3)
End If

 

[Guest]

How do I set a reference? (I've never done anything like this) Where do I
put the reference? I am using Access 2003, but other people that are going
to be using this database are on 2002 or 2000, so I should probably set it
for them.

Hi. I have a query, and I want to collect all of the email addresses
from it into one String at the push of a command button. The query
(and I need to use the results from the query, rather than from a
table), say TheQuery, has many fields, one of which being "Email
Address".

I have seen many postings here about recordset and DAO/DOA, but I am
not sure how to adapt those codings for my own use. I want to loop
through every email address field in each record and keep appending
the email address onto a string, adding a " , " between each email
address.

Ideally, the string with results would than be exported as the "to:"
field in Lotus Notes, or into a textbox.

Make sure you have a reference set to the Microsoft DAO Object Library
(version 3.6 if you're using Access 2000 or later). Then use code along
these lines:

Dim rs As DAO.Recordset
Dim strAddresses As String

Set rs = CurrentDb.OpenRecordset("TheQuery")

With rs
Do Until .EOF
If Not IsNull(![Email Address]) Then
strAddresses = strAddresses & ", " & ![Email Address]
End If
.MoveNext
Loop
.Close
End With

Set rs = Nothing

' Trim off the first comma+space.
If Len(strAddresses) > 0 Then
strAddresses = Mid(strAddresses, 3)
End If


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Do While Not rst.EOF
MyString = MySting & [Email Address] & " , "
rst.MoveNext
End Do

'Remove the last comma
MyString = Left(MyString,Len(MyString) -3)

 

[Dirk Goldgar]

How do I set a reference? (I've never done anything like this)
Where do I put the reference? I am using Access 2003, but other
people that are going to be using this database are on 2002 or 2000,
so I should probably set it for them.

If you're using Access 2003, you probably already have the reference
set. That reference may or may not be broken if you open the database
on other PCs using other versions of Access. If you find a problem in
such a case, then on the failing PC, open any code module, then (while
in the VB Editor environment) click Tools -> References..., locate the
reference for Microsoft DAO 3.6 Object Library, and ...

+ if it has MISSING next to it, uncheck it, close the References
dialog, then open it again, find the reference (it will probably have
moved), and check it again.

OR

+ if it is unchecked, put a check mark in the box next to it.