Hello, I need to send automatically reports to customers and I need to specify how often to send to each customer so I need multiple timers, one for each customer. How do I approach that? I can have 20-30 customers with different sending frequency. Only way I know if one timer in the form but this is way to few for me. Anyway to do this? Thank you
Multiple time periods in timer (1 Viewer)
- Thread starter Timax
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 04:50
- Joined
- May 7, 2009
- Messages
- 19,247
you only need 1 timer in a Hidden form.
you can open it through an Autoexec macro.
first create a table for your schedule of sending email (tblFrequency).
fields you need to add are:
customerID,
Day (text)(, Mon, Tue, Wed...),
Date (date/time),
Time (date/time, format to hh:mm),
Recurrent (Yes/No).
Emailed (Yes/No)
if you put a value on Day field, leave the Date field empty, vice versa.
when the hidden form is first opened it set the Emailed field to false, on its timer, it create a recordset from the above
table. move through each record to see if there is a "schedule" to email.
if there is it send the email and set the Emailed to True.
the code you will make will compare Day (if not blank) to today's date, Time to today's Time. Date to today's date.
the process repeats until all scheduled email are sent.
you can open it through an Autoexec macro.
first create a table for your schedule of sending email (tblFrequency).
fields you need to add are:
customerID,
Day (text)(, Mon, Tue, Wed...),
Date (date/time),
Time (date/time, format to hh:mm),
Recurrent (Yes/No).
Emailed (Yes/No)
if you put a value on Day field, leave the Date field empty, vice versa.
when the hidden form is first opened it set the Emailed field to false, on its timer, it create a recordset from the above
table. move through each record to see if there is a "schedule" to email.
if there is it send the email and set the Emailed to True.
the code you will make will compare Day (if not blank) to today's date, Time to today's Time. Date to today's date.
the process repeats until all scheduled email are sent.
- Local time
- Today, 15:50
- Joined
- Feb 28, 2001
- Messages
- 27,218
First, you are right. There is only one timer property per form.
How you do this depends on the frequency of reporting. Are we talking a frequency of seconds, minutes, hours, days, or months? Anything more often than once every hour or two makes me wonder about the nature of the business. If we are talking at least hours if not days or longer, then what I will suggest might work. Also, is this one report per customer or could a customer potentially get two different reports on two different intervals?
The method I would adopt is to have a table of desired customer transmissions with not a frequency but a period (the interval) expressed as whatever units are involved. Frequency is "1 report every hour" whereas period would be "1 hour between each report." Look at the DateAdd function to see the units you can choose to add, or there are ways to do this that would be based on the raw "day fractions."
Then, choose a "lowest supported frequency" (LSF). That is, NO customer ever gets reports more often than this LSF. If the LSF is an even number and all other frequencies of transmission are integer multiples of the LSF, then you set the timer to the LSF.
Your table would contain several fields:
Customer, report to send, report frequency, time last sent, Flag: (entry active).
If it is always one report per customer and it is always named the same for all customers, you omit the "report to send" field, which could have just been a report name anyway. In the simplest case, "Customer" is the prime key (PK) for your field; if it is different reports for different customers, you need the customer and the report name and they pair up to become a compound PK. Report frequency (actually, report PERIOD) is the amount of time you add to the "last transmission" to decide if it is time for the next transmission.
Your timer would contain a recordset LOOP that reads through your table of intended transmissions. For each active record in the set (see that flag field), you compute whether it is now time to send the indicated report and then either send it or don't, depending on the answer. If you send it, update the "last transmission" slot and move on. Depending on what else you wanted to do, you can add bells and whistles as you go, but what I described would do the basics.
Note also that for this to work, the timer has to be on a form that never closes. It can be minimized - but as long as these reports would be active, you can't close it. If that is not going to work for you, then you need to give us more details.
How you do this depends on the frequency of reporting. Are we talking a frequency of seconds, minutes, hours, days, or months? Anything more often than once every hour or two makes me wonder about the nature of the business. If we are talking at least hours if not days or longer, then what I will suggest might work. Also, is this one report per customer or could a customer potentially get two different reports on two different intervals?
The method I would adopt is to have a table of desired customer transmissions with not a frequency but a period (the interval) expressed as whatever units are involved. Frequency is "1 report every hour" whereas period would be "1 hour between each report." Look at the DateAdd function to see the units you can choose to add, or there are ways to do this that would be based on the raw "day fractions."
Then, choose a "lowest supported frequency" (LSF). That is, NO customer ever gets reports more often than this LSF. If the LSF is an even number and all other frequencies of transmission are integer multiples of the LSF, then you set the timer to the LSF.
Your table would contain several fields:
Customer, report to send, report frequency, time last sent, Flag: (entry active).
If it is always one report per customer and it is always named the same for all customers, you omit the "report to send" field, which could have just been a report name anyway. In the simplest case, "Customer" is the prime key (PK) for your field; if it is different reports for different customers, you need the customer and the report name and they pair up to become a compound PK. Report frequency (actually, report PERIOD) is the amount of time you add to the "last transmission" to decide if it is time for the next transmission.
Your timer would contain a recordset LOOP that reads through your table of intended transmissions. For each active record in the set (see that flag field), you compute whether it is now time to send the indicated report and then either send it or don't, depending on the answer. If you send it, update the "last transmission" slot and move on. Depending on what else you wanted to do, you can add bells and whistles as you go, but what I described would do the basics.
Note also that for this to work, the timer has to be on a form that never closes. It can be minimized - but as long as these reports would be active, you can't close it. If that is not going to work for you, then you need to give us more details.
Very interesting solution. Did not thought of that. To have table with all the time periods and run timer very frequently to check against current time but then I need one field with time when last run for this particular customer. It's the same report with different criteria in bounded query and frequency is various from ones a day to ones a week. It's an automatic report to customers with order balances and progress status. Thank you for your help. This is not an easy way to do it but I guess if nothing else is available I gonna have to take this route.
