469,268 Members | 962 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

Looping through a query

I have a form that is brought up at startup (frmSteve) and I would like to run a query (qryOlder) in the "On Current" for that form. The query captures any record that is over 14 days old. What I need to do is send an automatic email to myself for each record that came up in the query.
The query works fine. The code I have to auto-email works fine. I am just completely clueless as to how to set up the code to loop through the query and run the auto-email code for each record.

After searching online, I found some code and tried plugging it in with mine just to see what happened. Here's what I came up with:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. Dim db As Database
  4.  
  5. Dim rst As DAO.Recordset
  6.  
  7. Dim qdf As DAO.QueryDef
  8.  
  9. Set dbs = CurrentDb
  10.  
  11. Set qdf = dbs.QueryDefs("qryOlder")
  12.  
  13. Set rst = qdf.OpenRecordset()
  14.  
  15. Do Until rst.EOF
  16.  
  17.  ‘Code to send email (works perfect by itself)
  18.  
  19. Loop
  20.  
  21. rst.Close
  22. Set rst = Nothing
  23. Set qdf = Nothing
  24. Set dbs = Nothing
  25. End Sub
  26.  
This emails the first record in the query results HUNDREDS of times and never makes it to the next record.

Any help is very much appreciated!!!
Jul 29 '11 #1
14 18823
ADezii
8,800 Expert 8TB
'Code Line #14 is the critical component that you are missing. You also Declared db as DAO.Database
Expand|Select|Wrap|Line Numbers
  1. Dim db As Database 
but Instantiated dbs
Expand|Select|Wrap|Line Numbers
  1. Set dbs = CurrentDB
Please find the corrected ode below:
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim qdf As DAO.QueryDef
  4.  
  5. Set db = CurrentDb
  6.  
  7. Set qdf = db.QueryDefs("qryOlder")
  8.  
  9. Set rst = qdf.OpenRecordset()
  10.  
  11. With rst
  12.   Do Until .EOF
  13.     'Send E-Mail for each Record
  14.       .MoveNext         'Move to the next Record
  15.   Loop
  16. End With
  17.  
  18. rst.Close
  19. Set rst = Nothing
  20. Set qdf = Nothing
  21. Set db = Nothing
Jul 29 '11 #2
ADezii, thank you for such a quick reply! Thanks for the corrections! I don't know how I missed .MoveNext (it was in there at one point).
Now it only sends the same record (the first one) 3 times. So I get three identical emails with the same information in them. 3 happens to be the amount of records returned in the query (and I tested it by deleting one so there were only two in the query. It then sent two of the first record).

Could it have anything to do with the code I'm using to send the email? In case it does, I'll paste it. This is everything in "'Send E-Mail for each Record" above:

