Dlookup?

[Secret Squirrel]

I'm trying to send an email via VBA code to multiple users if the value in
the "wageupdate" field in my table is set to true. I just tested it and it's
only sending it to the first record it finds with the value of true. Should I
not be using "Dlookup"?
Is there another function I should be using to return multiple records?

sEmails = DLookup("", "tblUsers", "[WageUpdate] = True") & ";"

 

[Beetle]

DLookup won't wok because, as you've discovered, it only returns the first
record that meets the criteria. It would be better to create a recordset
using your criteria, and then loop through the recordset to generate
e-mails. Something like (untested example code);

Dim rs As DAO.RecordSet
Dim strSQL As String
Dim i As Integer

strSQL = "Select EMail From tblUsers Where WageUpdate = True"

Set rs =CurrentDb.OpenRecordSet (strSQL)

With rs
.MoveLast
.MoveFirst
For i = 1 to .RecordCount
'code to generate your e-mail
With olMailItem
.To = rs!EMail
.Subject = "blah blah blah"
.Display
End With
.MoveNext
Next i
End With

 

[Secret Squirrel]

Thank you for your help Beetle. It worked fine. But is there a way to have it
only send one email out to every user it finds instead of an individual email
for each user? Maybe have all the email addresses it finds put into the "To"
field of one email?

DLookup won't wok because, as you've discovered, it only returns the first
record that meets the criteria. It would be better to create a recordset
using your criteria, and then loop through the recordset to generate
e-mails. Something like (untested example code);

Dim rs As DAO.RecordSet
Dim strSQL As String
Dim i As Integer

strSQL = "Select EMail From tblUsers Where WageUpdate = True"

Set rs =CurrentDb.OpenRecordSet (strSQL)

With rs
.MoveLast
.MoveFirst
For i = 1 to .RecordCount
'code to generate your e-mail
With olMailItem
.To = rs!EMail
.Subject = "blah blah blah"
.Display
End With
.MoveNext
Next i
End With

--
_________

Sean Bailey

I'm trying to send an email via VBA code to multiple users if the value in
the "wageupdate" field in my table is set to true. I just tested it and it's
only sending it to the first record it finds with the value of true. Should I
not be using "Dlookup"?
Is there another function I should be using to return multiple records?

sEmails = DLookup("", "tblUsers", "[WageUpdate] = True") & ";"[/QUOTE][/QUOTE]

 

[Beetle]

Yes, you could modify it like;

Dim rs As DAO.RecordSet
Dim strSQL As String, strEMails As String
Dim i As Integer

strSQL = "Select EMail From tblUsers Where WageUpdate = True"

strEMails = ""

Set rs =CurrentDb.OpenRecordSet (strSQL)

With rs
.MoveLast
.MoveFirst
For i = 1 to .RecordCount
If strEMails <> "" Then strEMails = strEMails & ";"
strEMails = strEMails & rs!EMail
.MoveNext
Next i
End With

With olMailItem
.To = strEMails
.Subject = "blah blah blah"
.Display
End With

 

[Jorge]

How can this code be adapted to get values from a form?

Yes, you could modify it like;

Dim rs As DAO.RecordSet
Dim strSQL As String, strEMails As String
Dim i As Integer

strSQL = "Select EMail From tblUsers Where WageUpdate = True"

strEMails = ""

Set rs =CurrentDb.OpenRecordSet (strSQL)

With rs
.MoveLast
.MoveFirst
For i = 1 to .RecordCount
If strEMails <> "" Then strEMails = strEMails & ";"
strEMails = strEMails & rs!EMail
.MoveNext
Next i
End With

With olMailItem
.To = strEMails
.Subject = "blah blah blah"
.Display
End With

--
_________

Sean Bailey

I'm trying to send an email via VBA code to multiple users if the value in
the "wageupdate" field in my table is set to true. I just tested it and it's
only sending it to the first record it finds with the value of true. Should I
not be using "Dlookup"?
Is there another function I should be using to return multiple records?

sEmails = DLookup("", "tblUsers", "[WageUpdate] = True") & ";"[/QUOTE][/QUOTE]

 

[Beetle]

You can loop through the form's RecordsetClone;

Dim i As Integer

With Me.RecordsetClone
.MoveLast
.MoveFirst
For i = 1 to .RecordCount
'Code to do something
.MoveNext
Next i
End With

--
_________

Sean Bailey

How can this code be adapted to get values from a form?

Yes, you could modify it like;

Dim rs As DAO.RecordSet
Dim strSQL As String, strEMails As String
Dim i As Integer

strSQL = "Select EMail From tblUsers Where WageUpdate = True"

strEMails = ""

Set rs =CurrentDb.OpenRecordSet (strSQL)

With rs
.MoveLast
.MoveFirst
For i = 1 to .RecordCount
If strEMails <> "" Then strEMails = strEMails & ";"
strEMails = strEMails & rs!EMail
.MoveNext
Next i
End With

With olMailItem
.To = strEMails
.Subject = "blah blah blah"
.Display
End With

--
_________

Sean Bailey

I'm trying to send an email via VBA code to multiple users if the value in
the "wageupdate" field in my table is set to true. I just tested it and it's
only sending it to the first record it finds with the value of true. Should I
not be using "Dlookup"?
Is there another function I should be using to return multiple records?

sEmails = DLookup("", "tblUsers", "[WageUpdate] = True") & ";"[/QUOTE][/QUOTE][/QUOTE]