?
Solved

Simple Loop

Posted on 2012-04-02
17
Medium Priority
?
338 Views
Last Modified: 2012-08-13
I am fairly new to VBA and have programmed in other languages.

Can someone get me started on creating a loop based on a query.

I want to loop through a query record by record and after each record I have an email routine.

Thanks!
0
Comment
Question by:CompTech810
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37796290
It might look something like:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = currentdb
set rs = db.openrecordset("SELECT Field1, Field2, Field3 FROM yourTable",,dbfailonerror)

While not rs.eof

    'do something here
    'take a look at the Docmd.SendObject method for sending email
    'although there are some macro security issues they can be overcome using vbMAPI or some other method

    rs.MoveNext
Wend

rs.close
set rs = nothing
set db = nothing
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37796291
Try this:

Private Sub NameOfYourRoutine()
     Dim rst as Recordset
     
     Set rst = currentDb.OpenRecordset ("NameOfYourQuery")
     Do While Not rst.EOF
          'Put your email routine here
          '...
          '...
          rst.MoveNext
     Loop
     rst.Close
     Set rst = Nothing
End Sub
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37796300
Looks like fyed gave you the answer a few seconds ahead of me :)
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 77

Accepted Solution

by:
peter57r earned 375 total points
ID: 37796313
Dim strsql as string
Dim db as database
Dim rs as dao.recordset

strsql = "Select * from table"    ' your query here
set db= currentdb
set rs = db.openrecordset(strsql)
if not rs.recordcount = 0 then
rs.movefirst
do until rs.eof

' your email code here

Rs.movenext
Loop
else
' do nothing
end if
rs.close
set rs= nothing
set db = nothing
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37796990
IrogSinta - I decided to use your code because I understand it the best,  although I get an error "You must use dbseechanges".  I googled it but not sure where to put it in your code.  I forgot to mention that I am connected to a SQL database through ODBC.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37797007
Irog's syntax is identical to what I posted, so I don't get that.

your syntax problem is in the recordset open line.  Try:

     Set rst = currentDb.OpenRecordset ("NameOfYourQuery", dbSeeChanges, dbFailOnError)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37797029
I am not familiar with dbSeeChanges so I defer to fyed's expertise in this area.
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37797075
Fryed, I wasnt sure how to insert the query in your code.  Also, I'm not sure which are reserved words and where I should be putting my information.  I'm learning VBA the painfull way, and no time to take a class......

Anywho, now I am getting an error "Invalid Argument"

This is the code:

Set rst = CurrentDb.OpenRecordset("qrycontactstoemail", dbSeeChanges, dbFailOnError)
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37797106
Set rst = CurrentDb.OpenRecordset("qrycontactstoemail", dbSeeChanges + dbFailOnError)
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37797153
Hmmmm, still invaild argument...

Dim rst As Recordset
     
     Set rst = CurrentDb.OpenRecordset("qrycontactstoemail", dbSeeChanges + dbFailOnError)
     Do While Not rst.EOF

I included the statements directly ahead of it and after it just incase the error that was highlighted  is not the real error.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37797219
What version of Access are you running?

Are you sure you have the query name spelled correctly?

Do you have a reference set to the Microsoft DAO 3.6 Object Library (you won't need this if you are running Access 2007)

And I was right the first time, the options should be separated by commas, not a +, so go back to:

Set rst = CurrentDb.OpenRecordset("qrycontactstoemail", dbSeeChanges, dbFailOnError)
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37797374
I'm running Access 2007.  I went ahead and selected Microsoft DAO 3.6 and reran the code and I'm still getting the error.  I even did a rename on the query to copy the name and pasted it into the code.  Also, I closed Access and reopened it.   Still getting the error.....  wish this was RPG...
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37797448
can you post the entire set of code, starting with the dimension statements?
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37797603
For now I'm just using the code and when it is working I'll insert my email command.  I did have my email command in the code but still got the error.  Thanks for your help!!

Dim rst As Recordset
     
     Set rst = CurrentDb.OpenRecordset("qryContactsToEmail", dbSeeChanges, dbFailOnError)
     Do While Not rst.EOF
           
     rst.MoveNext
     Loop
     rst.Close
     Set rst = Nothing
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37800649
Fryed:  Any suggestions to the error I am getting.  I tried another set of code and I'm getting the same error......
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37801251
Fryed:  I saw your comment to a MVP about Microsoft's help.  I agree with you that Microsoft lacks in documentation.

Anywho, as you know the following code works now.  I'm putting in on here so if other people have the same issue.  Thanks for all your help!!!

Set rs = db.OpenRecordset("qryContactsToEmail", dbOpenDynaset, dbSeeChanges)
0
 
LVL 2

Author Closing Comment

by:CompTech810
ID: 37801263
I would have put excellent except that there where issues.

Set rs = db.OpenRecordset("qryContactsToEmail", dbOpenDynaset, dbSeeChanges)  Worked.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question