Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Automatic E-mail update from Access 2000 2
- Thread starter ambra19
- Start date
-
2
- #2
Use an expression to calculate the difference between your important date and the computer system date. Then use a Select Case statement to take the appropriate action if the result matches one of your critical periods. If it matches, create the appropriate email message.
To learn how to get Access to send an email automatically, check out FAQ181-493
HTH
Lightning
To learn how to get Access to send an email automatically, check out FAQ181-493
HTH
Lightning
- Thread starter
- #3
Yes, one piece of code will do this.
What I would do is put a Select Case statement into the On Open event of the startup form or Switchboard form of my application. Something like:
Select Case Date
Case is = #[Your First Important Date]#
....Code to Send the appropriate email
Case is = #[Your second Important Date]#
....Code to Send a second email
...
...
...
Case Else
....Do nothing because this is not an important date
End Select
By putting it in the On Open event of a main form for the database, this code will run every time you open the database. If you open the db several times a day and don't want to send an email each time, you would need to set a flag somwhere to indicate that the email had already been sent today and not to send it again.
HTH
Lightning
What I would do is put a Select Case statement into the On Open event of the startup form or Switchboard form of my application. Something like:
Select Case Date
Case is = #[Your First Important Date]#
....Code to Send the appropriate email
Case is = #[Your second Important Date]#
....Code to Send a second email
...
...
...
Case Else
....Do nothing because this is not an important date
End Select
By putting it in the On Open event of a main form for the database, this code will run every time you open the database. If you open the db several times a day and don't want to send an email each time, you would need to set a flag somwhere to indicate that the email had already been sent today and not to send it again.
HTH
Lightning
- Thread starter
- #5
Thanks again for your prompt response Lightning.
I am still a bit unclear, as I am doing my using Visual Basic, not the VBA in Access. Using your method, I take it that I would create a form in VBA in Access for my database, and that each time it is opened, the e-mail will be sent.
ambra19
I am still a bit unclear, as I am doing my using Visual Basic, not the VBA in Access. Using your method, I take it that I would create a form in VBA in Access for my database, and that each time it is opened, the e-mail will be sent.
ambra19
Yes, that is why I suggest using the Startup form or the Switchboard form for the application.
Typically the Startup form is the Switchboard, and this form remains open the whole time the application is running. Therefore the code would automatically run when the application is first opened, and then just sits there inactive until the application is opened again.
HTH
Lightning
Typically the Startup form is the Switchboard, and this form remains open the whole time the application is running. Therefore the code would automatically run when the application is first opened, and then just sits there inactive until the application is opened again.
HTH
Lightning
If you are using VB, then the equivalent is to put the code in the Sub Main() procedure....
If you are using VB and Access is just the backend then,
how about using the windows scheduler to start microsoft access, say at 2am every morning, using the command-line ( /cmd ) switch,
see:
e.g. mydatabase.mdb \cmd "CheckDates"
You could create a macro called 'AutoExec', containing the RunCode action e.g. with the function name set to 'StartUp()'. The AutoExec macro runs when the database starts up.
Then create a public function called 'StartUp' and write code to capture the command-line (see website link). The commandline will tell you whether or not you are wanting to check for up coming appointments. Tehn close the app again
I have never tried with but look interesting,
Cheers,
Dan
P.S. Need to check first the Scheduler can startup Access.
If you are using VB and Access is just the backend then,
how about using the windows scheduler to start microsoft access, say at 2am every morning, using the command-line ( /cmd ) switch,
see:
e.g. mydatabase.mdb \cmd "CheckDates"
You could create a macro called 'AutoExec', containing the RunCode action e.g. with the function name set to 'StartUp()'. The AutoExec macro runs when the database starts up.
Then create a public function called 'StartUp' and write code to capture the command-line (see website link). The commandline will tell you whether or not you are wanting to check for up coming appointments. Tehn close the app again
I have never tried with but look interesting,
Cheers,
Dan
P.S. Need to check first the Scheduler can startup Access.
Lightning,
I've been trying to something similar. I have a macro that sends out a report. I run it the first of each month.
Your Cas Date may work in getting this automated.
Would I have this on the OnOpen event of the start-up form, then have the Windows Task Scheduler open Access the first day of each month? :
Select Case Date
Case is = #[firstdayofmonth]#
DoCmd.RunMacro "mrcSendReport"
Case Else
....Do nothing because this is not an important date
End Select
I've been trying to something similar. I have a macro that sends out a report. I run it the first of each month.
Your Cas Date may work in getting this automated.
Would I have this on the OnOpen event of the start-up form, then have the Windows Task Scheduler open Access the first day of each month? :
Select Case Date
Case is = #[firstdayofmonth]#
DoCmd.RunMacro "mrcSendReport"
Case Else
....Do nothing because this is not an important date
End Select
Yes, that would work.
Again, just be aware that you would need to set a flag somewhere to ensure that duplicate emails are not sent if you open the database more than once on the scheduled day.
One way to do this would be by having a table that records the date after your email is sent, and check for today's date in that table before executing the send email code. This method would also give you an audit trail showing when the emails were sent.
HTH
Lightning
Again, just be aware that you would need to set a flag somewhere to ensure that duplicate emails are not sent if you open the database more than once on the scheduled day.
One way to do this would be by having a table that records the date after your email is sent, and check for today's date in that table before executing the send email code. This method would also give you an audit trail showing when the emails were sent.
HTH
Lightning
- Status
Similar threads
- Locked
- Question