Expand|Select|Wrap|Line Numbers
  1. Set objMessage = CreateObject("CDO.Message")
  2.            objMessage.Subject = "Reminder for " & [Type] & ", Reference # " & [Reference #]
  3.            objMessage.From = "steven.earley@bendix.com"
  4.            objMessage.To = "steven.earley@bendix.com"
  5.            objMessage.TextBody = "Description: " & [Descripion] & vbCrLf & "PC #: " & [PC] & vbCrLf & "PR #: " & [PR] & vbCrLf & "Type: " & [Type] & vbCrLf & "Product: " & [Product Type] & vbCrLf & "Status: " & [Status] & vbCrLf & "Bendix Number: " & [Bendix Number] & vbCrLf & "Customer Number: " & [Customer Number] & vbCrLf & "Start Date: " & [StartDate] & vbCrLf & "End Date: " & [EndDate] & vbCrLf & "Engineer: " & [Engineer] & vbCrLf & "NOTES: " & vbCrLf & vbCrLf & [Notes]
  6.  
  7.  
  8.            objMessage.Configuration.Fields.Item _
  9.             ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  10.  
  11.             'Name or IP of Remote SMTP Server
  12.            objMessage.Configuration.Fields.Item _
  13.            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "ELYS0250.corp.knorr-bremse.com"
  14.  
  15.             'Server port (typically 25)
  16.            objMessage.Configuration.Fields.Item _
  17.             ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  18. '
  19.            objMessage.Configuration.Fields.Update
  20.  
  21.             '==End remote SMTP server configuration section==
  22.  
  23.  
  24.            objMessage.Send

Also, I have this in the On Current event of the form but I go in and out of this form all day long, which meens it's constantly shooting the same emails everytime I go in. Is there a way to force it to occur only when opening the db?

Thanks again for your help!
Jul 29 '11 #3
ADezii
8,800 Expert 8TB
It appears as though you need to modify the Subject and Body for each Record, then Send it, as in:
Expand|Select|Wrap|Line Numbers
  1. With rst
  2.   Do Until .EOF
  3.     objMessage.Subject = "Reminder for " & ![Type] & ", Reference # " & ![Reference #]
  4.     objMessage.TextBody = "Description: " & ![Descripion] & vbCrLf & "PC #: " & ![PC] & vbCrLf & "PR #: " & _
  5.                            ![PR] & vbCrLf & "Type: " & ![Type] & vbCrLf & "Product: " & ![Product Type] & vbCrLf & _
  6.                            "Status: " & ![Status] & vbCrLf & "Bendix Number: " & ![Bendix Number] & vbCrLf & _
  7.                            "Customer Number: " & ![Customer Number] & vbCrLf & "Start Date: " & ![StartDate] & _
  8.                            vbCrLf & "End Date: " & ![EndDate] & vbCrLf & "Engineer: " & ![Engineer] & vbCrLf & _
  9.                            "NOTES: " & vbCrLf & vbCrLf & ![Notes]
  10.     objMessage.Send
  11.       .MoveNext         'Move to the next Record
  12.   Loop
  13. End With
P.S. - Also notice the Field Designators are prefaced with a ! since you are withing a With...End With Construct.
Jul 29 '11 #4
NeoPa
32,171 Expert Mod 16PB
@ADezii
I'm no great expert at using email in code (so I may be wrong here) but it looks like you've missed out the creation line of each message :
Expand|Select|Wrap|Line Numbers
  1. Set objMessage = CreateObject("CDO.Message")
Your TextBody code is much easier to read though :-) (A point worth noting for your work WannabePrgmr, but particularly for posting questions.)
Jul 30 '11 #5
ADezii
8,800 Expert 8TB
@NeoPa:
You are correct NeoPa in that each E-Mail Instance, along with its Parameters, would have to be uniquely defined. What I was demonstrating was more or less Pseudo Code in order to keep the Code condensed and to illustrate the concept of each E-Mail needing to be individually tailored, although I woefully failed to indicate that. Thanks for putting me on the right track.
Jul 30 '11 #6
NeoPa, Thanks for your response! I believe ADezii was simply pointing out what he changed. The "Set objMessage = ..." code was in the original, messy (sorry about that) code that I posted.

Question for either of you.....I kept the ! in front as you did but for a few of them it kept coming back with an "object not found" error. I had to simply delete the ! and it's working perfect! So most of them still have it before but a few wont work with them???

I previously asked another question about running this code at startup instead of in the On Current of a form that I go in and out of all day. I simply pulled it out into a command button on the form so I could just send emails when I wanted.

Thanks for all your help! Everything is functioning better than expected!
Jul 30 '11 #7
ADezii
8,800 Expert 8TB
@WannebePrgmr
Question for either of you.....I kept the ! in front as you did but for a few of them it kept coming back with an "object not found" error. I had to simply delete the ! and it's working perfect! So most of them still have it before but a few wont work with them???
Sorry, but this does not make any sense to me. In the Sample Code below, Line #13 or 14 will work correctly, but Line #15 will not.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim qdf As DAO.QueryDef
  4.  
  5. Set db = CurrentDb
  6.  
  7. Set qdf = db.QueryDefs("qryEmployees")
  8.  
  9. Set rst = qdf.OpenRecordset()
  10.  
  11. With rst
  12.   Do Until .EOF
  13.     Debug.Print ![LastName] & ", " & ![FirstName]
  14.     Debug.Print rst![LastName] & ", " & rst![FirstName]
  15.     Debug.Print [LastName] & ", " & [FirstName]
  16.       .MoveNext
  17.   Loop
  18. End With
  19.  
  20. rst.Close
  21. Set rst = Nothing
  22. Set qdf = Nothing
  23. Set db = Nothing
Jul 30 '11 #8
Interesting! Here is the code that works. As soon as I put the ! back in front of those that do not have it, "object not founs" comes back.
Expand|Select|Wrap|Line Numbers
  1. .....![Type] & vbCrLf & "Product: " &_
  2.  [Product Type] & vbCrLf & "Status: " & ![Status] &_
  3.  vbCrLf & "Bendix Number: " & [Bendix Number] &_
  4.  vbCrLf & "Customer Number: " & [Customer Number]
I'm happy it works!!!!!
Jul 30 '11 #9
ADezii
8,800 Expert 8TB
@WannabePrgmr:
Happy it works, but I'm still puzzled. Just providing the [Field Name] without any qualifier (! or Recordset Object) only gave the same Values for that Field Name as contained within the 1st Record and not the others. Oh well, toss this one in the Weirdo Pile! (LOL).
Jul 30 '11 #10
ADezii, I guess I wasn't looking close enough to the emails!!! You're right, when I take away the ! it repeats whatever data is in the first record. But if I put the ! there, the following error comes up "Run-time error '3265': Item not found in this collection".
It only happens on [Customer Number] and [Bendix Number].
I'm going to google the error and see what I find.....
Jul 30 '11 #11
I figured it out! Classic idiot move on my part! It was actually only [Bendix Number] that was causing the issue and it was because it was not listed in qryOlder.
Thanks for your help and explanation again! I learn so much from you guys!!!!
Jul 30 '11 #12
NeoPa
32,171 Expert Mod 16PB
I would guess the chances are that a reference to the field without the . or bang (. or !) will appear to work, but would actually be referencing a similarly named control on the form (Hence the appearance of referring to the first record).
Jul 30 '11 #13
ADezii
8,800 Expert 8TB
@NeoPa:
I would guess the chances are that a reference to the field without the . or bang (. or !) will appear to work, but would actually be referencing a similarly named control on the form
Nice catch NeoPa, that is exactly what is happening. Right IFO my eyes, and never saw it! (LOL)
Jul 30 '11 #14
NeoPa
32,171 Expert Mod 16PB
: - )

(Frinny Smile)
Jul 31 '11 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by kaptain kernel | last post: by
3 posts views Thread by Karl Irvin | last post: by
2 posts views Thread by Delores | last post: by
1 post views Thread by Intrepid_Yellow | last post: by
3 posts views Thread by DougieC | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.