• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

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

0
ShawnGray
Asked:
ShawnGray
  • 12
  • 9
1 Solution
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
ShawnGrayAuthor Commented:
Cap,
Thank you.  I really appreciate your help.
You're awesome.
Shawn
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now