[X]My Assistant

[kenwood1]

Hi everyone,

I was googled it and do not find. I would like to find if there is a way to programm a button to work a certain time, like everyday 11PM or 6PM ...


Thanks in advance for any help.

[GroverParkGeorge]

Yes, but I doubt you'd "click" a button to accomplish the task at hand.

Let's step back a second and look at what a button Click event is.

Consider this stub.

CODE

Private Sub cmdRunMyCode_Click()

End Sub

Even though it is assigned to run on the click event of a command button, it is, in fact a Sub, that is to say, a VBA procedure.

Now consider this Stub.

CODE

Public Sub RunMyCode()

End Sub

They are almost identical, except the first is assigned to run on the Click event of a Command Button called cmdRunMyCode. The second is free-standing and must be called from some other event.

We can put the same code in either, or both.

CODE

Private Sub cmdRunMyCode_Click()

    Dim str As String
    str = "SELECT * FROM SOMETABLE"
    CurrentDb.Execute Query:=str, options:=dbFailOnError

End Sub

CODE

Public Sub RunMyCode()

    Dim str As String
    str = "SELECT * FROM SOMETABLE"
    CurrentDb.Execute Query:=str, options:=dbFailOnError

End Sub

And finally, we can call the free-standing Sub from the command button:

CODE

Private Sub cmdRunMyCode_Click()

    Call RunMyCode

End Sub

Public Sub RunMyCode()

    Dim str As String
    str = "SELECT * FROM SOMETABLE"
    CurrentDb.Execute Query:=str, options:=dbFailOnError

End Sub

So far, that ought to be fairly easy to follow, but let's now consider the opposite. Let's run the sub from the command button's Click event from the other Sub.

CODE

Private Sub cmdRunMyCode_Click()

    Dim str As String
    str = "SELECT * FROM SOMETABLE"
    CurrentDb.Execute Query:=str, options:=dbFailOnError

End Sub

Public Sub RunMyCode()

    Call cmdRunMyCode_Click

End Sub

See? because the sub is nothing more than VBA which can be called by clicking a button, we can also call that sub form other places as well.

And now we're down to making this work.

You can write an AutoExec macro that opens the form and "clicks" the button by called that line of code: Call cmdRunMyCode_Click

You can use Windows' Task Scheduler to open the accdb where this form resides at the required time. And when that accdb opens the AutoExec macro in it can open the form and fire the code, either version of the sub would work.
This post has been edited by GroverParkGeorge: Oct 22 2019, 08:24 AM

[Daniel_Stokley]

Hello,

Try using the form's Timer Event:

CODE

Sub Form_Timer()
  ' Use two constants to define a time window.
    Const startTime = #4:40:00 AM#   ' starting time for timer check
    Const endTime = #4:45:00 AM#     ' ending time for timer check
     Dim intweekday As Integer ' can be used to define which days of the week to allow the task to run.
     Dim bolRunReports As Boolean
    
     ' Define date ranges where the task should not run.
     If Date > #11/23/2011# And Date < #11/27/2011# Then
         bolRunReports = False
     ElseIf Date > #12/22/2011# And Date < #12/25/2011# Then
         bolRunReports = False
     Else
         bolRunReports = True
     End If
    
     If bolRunReports Then
         intweekday = Weekday(Date)
        
         If intweekday > 1 And intweekday < 7 Then ' change this control which days of the week the task may run.
             If Time() > startTime And Time() < endTime Then
     '*** Do my task here ***
                 MsgBox "Task complete.", vbOKOnly, "** Finished **"
             End If
         End If
     End If
</p><p>End Sub

[kenwood1]

Thank you so much.

I have a hard time to make it works


I just want to run 1 time per day and at 5PM for 5 minutes.

Accroding to your program. I just need to change the time, that's what I only need to do?

Const startTime = #5:00:00 AM# ' starting time for timer check
Const endTime = #5:10:00 AM# ' ending time for timer check

Can it be the setting time from Europe which cannot work ???

[Daniel_Stokley]

Hmmm...

Sorry, but I'm not sure about European time standards.

You wrote

I just want to run 1 time per day and at 5PM for 5 minutes

The code I provided has nothing to do with how many times the code runs. It only sets a time window in which the program should run.

Based on what you wrote, you should set the two constants as follows:

CODE

Const startTime = #5:00:00 PM#   ' starting time for timer check
     Const endTime = #5:05:00 PM#     ' ending time for timer check

[kenwood1]

Thank you so much Daniel for all your time.

It works now.

[Daniel_Stokley]

Excellent! Glad I could help.