473,883 Members | 1,750 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Looping through a query

78 New Member
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 21605
ADezii
8,834 Recognized Expert Expert
'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
WannabePrgmr
78 New Member
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,834 Recognized Expert Expert
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,584 Recognized Expert Moderator MVP
@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,834 Recognized Expert Expert
@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
WannabePrgmr
78 New Member
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,834 Recognized Expert Expert
@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
WannabePrgmr
78 New Member
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,834 Recognized Expert Expert
@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

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

Similar topics

8
4066
by: kaptain kernel | last post by:
i've got a while loop thats iterating through a text file and pumping the contents into a database. the file is quite large (over 150mb). the looping causes my CPU load to race up to 100 per cent. Even if i remove the mysql insert query and just loop through the file , it still hits 100 per cent CPU. This has the knock on effect of slowing my script down so that mysql inserts are occuring every 1/2 second or so.
4
3013
by: Giulio | last post by:
Hello, I have a continuous form with a combo box inside. From the combo box I can pick some values which, by the "after-update" event, change some other combo box values determined by a query. (i.e. I have some tree names and depending on what tree I pick on the other combo box I can pick its fruits and other properties) Now, mine is a continous form, and whenever I pick a value from the first combo box the second one correctly shows...
8
2101
by: RC | last post by:
I have a table that lists many box numbers. Each box number has a Pallet Number (indicating which pallet the box is in). When the Pallets are loaded into a shipping Container I need to update the table to indicate which pallets and boxes are in the container. In my code below, in the table named "Products", I find the first Pallet Number that matches the Pallet Number typed in the box on my form (PalletNumberContainerFormComboBox)....
3
3605
by: Karl Irvin | last post by:
Can you loop through Query Defs and get the name of the tables/queries used in each query?
2
1589
by: Delores | last post by:
I have been running make-table queries to create 10 tables (one for each unit) that are exported to Excel spreadsheets. Because of my limited knowledge with code, I've created 10 separate queries to do the job and then run a macro to do the exporting. I know there must be a way to generate the 10 tables with only one query and I think it probably involves looping(?) through the query. I've read the previous postings and anytime I try to...
5
1934
by: talktozee | last post by:
Hello, everyone! Here's are the basics: 1. The query looks at all positions that are active and haven't been filled. 2. It then has to look at every single position and determine three things: Does the person running the query (via an ASP page) have certain rights? a. Recruit rights b. Enlist rights c. Take rights
1
2503
by: Intrepid_Yellow | last post by:
Hi, I have the following code that runs my report generator. The user selects a table from a combo box, then whatever fields they want from a list box. (This part all works and the report runs fine). There is then a combo box they can select a field from (eg CompanyID etc) and then the list box below that contains the values (eg Microsoft, Novell etc). These are all multi-select list boxes. Now I can get the code to work if the user...
3
2264
by: DougieC | last post by:
I am trying to insert records into a mastertable. The first field is auto-incrementing and therefore I am unable to insert all records at the same time. I have create the following code to insert one record in at a time. insert into `mastertable3` select ' ', 0301_2500.ssn, 0301_2500.vsssn, 0301_2500.name_ind, 0301_2500.pay_gr_aa from time_period, Mastertable3 right join 0301_2500 on mastertable3.ssn = 0301_2500.SSN where ...
1
1596
by: kieth | last post by:
hi, i have managed to put together a program that reads a file and if it finds the word "error" it brings back that line of details... unfortunately, it stops on that line... does anybody know how i get it to carry on through an entire log? this is my code so far
2
5380
by: bushnellweb | last post by:
I am looping through a query and I am wondering if there is a way to access data on the next row of the query I am looping through. Heres some code: <cfquery datasource="#ds#" name="myQRY"> SELECT pic_src FROM pics WHERE pic_id = 2 </cfquery> <cfoutput query="myQRY">
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9797
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7977
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6005
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4622
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.