Solved

Simple Loop

Posted on 2012-04-02
17
312 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access report groups with sums 5 26
Create a EXCEL FIle from Access Procedure 1 13
How to use DLookup with IsNull Function 4 25
update all email addresses SQL 1 23
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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

774 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