Solved Task Scheduler Automation (1 Viewer)

[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

 

[Pat Hartman]

I fancy building one on my own.

You might look at the one FMSINC sells. They have many useful Access tools.

 

[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.

 

[Pat Hartman]

Pat, thank you - do you have a link?

We are not supposed to post links. Use the name I gave you plus .com

 

[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.

 

[Pat Hartman]

Looks like Tom found your coding problem but you still might want to look at the FMS product.

 

[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.

 

[Pat Hartman]

but these are issues you have to consider when building a scheduler.

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

 

[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.

 

[Pat Hartman]

My bosses would have vetoed that purchase when I was working as well.

Your time isn't free. What was your fully burdened hourly rate? How many hours would it take to produce something even close to being as functional as what FMS offers? I'm guessing that unless you were working almost for free, your fully burdened rate would have been $75-$100 per hour. That gives you 4-5 hours to complete this project. Go -

 

[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?

 

[Pat Hartman]

You still would have been hard pressed to build something even close to that functionality for ~ $16 per hour.

 

[Gasman]

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