Simple Loop

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!
LVL 2
CompTech810Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
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
 
Dale FyeCommented:
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
 
IrogSintaCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
IrogSintaCommented:
Looks like fyed gave you the answer a few seconds ahead of me :)
0
 
CompTech810Author Commented:
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
 
Dale FyeCommented:
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
 
IrogSintaCommented:
I am not familiar with dbSeeChanges so I defer to fyed's expertise in this area.
0
 
CompTech810Author Commented:
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
 
Dale FyeCommented:
Set rst = CurrentDb.OpenRecordset("qrycontactstoemail", dbSeeChanges + dbFailOnError)
0
 
CompTech810Author Commented:
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
 
Dale FyeCommented:
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
 
CompTech810Author Commented:
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
 
Dale FyeCommented:
can you post the entire set of code, starting with the dimension statements?
0
 
CompTech810Author Commented:
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
 
CompTech810Author Commented:
Fryed:  Any suggestions to the error I am getting.  I tried another set of code and I'm getting the same error......
0
 
CompTech810Author Commented:
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
 
CompTech810Author Commented:
I would have put excellent except that there where issues.

Set rs = db.OpenRecordset("qryContactsToEmail", dbOpenDynaset, dbSeeChanges)  Worked.
0
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.

All Courses

From novice to tech pro — start learning today.