rst.MoveNext

I'm trying to learn loops and movenext stuff.
Found this code on EE and modified it to see how it works.

I have three records in the table all marked "yes".
But the code sends the first record three times.

Can anyone show me why and how to correct?
Thank you,
Shawn

Private Sub TESTcmd_Click()
Dim dbthisdb As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strSendTo As String
strSql = "SELECT [name] FROM [xxxTESTxxx]" _
       & "WHERE [F2] = Yes" _
 
Set dbthisdb = DBEngine.Workspaces(0).Databases(0)
Set rst = dbthisdb.OpenRecordset(strSql, dbOpenForwardOnly)
 
Do While Not rst.EOF()
  DoCmd.SendObject , , , EmailAddress, , , "Subject Test line" & [F1], "test message text", False
  
  rst.MoveNext
Loop
 
Set rst = Nothing
Set dbthisdb = Nothing
 
End Sub

Open in new window

ShawnGrayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
You've got me beat..
I can't see why you're even sending the first record.
There doesn't seem to be any reference to your recordset in the sendobject.
0
Rey Obrero (Capricorn1)Commented:
Set dbthisdb = DBEngine.Workspaces(0).Databases(0)
Set rst = dbthisdb.OpenRecordset(strSql, dbOpenForwardOnly)
rst.movefirst
Do Until rst.EOF
  DoCmd.SendObject , , , EmailAddress, , , "Subject Test line" & [F1], "test message text", False
 
  rst.MoveNext
Loop
0
Rey Obrero (Capricorn1)Commented:
Set dbthisdb = DBEngine.Workspaces(0).Databases(0)
Set rst = dbthisdb.OpenRecordset(strSql, dbOpenForwardOnly)
rst.movefirst
Do Until rst.EOF
  DoCmd.SendObject , , , EmailAddress, , , "Subject Test line " & rst(0) & ", "test message text", False
 
  rst.MoveNext
Loop
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
oop missing " &

 DoCmd.SendObject , , , EmailAddress, , , "Subject Test line " & rst(0) & "," & "test message text", False
0
ShawnGrayAuthor Commented:
Thank you.  I'm getting "invalid operation" at

rst.MoveFirst
0
Rey Obrero (Capricorn1)Commented:

the strSql is wrong too.
Private Sub TESTcmd_Click()
Dim dbthisdb As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strSendTo As String
strSql = "SELECT [name] FROM [xxxTESTxxx]"
strSql =strSql & " WHERE [F2] = Yes"
 
Set dbthisdb = DBEngine.Workspaces(0).Databases(0)
Set rst = dbthisdb.OpenRecordset(strSql)
 
if rst.eof then
   msgbox "No records"
   exit sub
end if
 
rst.movefirst
 
Do Until rst.EOF
  DoCmd.SendObject , , , EmailAddress, , , "Subject Test line " & rst(0) & ", "test message text", False
 
  rst.MoveNext
Loop
 
 
Set rst = Nothing
Set dbthisdb = Nothing
 
End Sub

Open in new window

0
Rey Obrero (Capricorn1)Commented:
you have to correct this line too

  DoCmd.SendObject , , , EmailAddress, , , "Subject Test line " & rst(0) & ", "test message text", False
 

changing EmailAddress to a real email address
0
ShawnGrayAuthor Commented:
I've attached a sample.
Would you look at this?
Thank you,
Shawn
emailloop.mdb
0
Rey Obrero (Capricorn1)Commented:
see the msgbox after clicking the button
emailloopRev.mdb
0
ShawnGrayAuthor Commented:
Hey Cap,
Sorry, I must have some mental block on this topic.
I tweaked the code below, it now sends to the correct address but it ignores my "where" clause and seems to email record one to all three email addresses.
(changed the field [Name] to [NameField].  Learned that it inserts the name of the table when I use "Name".)
Private Sub TESTcmd_Click()
Dim dbthisdb As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strSendTo As String
strSql = "SELECT [emailaddress] FROM [xxxTESTxxx]"
strSql = strSql & " WHERE [F2] = Yes"
 
