Looping through a query

[WannabePrgmr]

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:

Private Sub Form_Current()

Dim db As Database

Dim rst As DAO.Recordset

Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryOlder")

Set rst = qdf.OpenRecordset()

Do Until rst.EOF

 ‘Code to send email (works perfect by itself)

Loop

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
End Sub

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

[ADezii]

'Code Line #14 is the critical component that you are missing. You also Declared db as DAO.Database

Dim db As Database

but Instantiated dbs

Set dbs = CurrentDB

Please find the corrected ode below:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
  
Set db = CurrentDb
  
Set qdf = db.QueryDefs("qryOlder")
  
Set rst = qdf.OpenRecordset()

With rst
  Do Until .EOF
    'Send E-Mail for each Record
      .MoveNext         'Move to the next Record
  Loop
End With
  
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

[WannabePrgmr]

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:

Set objMessage = CreateObject("CDO.Message")
           objMessage.Subject = "Reminder for " & [Type] & ", Reference # " & [Reference #]
           objMessage.From = "steven.earley@bendix.com"
           objMessage.To = "steven.earley@bendix.com"
           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]


           objMessage.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

            'Name or IP of Remote SMTP Server
           objMessage.Configuration.Fields.Item _
           ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "ELYS0250.corp.knorr-bremse.com"

            'Server port (typically 25)
           objMessage.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
'
           objMessage.Configuration.Fields.Update

            '==End remote SMTP server configuration section==


           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!

[ADezii]

It appears as though you need to modify the Subject and Body for each Record, then Send it, as in:

With rst
  Do Until .EOF
    objMessage.Subject = "Reminder for " & ![Type] & ", Reference # " & ![Reference #]
    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]
    objMessage.Send
      .MoveNext         'Move to the next Record
  Loop
End With

P.S. - Also notice the Field Designators are prefaced with a ! since you are withing a With...End With Construct.

[NeoPa]

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

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

[ADezii]

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

[WannabePrgmr]

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!

[ADezii]

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

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
  
Set db = CurrentDb
  
Set qdf = db.QueryDefs("qryEmployees")
  
Set rst = qdf.OpenRecordset()

With rst
  Do Until .EOF
    Debug.Print ![LastName] & ", " & ![FirstName]
    Debug.Print rst![LastName] & ", " & rst![FirstName]
    Debug.Print [LastName] & ", " & [FirstName]
      .MoveNext
  Loop
End With
  
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

[WannabePrgmr]

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.

.....![Type] & vbCrLf & "Product: " &_
 [Product Type] & vbCrLf & "Status: " & ![Status] &_
 vbCrLf & "Bendix Number: " & [Bendix Number] &_
 vbCrLf & "Customer Number: " & [Customer Number]

I'm happy it works!!!!!

[ADezii]

@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).

[WannabePrgmr]

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

[WannabePrgmr]

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

[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 (Hence the appearance of referring to the first record).

[ADezii]

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

[NeoPa]

: - )

(Frinny Smile)