Help with loop

[Jock]

Hi group,

I am needing assistance witht he logic in my loop as below.

Basically what I am trying to achieve is to populate an unbound
textbox with email addresses of those in the qualifying SQL statement.

However, I am getting an "Item not found in this collection" error

Any ideas where I am going wrong.


Private Sub Command2_Click()

Dim qry As String, rst As Object

qry = "SELECT Employees.Email FROM DistributionList INNER JOIN
Employees ON DistributionList.ID = Employees.DistributionList.Value
WHERE (((DistributionList.DistributionList)='Chemistry'));"

Set rst = CurrentDb.OpenRecordset(qry)

rst.MoveFirst

Do While Not rst.EOF

Form!EmailAddresses.Value = rst!Employees.Email

rst.MoveNext

Loop

End Sub

 

[John Spencer]

Private Sub Command2_Click()

Dim qry As String, rst As Object

qry = "SELECT Employees.Email FROM DistributionList INNER JOIN
Employees ON DistributionList.ID = Employees.DistributionList.Value
WHERE (((DistributionList.DistributionList)='Chemistry'));"

Set rst = CurrentDb.OpenRecordset(qry)

rst.MoveFirst

Do While Not rst.EOF

Form!EmailAddresses = rst!Email

rst.MoveNext

Loop

End Sub

One problem is that you are only going to end up with one value in
Form!EmailAddresses. If you want to get all the addresses and have then
separated by some character (a semi-colon for instance), then you need to
concatenate (combine)the values.




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jock]

Thanks for your reply John....

Found a more suitable option that selects and concatonates:

============================================================

Private Sub Command2_Click()

Dim db As Database
Dim sSQL As String
Dim rst As Recordset
Dim sOut As String
Dim iLen As Integer
Const SEP = ";"

sSQL = "SELECT Employees.Email FROM DistributionList INNER JOIN
Employees ON DistributionList.ID = Employees.DistributionList.Value
WHERE (((DistributionList.DistributionList)='Chemistry'));"
Set db = CurrentDb()
Set rst = db.OpenRecordset(sSQL)
Do While Not rst.EOF
sOut = sOut & rst![Employees.Email] & SEP
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
iLen = Len(sOut) - Len(SEP)
If iLen > 0 Then
ShowRelated = Left$(sOut, iLen) 'remove trailing
separator.
End If
EmailAddresses.Value = sOut
End Sub

============================================================