Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Loop without Do Error

Status
Not open for further replies.
spluim

spluim

Technical User
May 14, 2003
25
US
I am getting an error "Loop without Do". Any help would be appreciated.

Private Sub Command14_Click()


Me.Refresh
Me.Requery

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qrySalespersonUpdate")
sql = "SELECT tblSamples.[intDNRSample#], tblSamples.ynColifUnsafe, tblSamples.ynEcolSafe, tblSamples.ynEcolUnsafe, tblSamples.ynHighNitrates, tblSamples.txtWellAddress, tblSamples.datSampleDate, tblSamples.Salesperson, tblSamples.datReported, tblSamples.txtCounty, tblSamples.txtTownship FROM tblSamples LEFT JOIN tblSalespersonEmail ON tblSamples.Salesperson = tblSalespersonEmail.Salesperson WHERE (((tblSamples.ynColifUnsafe) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))Or (((tblSamples.ynHighNitrates) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.BOF And rst.EOF Then
Call MsgBox("No Results Need to be Emailed")

Else

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)


Do While Not rs.EOF
rs.MoveFirst

strEmail = txtEmail

strBody = "Unique Well # " & rs("intDNRSample#") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "County: " & rs("txtCounty") & Chr(13)
strBody = strBody & "Township: " & rs("txttownship") & Chr(13)
strBody = strBody & "Address: " & rs("txtWellAddress") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "Bacti: " & rs("Bact") & Chr(13)
strBody = strBody & "Nitrate: " & rs("intNitrate")




'***creates and sends email
With objEmail
.To = rs("Email")
.CC = "sharonp@samswelldrilling.com"
.Subject = "Unsafe Water and/or High Nitrate Results "
.Body = strBody





.Display

rs.MoveNext

Loop


rs.Close
db.Close


'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit

End With

End Sub
 
Sort by date Sort by votes
Move the

[tt]End With[/tt]

somewhere appropriate prior to the Loop statement (after .Display?)

You'll also need an

[tt]End If[/tt]

Probably after the Loop statement

Roy-Vidar
 
Upvote 0 Downvote
Hi....


Move your rs.MoveFirst outside the loop


Add a End With for With objEmail after .Display

remove End With after objOutlook.Quit

You had your nested loops misarranged...

do
with
end with
loop



With and End With is also considered a loop construction
 
Upvote 0 Downvote
I made the following changes, now I am getting end if without block if


Me.Refresh
Me.Requery

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qrySalespersonUpdate")
sql = "SELECT tblSamples.[intDNRSample#], tblSamples.ynColifUnsafe, tblSamples.ynEcolSafe, tblSamples.ynEcolUnsafe, tblSamples.ynHighNitrates, tblSamples.txtWellAddress, tblSamples.datSampleDate, tblSamples.Salesperson, tblSamples.datReported, tblSamples.txtCounty, tblSamples.txtTownship FROM tblSamples LEFT JOIN tblSalespersonEmail ON tblSamples.Salesperson = tblSalespersonEmail.Salesperson WHERE (((tblSamples.ynColifUnsafe) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))Or (((tblSamples.ynHighNitrates) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.BOF And rst.EOF Then
Call MsgBox("No Results Need to be Emailed")

Else

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)


Do While Not rs.EOF
rs.MoveFirst

strEmail = txtEmail

strBody = "Unique Well # " & rs("intDNRSample#") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "County: " & rs("txtCounty") & Chr(13)
strBody = strBody & "Township: " & rs("txttownship") & Chr(13)
strBody = strBody & "Address: " & rs("txtWellAddress") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "Bacti: " & rs("Bact") & Chr(13)
strBody = strBody & "Nitrate: " & rs("intNitrate")

'***creates and sends email
With objEmail
.To = rs("Email")
.CC = "sharonp@samswelldrilling.com"
.Subject = "Unsafe Water and/or High Nitrate Results "
.Body = strBody
.Display

