add a filter when looping thru a recordset

[bonjourno]

Hi there,

I'm using the code below to loop through a record set and select Email addresses of certain users to send them email automatically. How can I modify this code so I can add a filter.??
I want to select only users that belong to certain Group(Group is a Text field in the tbl_users) and I want ReceiveEmail=True (ReceiveEmail is a boolean field in the same table also)


rst.Open "tbl_Users", CurrentProject.Connection, adOpenForwardOnly
Do While Not rst.EOF
strTo = strTo & Nz(rst.Fields("EmailAddress").Value) & "; "
rst.MoveNext
Loop
SendEmails(strTo, strSubject, strMessage)


Many Thanks in advance.
Ronaldo

 

[PHV]

Replace this:
rst.Open "tbl_Users", CurrentProject.Connection, adOpenForwardOnly
with something like this:
rst.Open "SELECT * FROM tbl_Users WHERE ReceiveEmail=True AND [Group]='certain group'", CurrentProject.Connection, adOpenForwardOnly

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

You are opening the whole table. You need to provide an SQL statement and restrict the users to the ones you want. For example

Dim myGroup As String
myGroup = "[red]Some Group Value[/red]"
rst.Open "SELECT * FROM tbl_Users WHERE Group = '" & myGroup & "'" & _
         "                          AND ReceiveEmail=True " , _
          CurrentProject.Connection, adOpenForwardOnly

 

[Andrzejek]

I think you can either do:
[tt]
rst.Open "[blue]Select EmailAddress From tbl_Users Where ReceiveEmail=True And Group = 123[/blue]", CurrentProject.Connection, adOpenForwardOnly
[/tt]

or

[tt]
rst.Open "tbl_Users", CurrentProject.Connection, adOpenForwardOnly
Do While Not rst.EOF[blue]
If rst.Fields("Group").Value & "" = 123
And rst.Fields("ReceiveEmail").Value & "" = True Then[/blue]
strTo = strTo & Nz(rst.Fields("EmailAddress").Value) & "; " [blue]
End If[/blue]
rst.MoveNext
Loop

[/tt]

Have fun.

---- Andy

 

[bonjourno]

Thank you very much sir...
your solution with the IF statement worked just fine.
Regards!!