UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Set A Time To Make Automatically A Button To Click Vba, Office 2013    
 
   
kenwood1
post Oct 22 2019, 07:21 AM
Post#1



Posts: 508
Joined: 11-July 09



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.
Go to the top of the page
 
GroverParkGeorge
post Oct 22 2019, 08:23 AM
Post#2


UA Admin
Posts: 36,018
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Daniel_Stokley
post Oct 22 2019, 08:27 AM
Post#3



Posts: 329
Joined: 22-December 14
From: Grand Junction, CO, USA


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



Go to the top of the page
 
kenwood1
post Oct 22 2019, 09:17 AM
Post#4



Posts: 508
Joined: 11-July 09



Thank you so much.

I have a hard time to make it works iconfused.gif


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 ???
Go to the top of the page
 
Daniel_Stokley
post Oct 22 2019, 09:42 AM
Post#5



Posts: 329
Joined: 22-December 14
From: Grand Junction, CO, USA


Hmmm...

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

You wrote
QUOTE
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



Go to the top of the page
 
kenwood1
post Oct 22 2019, 01:45 PM
Post#6



Posts: 508
Joined: 11-July 09



Thank you so much Daniel for all your time.

It works now. thumbup.gif
Go to the top of the page
 
Daniel_Stokley
post Oct 22 2019, 03:17 PM
Post#7



Posts: 329
Joined: 22-December 14
From: Grand Junction, CO, USA


Excellent! Glad I could help.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2019 - 11:44 AM