End If

rs.MoveNext

Loop

End If

rs.Close
db.Close

'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit



End Sub
 
Upvote 0 Downvote
I hate to be a pain lewds but I am relatively new to this can you make the changes on my code.


Me.Refresh
Me.Requery

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qrySalespersonUpdate")
sql = "SELECT tblSamples.[intDNRSample#], tblSamples.ynColifUnsafe, tblSamples.ynEcolSafe, tblSamples.ynEcolUnsafe, tblSamples.ynHighNitrates, tblSamples.txtWellAddress, tblSamples.datSampleDate, tblSamples.Salesperson, tblSamples.datReported, tblSamples.txtCounty, tblSamples.txtTownship FROM tblSamples LEFT JOIN tblSalespersonEmail ON tblSamples.Salesperson = tblSalespersonEmail.Salesperson WHERE (((tblSamples.ynColifUnsafe) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))Or (((tblSamples.ynHighNitrates) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.BOF And rst.EOF Then
Call MsgBox("No Results Need to be Emailed")

Else

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

rs.MoveFirst

Do While Not rs.EOF


strEmail = txtEmail

strBody = "Unique Well # " & rs("intDNRSample#") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "County: " & rs("txtCounty") & Chr(13)
strBody = strBody & "Township: " & rs("txttownship") & Chr(13)
strBody = strBody & "Address: " & rs("txtWellAddress") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "Bacti: " & rs("Bact") & Chr(13)
strBody = strBody & "Nitrate: " & rs("intNitrate")

'***creates and sends email
With objEmail
.To = rs("Email")
.CC = "sharonp@samswelldrilling.com"
.Subject = "Unsafe Water and/or High Nitrate Results "
.Body = strBody
.Display



rs.MoveNext

Loop

End If

rs.Close
db.Close

'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit



End Sub
 
Upvote 0 Downvote
<code>
Set db = CurrentDb
Set rs = db.OpenRecordset("qrySalespersonUpdate")
sql = "SELECT tblSamples.[intDNRSample#], tblSamples.ynColifUnsafe, tblSamples.ynEcolSafe, tblSamples.ynEcolUnsafe, tblSamples.ynHighNitrates, tblSamples.txtWellAddress, tblSamples.datSampleDate, tblSamples.Salesperson, tblSamples.datReported, tblSamples.txtCounty, tblSamples.txtTownship FROM tblSamples LEFT JOIN tblSalespersonEmail ON tblSamples.Salesperson = tblSalespersonEmail.Salesperson WHERE (((tblSamples.ynColifUnsafe) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))Or (((tblSamples.ynHighNitrates) = -1) And ((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & [Forms]![frmReportPopUp]![rdate] & "#))"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If rs.BOF And rst.EOF Then
Call MsgBox("No Results Need to be Emailed")

Else

Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

rs.MoveFirst

Do While Not rs.EOF


strEmail = txtEmail

strBody = "Unique Well # " & rs("intDNRSample#") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "County: " & rs("txtCounty") & Chr(13)
strBody = strBody & "Township: " & rs("txttownship") & Chr(13)
strBody = strBody & "Address: " & rs("txtWellAddress") & Chr(13)
strBody = strBody & " " & Chr(13)

strBody = strBody & "Bacti: " & rs("Bact") & Chr(13)
strBody = strBody & "Nitrate: " & rs("intNitrate")

'***creates and sends email
With objEmail
.To = rs("Email")
.CC = "sharonp@samswelldrilling.com"
.Subject = "Unsafe Water and/or High Nitrate Results "
.Body = strBody
.Display
end with 'THIS RIGHT HERE WAS MISSING


rs.MoveNext

Loop

End If

rs.Close
db.Close

'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit



End Sub

Randall Vollen
National City Bank Corp.
 
