Solved Task Scheduler Automation

[Malcolm17]

Hello,

I am trying to build an automated task scheduler for my system where every day it will run a report at the selected time each day, or run some process.

Currently I have a few small access databases running in windows task scheduler that open, run whatever then close - works well, however I fancy building one on my own. I have attached a copy for you to see.

My issues is that I have formatted a field that shows the hour and one that shows the minute, but it doesn't seem to be running the task at the time. I have a feeling the issue is just because I have shown the time formatted, this is still not right. The forms updates every 15 seconds using the On Timer function.

Please can you have a look and let me know where I am going wrong - should I be doing something different or would this even work?

Thank you,

Malcolm

 

[Gasman]

You have nested Ifs?

Private Sub Form_Timer()
    Me.Refresh

    If [cboEarlyDutyManagerActive] = "Yes" And [cboEarlyDutyManagerStatus] = "Waiting" And [cboEarlyDutyManagerHour] = [txtNowHour] And [cboEarlyDutyManagerMinute] = txtNowMinute Then
        MsgBox "Early Duty Manager Run"

        If [cboLateDutyManagerActive] = "Yes" And [cboLateDutyManagerStatus] = "Waiting" And [cboLateDutyManagerHour] = [txtNowHour] And [cboLateDutyManagerMinute] = txtNowMinute Then
            MsgBox "Late Duty Manager Run"

            If [cboEarlyReceptionActive] = "Yes" And [cboEarlyReceptionStatus] = "Waiting" And [cboEarlyReceptionHour] = [txtNowHour] And [cboEarlyReceptionMinute] = txtNowMinute Then
                MsgBox "Early Reception Run"

                If [cboLateReceptionActive] = "Yes" And [cboLateReceptionStatus] = "Waiting" And [cboLateReceptionHour] = [txtNowHour] And [cboLateReceptionMinute] = txtNowMinute Then
                    MsgBox "Late Reception Run"

                    If [cboNightsActive] = "Yes" And [cboNightsStatus] = "Waiting" And [cboNightsHour] = [txtNowHour] And [cboNightsMinute] = txtNowMinute Then
                        MsgBox "Nights Run"

                    End If
                End If
            End If
        End If
    End If
End Sub

You are also comparing numbers with strings?

One way would be

    If [cboEarlyDutyManagerActive] = "Yes" And [cboEarlyDutyManagerStatus] = "Waiting" And [cboEarlyDutyManagerHour] = Val([txtNowHour]) And [cboEarlyDutyManagerMinute] = Val(txtNowMinute) Then

That works, however will run multiple times in that minute according to all the MSGBOX messages I got.

 

[Malcolm17]

Hi Gasman, nested Ifs wasn't the plan, I moved them around when I was trying to make it work - do you know where I am going wrong with the timing issues please?

Pat, thank you - do you have a link?

Thank you

 

[Gasman]

I have updated my post.
Edit: Thinking about it, just show Hour() and Minute() of Now(). Format creates a string output.

 

[Malcolm17]

Fabulous, thank you for that

I will hopefully sort that problem my getting it to change the status before it does anything else and then they will all be updated again at midnight for the following days tasks to run. Thanks again.

 

[tvanstiphout]

Hello,

I am trying to build an automated task scheduler for my system where every day it will run a report at the selected time each day, or run some process.

Currently I have a few small access databases running in windows task scheduler that open, run whatever then close - works well, however I fancy building one on my own. I have attached a copy for you to see.

My issues is that I have formatted a field that shows the hour and one that shows the minute, but it doesn't seem to be running the task at the time. I have a feeling the issue is just because I have shown the time formatted, this is still not right. The forms updates every 15 seconds using the On Timer function.

Please can you have a look and let me know where I am going wrong - should I be doing something different or would this even work?

Thank you,

Malcolm

I checked your code and put a breakpoint at the bottom of the Timer event. Then in the Immediate window:
?[cboEarlyDutyManagerHour], [txtNowHour], [cboEarlyDutyManagerHour] = [txtNowHour]
9 09 False
The number 9 is compared to the string "09" and the result is: not equal.

My first attempt to fix was to change the expression of txtNowHour to:
=Format(Now(),"h")
?[cboEarlyDutyManagerHour], [txtNowHour], [cboEarlyDutyManagerHour] = [txtNowHour]
9 9 False
The number 9 is compared to the string "9" and the result is: not equal.
That surprised me a bit - VBA does a lot of type coercion, and for example (in the Immediate window):
?9 = "9"
True

I shrugged and casted the string to number with CInt:
?[cboEarlyDutyManagerHour], [txtNowHour], [cboEarlyDutyManagerHour] = CInt([txtNowHour])
9 9 True

So if you do this with your txtNowHour and txtNowMinute in the timer event, the code will work as you intended.

 

[Malcolm17]

Hi All,

Much appreciated, thank you. I have gone with Gasman's solution for now - I appreciate the help from everyone tho.

Thank you, Malcolm

 

[Gasman]

Hi All,

Much appreciated, thank you. I have gone with Gasman's solution for now - I appreciate the help from everyone tho.

Thank you, Malcolm

Which one?, as I offered 2

 

[Malcolm17]

I went with this for now

If [cboEarlyDutyManagerActive] = "Yes" And [cboEarlyDutyManagerStatus] = "Waiting" And [cboEarlyDutyManagerHour] = Val([txtNowHour]) And [cboEarlyDutyManagerMinute] = Val(txtNowMinute) Then

Then the next line I change the Status to Running
I run the Process for whatever it should do
Then it changes the Status to Complete

Thank you

 

[Gasman]

Personally I would have gone with the second option, as you convert something to a strinģ and then have to convert to a number.
I would just leave it as number.

 

[The_Doc_Man]

Just a word of warning. Running automated events to a specific time presumes that the DB doing this will be running at the specific time, or is capable of deciding that something is overdue. There can also be redundancies and overlaps when you have code running something while playing "catch up." Also, if this is a single, stand-alone DB, then fine. But if this is a DB that can be shared, you have to consider whether anyone's instance of that DB can run the things you want run - and whether a potential other person would have the permissions required to do what needs to be done. Not saying you WILL run into those things - but these are issues you have to consider when building a scheduler.

 

[Gasman]

At $400 I can see the O/P giving it a miss.
My bosses would have vetoed that purchase when I was working as well.

 

[Gasman]

No, I was working for about $9 an hour.
I was not a developer, just someone in the office who could knock up some code. Pretty much like a lot of people that come here, I believe?

 

[Gasman]

I pretty much agree with you, but, still cannot see it happening.
Willing for the O/P to prove me wrong though.

 

[The_Doc_Man]

Chances are excellent that the FMS product actually works. Several of my clients use it to schedule backups and possibly other tasks as well.

Actually, I agree with you, Pat. My comments were strictly for building the "roll your own" type of scheduler.

I built something using the DCL scripting language for the Navy Reserve personnel system because OpenVMS didn't have a periodic task scheduler like Windows does. Never quite figured out why they didn't. Took me a while to refine it, but it was quite easy to set up new tasks with almost any kind of scheduling interval from every 15 minutes to once per year on a particular date at a particular time. All sorts of pitfalls - including what to do if you had something scheduled but we lost power during a time that overlapped the selected run-time. If power was out for more than 1 cycle, I built in the ability to either play catch-up for all of the missed cycles, or schedule once no matter how many intervals were missed, or just skip it if it couldn't run at the exact time. Just one example of how complex things could get.