Set dbthisdb = DBEngine.Workspaces(0).Databases(0)
Set rst = dbthisdb.OpenRecordset(strSql)
 
If rst.EOF Then
   MsgBox "No records"
   Exit Sub
End If
 
rst.MoveFirst
 
Do Until rst.EOF
  DoCmd.SendObject , , , rst(0), , , "-F1=" & [F1] & "  -NameField=" & [NameField] & "  -F2=" & [F2], "test message text " & [emailaddress], False
  'Debug.Print rst(0)
  'MsgBox rst(0)
  rst.MoveNext
Loop
 
Set rst = Nothing
Set dbthisdb = Nothing
 
End Sub

Open in new window

0
Rey Obrero (Capricorn1)Commented:
can you explain what you are trying to do in this statement


  DoCmd.SendObject , , , rst(0), , , "-F1=" & [F1] & "  -NameField=" & [NameField] & "  -F2=" & [F2], "test message text " & [emailaddress], False


0
ShawnGrayAuthor Commented:
The rst(0) is new to me but it seems to grab the email address. :/

The rest of the stuff is supposed take a few fields from each record and email it to a client.   Ulitmately these fields will be [clientaccountnumber] and [accountbalance].  Very specific to each email address.
0
Rey Obrero (Capricorn1)Commented:
change this

strSql = "SELECT [emailaddress] FROM [xxxTESTxxx]"
strSql = strSql & " WHERE [F2] = Yes"


to

strSql = "SELECT * FROM [xxxTESTxxx]"
strSql = strSql & " WHERE [F2] = Yes"

to refer to the fields in the recordset, use this

rs!emailaddress
rs!clientaccountnumber
rs!accountbalance
rs!F2
rs!F1
0
ShawnGrayAuthor Commented:
Like this?
DoCmd.SendObject , , , rst(0), , , rs!f1, "test message text ", False
says "object required"

0
Rey Obrero (Capricorn1)Commented:

DoCmd.SendObject , , , rst!emailaddress, , , rs!f1, "test message text ", False
0
ShawnGrayAuthor Commented:
still get an error on that line "object required".
Dim dbthisdb As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strSendTo As String
strSql = "SELECT * FROM [xxxTESTxxx]"
strSql = strSql & " WHERE [F2] = Yes"
 
Set dbthisdb = DBEngine.Workspaces(0).Databases(0)
Set rst = dbthisdb.OpenRecordset(strSql)
 
If rst.EOF Then
   MsgBox "No records"
   Exit Sub
End If
 
rst.MoveFirst
 
Do Until rst.EOF
DoCmd.SendObject , , , rst!emailaddress, , , rs!F1, "test message text ", False
  rst.MoveNext
Loop
 
Set rst = Nothing
Set dbthisdb = Nothing

Open in new window

0
Rey Obrero (Capricorn1)Commented:


use this

    DoCmd.SendObject acSendNoObject, , , rst!emailaddress, , , rs!F1, "test message text ", False
0
ShawnGrayAuthor Commented:
same error
emailloopRev.mdb
0
Rey Obrero (Capricorn1)Commented:
oh...nooo...   (  rs!F1 ) '<<< we are not using rs

use this


DoCmd.SendObject acSendNoObject, , , rst!emailaddress, , , rst!F1, "test message text ", False
0
ShawnGrayAuthor Commented:
Ok.  Cool. The fields are working right.  But it still ignores WHERE.
All records are sent even if [f2]=No
0
Rey Obrero (Capricorn1)Commented:
field  F2 is text not boolean YesNo

change

strSql = "SELECT * FROM [xxxTESTxxx]"
strSql = strSql & " WHERE [F2] = Yes"

to

strSql = "SELECT * FROM [xxxTESTxxx]"
strSql = strSql & " WHERE [F2] = 'Yes'"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShawnGrayAuthor Commented:
Cap,
Thank you.  I really appreciate your help.
You're awesome.
Shawn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.