Finding the proper Event for Workflow email (1 Viewer)

[PatAccess]

Hello Guys,

I didn't know how to word the subject of this thread for future reference but, I want to create a function for workflow emails that will be called rather the Access DB is opened or not.
Some background: I currently have a function that sends emails on date conditions but it is on the OnOpen event. If I want to the email to be sent rather the DB is opened or not, how do I do that?

Thank you for your help.

 

[GPGeorge]

You can use Windows Task Scheduler to open the accdb on a schedule. In the AutoExec macro for that accdb, run the email code.

However, I'm not 100% sure we're on the same page. Are you planning to call this from a different Access relational database application? Or on a strict timer schedule.

 

[PatAccess]

You can use Windows Task Scheduler to open the accdb on a schedule. In the AutoExec macro for that accdb, run the email code.

However, I'm not 100% sure we're on the same page. Are you planning to call this from a different Access relational database application? Or on a strict timer schedule.

On a specific schedule. The Code includes this piece

    'If today is the 15th of the month
    If todaysDate = dateFirstDayOfCurrMonth + 14 Then
        Call CorpLicSendEmail("Rpt_RenewNow")
    'If today is the first Monday of the Month
    ElseIf todaysDate = dateFirstDayOfCurrMonth Then
        Call CorpLicSendEmail("Rpt_RenewUpcoming")
    End If

So I want to the call to CorpLicSendEmail to happen rather the DB is opened or not

 

[Pat Hartman]

Don't use the AutoExec macro. That will interfere with normal operations of the database. Create a new macro that just does the email procedure and then closes the database. The macro calls your existing functions. Don't try to build the function into a macro. The macro has two steps - call your function. Close the database.

The Windows Scheduler sets up the schedule to open the application. You can use two rules and have separate macros for each. So if windows runs the job on the 15th, it opens the access database running macroA but if it is the first Monday, then it opens using macro2.

The only problem with the scheduler is the PC MUST be on or the task won't run. If you can get your IT people to let you do this on the server, you'll be better off but the problem with that is they will not want to install Office on the server. So, if you lose that battle, you need to set the tasks up on a machine that is left on 24x7.

I would also add an additional step. Either send you and at least one other person an email that says the job is complete OR have the functions log an entry in a database table to you can have a place to check to determine if the job is running as it should.

 

[PatAccess]

Don't use the AutoExec macro. That will interfere with normal operations of the database. Create a new macro that just does the email procedure and then closes the database. The macro calls your existing functions. Don't try to build the function into a macro. The macro has two steps - call your function. Close the database.

The Windows Scheduler sets up the schedule to open the application. You can use two rules and have separate macros for each. So if windows runs the job on the 15th, it opens the access database running macroA but if it is the first Monday, then it opens using macro2.

The only problem with the scheduler is the PC MUST be on or the task won't run. If you can get your IT people to let you do this on the server, you'll be better off but the problem with that is they will not want to install Office on the server. So, if you lose that battle, you need to set the tasks up on a machine that is left on 24x7.

I would also add an additional step. Either send you and at least one other person an email that says the job is complete OR have the functions log an entry in a database table to you can have a place to check to determine if the job is running as it should.

Ok I'll test that also. Question: I created a maco with the name of my function but it keeps telling me that it can't find that function? What could be the issue?

 

[theDBguy]

Ok I'll test that also. Question: I created a maco with the name of my function but it keeps telling me that it can't find that function? What could be the issue?

Can you show us your macro code?

 

[Pat Hartman]

Are you sure your code is a Function and not a Sub? Macros can only run Functions.

 

[GPGeorge]

Don't use the AutoExec macro. That will interfere with normal operations of the database. Create a new macro that just does the email procedure and then closes the database. The macro calls your existing functions. Don't try to build the function into a macro. The macro has two steps - call your function. Close the database.

The Windows Scheduler sets up the schedule to open the application. You can use two rules and have separate macros for each. So if windows runs the job on the 15th, it opens the access database running macroA but if it is the first Monday, then it opens using macro2.

The only problem with the scheduler is the PC MUST be on or the task won't run. If you can get your IT people to let you do this on the server, you'll be better off but the problem with that is they will not want to install Office on the server. So, if you lose that battle, you need to set the tasks up on a machine that is left on 24x7.

I would also add an additional step. Either send you and at least one other person an email that says the job is complete OR have the functions log an entry in a database table to you can have a place to check to determine if the job is running as it should.

Doh. AutoExec? What was I thinking?

 

[Pat Hartman]

tee many martonies I think