Upvote 0 Downvote
Indenting properly will help make the errors more obvious:
Code: 
Sub SendEmails()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim sql As String
  Dim strEmail As String
  Dim strBody As String
  Dim objOutlook As Outlook.Application
  Dim objEmail As Outlook.MailItem
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("qrySalespersonUpdate")
  
  sql = "SELECT tblSamples.[intDNRSample#], tblSamples.ynColifUnsafe, " & _
        "tblSamples.ynEcolSafe, tblSamples.ynEcolUnsafe, tblSamples.ynHighNitrates, " & _
        "tblSamples.txtWellAddress, tblSamples.datSampleDate, tblSamples.Salesperson, " & _
        "tblSamples.datReported, tblSamples.txtCounty, tblSamples.txtTownship FROM " & _
        "tblSamples LEFT JOIN tblSalespersonEmail ON tblSamples.Salesperson = " & _
        "tblSalespersonEmail.Salesperson WHERE (((tblSamples.ynColifUnsafe) = -1) And " & _
        "((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & _
        [Forms]![frmReportPopUp]![rdate] & "#)) Or (((tblSamples.ynHighNitrates) = -1) And " & _
        "((tblSamples.Salesperson) Is Not Null)) And ((tblSamples.datReported) = #" & _
        [Forms]![frmReportPopUp]![rdate] & "#))"
        
  Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
  
  If rs.BOF And rst.EOF Then
    Call MsgBox("No Results Need to be Emailed")
  Else
    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olmailitem)
    
    Do While Not rs.EOF        
      strEmail = txtEmail      
      strBody = "Unique Well # " & rs("intDNRSample#") & Chr(13)
      strBody = strBody & " " & Chr(13)
      strBody = strBody & "County: " & rs("txtCounty") & Chr(13)
      strBody = strBody & "Township: " & rs("txttownship") & Chr(13)
      strBody = strBody & "Address: " & rs("txtWellAddress") & Chr(13)
      strBody = strBody & " " & Chr(13)
      strBody = strBody & "Bacti: " & rs("Bact") & Chr(13)
      strBody = strBody & "Nitrate: " & rs("intNitrate")
        
      With objEmail
        .To = rs("Email")
        .CC = "sharonp@samswelldrilling.com"
        .Subject = "Unsafe Water and/or High Nitrate Results "
        .Body = strBody
        .Display
      End With        
      rs.MoveNext
    Loop
  End If
  
  rs.Close
  db.Close
  objOutlook.Quit
End Sub
*** I wouldn't post real email addresses in my code [pc2]

VBSlammer
redinvader3walking.gif

"You just have to know which screws to turn." - Professor Bob
 
Upvote 0 Downvote
You also have 2 different variable names for the recordset:
[tt]
If rs.BOF And rst.EOF Then[/tt]

VBSlammer
redinvader3walking.gif

"You just have to know which screws to turn." - Professor Bob
 
Upvote 0 Downvote
In this segment of code you need to add and END WITH. Two post prior you had an end if. Change it to End With, everything else looks fine.

> '***creates and sends email
>With objEmail
> .To = rs("Email")
> .CC = "sharonp@samswelldrilling.com"
> .Subject = "Unsafe Water and/or High Nitrate Results "
> .Body = strBody
> .Display
>
>>>>End With
>
> rs.MoveNext
>
>Loop
>
>End If
 
Upvote 0 Downvote
Move this line:
objOutlook.Quit
between Loop and End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Hi...

Looks like everybody else got back before I did.

I hope all is working for you now.

Please note VBSLAMMER's remarks about proper code formating and indenting.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
586
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
500
Andrzejek
Andrzejek
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
544
strongm
strongm
IrishJim
  • Locked
  • Question
How do I capture the &quot;digital signature&quot;, for display in my Form header?
Replies
0
Views
128
IrishJim
IrishJim
VicM
  • Locked
  • Question
Error when using Immediate window-recent happening 2
Replies
4
Views
213
VicM
VicM

Part and Inventory Search

Sponsor

Back
Top