Question How to Process all Records in a Table? (1 Viewer)

[NoSmoke]

I wish to set up a simple table that will contain:

. Person's name
. Person's email address
. Date at which a form email should be sent to that person

I want a program to check each record in the table periodically and send an email to those persons as the send date for that person requires. I think I can handle the date logic and email sending etc. but am stuck on providing code to automatically work through the table (from 1st to last record) so as to process all the records.

Any help with this would be much appreciated

 

[llkhoutx]

Run a query that has a function which does the email when appropriate.

 

[NoSmoke]

Do you mean the query designer has the ability to add a VBA programmed function? Or is SQL or something required?

 

[llkhoutx]

Both can be used.

 

[HiTechCoach]

I wish to set up a simple table that will contain:

. Person's name
. Person's email address
. Date at which a form email should be sent to that person

I want a program to check each record in the table periodically and send an email to those persons as the send date for that person requires. I think I can handle the date logic and email sending etc. but am stuck on providing code to automatically work through the table (from 1st to last record) so as to process all the records.

Any help with this would be much appreciated

If this is just a single table (a spreadsheet in Access) then you really should correct your table design before you do anything else.

In my experience doing many application to handle your needs it always has required multiple tables.

Date at which a form email should be sent to that person

his information is lie a transaction. Something that is repeated. In that case this needs to be in a child (related) table with one record per email to send.

This change will give you the all the history. Power information.

 

[NoSmoke]

Thanks Boyd but I really don't need anything further such as send history. Now that you mention it, it would be just a spreadsheet in Access so, I might be better off just doing it in Excel VBA. If however, I can figure out how to get Excel to send an email.

I already have an Access program that sends email but only for the current record. It uses the DoCmd.SendObject acSendForm statement. I guess I would need something similar for Excel, if it exists.

Would still be very interested however in knowing how to do this task (for each record in the table) in Access VBA if anyone can help with that.

 

[HiTechCoach]

I have done this in both Exel and Access. It is a lot more work and VBA coding to do it in Excel.

Access is a database, unlike spreadsheets, is by design all about processing lots of records.

What Access has that makes it so power is queries. You can create a query (SQL statement) that selects all the desired records.

To Loop through records and process each one individually you would use a recordset.

I have created an example the batch prints invoice. It saves it one to a PDF. It would be very simple to add a line to also email it.

See: Batch Printing and Save As PDF (Click Here)

This is an Acess 2007 format database.

This is an example based on the NorthWind example using the Invoice report.

I created a form to select the Invoices you want to print. There is a "Select All", "Unselect All", "Print Invoices" , and "Save Invoices as separate PDFs" buttons.

To make it work I had to added one field to the "Orders" table. I added this field to the query form the Invoice report.

Hope you find this useful.


*** The download uses the built-in ability to create PDFs available in Access 2007 and later.

Note: with Access 2007 you will need to be sure that you have downloaded and installed the add-in for creatng PDFs..

 

[NoSmoke]

Thank you again Boyd - with that help I think I can go from here

 

[HiTechCoach]

Thank you again Boyd - with that help I think I can go from here

You're welcome. Glad we could assist.

May you have great success with your project.