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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Dale FyeOwner, Developing Solutions LLCCommented:
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
IrogSintaCommented:
Looks like fyed gave you the answer a few seconds ahead of me :)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

peter57rCommented:
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

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
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 FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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
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.