Solved

Simple Loop

Posted on 2012-04-02
17
288 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
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
 
LVL 77

Accepted Solution

by:
peter57r earned 125 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now