Running a "Run All" using Scheduled Task

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
Greetings,

I can probably do this for something similar but I just cannot piece together my needs for this specific task. I did not write the code but know if I click the button "Run All" it will go through all the processes contained below.

My goal is to run this section on a schedule where it runs 6a and 6p.

I thought I could just create a macro for it and then do a Schedule Timer in Windows, but I cannot figure what I'm selecting when I go to create a macro. If it was just one query I could just select the query.

Any help would be great.

I did google and found a great guide but it focused on using only one macro for 1 query.

Anyway, any thoughts on what would work? Basically the "Run all" has a section where it calls each of the other queries and goes through them all and then it's done.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,667
Office Version
2013
Platform
Windows
Hi, you need to analyze how the button works, so you can do the same thing without having to actually click the button. Probably it calls code or runs a macro. So you just need to be able to call the same code or run the same macro.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
Okay so,

I went to make a macro and selected "RunCode" but wasn't sure what the Function Name should be.

I'm hoping I'm getting warmer, and I found the code that runs when I click the button is the following;

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub cmdsyncrunall_Click()
'Dim lbwait
'Call DriveTest 'Module 'DriveSpace' checks for low drive space, F drive
On Error Resume Next 'Run All'
lbRCS.Caption = "Sync TNS with CIS"
'lbwait.Visible = True
RCS.Visible = False
lbupdate.Visible = True
lbRCS.Visible = True
cmdsyncrunall.Caption = "Wait"
lblwait.Visible = True
RCS.SourceObject = ""
lbrandate.Caption = ""
Dim I As Integer
Dim cl As Integer
    
    Call metertype 'updates missing metertype 97 &109
    Call meterclass 'updates GEKV2C meter class to 102
   ' Call cmdcycle99  'Cycle 99 update
    Call cmdpremise 'Premise
    Call cmdacct  'Acct
    Call cmdrate 'Rate
    Call cmdcycle  'Cycle
    'Call cmdpropane  'Propane Acct no longer used
    Call cmduser2 'Updates Active Meters reading in TNS user2 notes
    Call cmduser1 'Updates User1 field with DSI Collar SN#
    Call cmduser6 'Updates DRU number in TNS user6 notes
    Call cmdmissingmeter ' Updates missing meter#
'    Call GensEx 'Changes class of Gen to Inactive when GLS expires
    Call delstatusread 'Deletes Switch Reads[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'lbwait.Visible = False
'Call fhide[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]lbRCS.Caption = "TNS Desktop Applications"
cmdsyncrunall.Caption = "Run All"
RCS.Visible = True[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]With DoCmd
    .SetWarnings False
    .OpenQuery "Date_Update_Query"
    .Close acQuery, "Date_Update_Query", acSaveYes
    .OpenTable "Date_table"
    .Close acTable, "Date_table", acSaveYes
    .SetWarnings True
End With
cl = DCount("*", "Meter_Class_Service_Multiplier_Query")
'i = DCount("*", "Accts w/Inactive_Gens and Incorrect Rate Code")
DoCmd.OpenQuery "Estimated_Query"
DoCmd.OpenQuery "AMR_Missing_IntervalReads"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]DoCmd.OpenForm "lookup Tasks Queries"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]lbrandate.Caption = DLookup("todaydate", "Date_table", "key = 1")
Call countrecords
If RCS.SourceObject = "" Then lbupdate.Caption = "" Else lbupdate.Caption = "Inactive Gen Accts that needs Rate Change"
If cl > 0 Then DoCmd.OpenQuery "Meter_Class_Service_Multiplier_Query", acViewNormal[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]FinalNote = MsgBox("Sync Complete")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'isdone:
   ' Call cmdexit
End Sub
[/FONT]




Hi, you need to analyze how the button works, so you can do the same thing without having to actually click the button. Probably it calls code or runs a macro. So you just need to be able to call the same code or run the same macro.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,667
Office Version
2013
Platform
Windows
Looks like you will have to move the code from a private function (in a form) to a public function (in a module). Then you can run it as an automated task. That would be my preference.

I am not sure if you leave it as a private function in a form -- possibly you can run it from in a form but the exact details I am not sure in that case - maybe maybe not.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
Thanks for the tip,

I found a module called obsolete code and it had the following in it. I did add some of the code from the form area to this area, maybe it'll work....

Code:
Option Compare Database


Private Sub cmdrunall()
On Error Resume Next 'Run All'
lbRCS.Caption = "Sync TNS with CIS"
RCS.Visible = False
lbupdate.Visible = True
lbRCS.Visible = True
cmdsyncrunall.Caption = "Wait"
lblwait.Visible = True
RCS.SourceObject = ""


'Call UpdateConnectStrings
   
    Call metertype 'updates missing metertype 97 &109
    Call meterclass 'updates GEKV2C meter class to 102
    Call cmdcycle99  'Cycle 99 update
    Call cmdpremise 'Premise
    Call cmdacct  'Acct
    Call cmdrate 'Rate
    Call cmdcycle  'Cycle
   'Call cmdpropane  'Propane Acct
    Call cmduser2 'Updates Active Meters reading in TNS user2 notes
    Call cmduser1 'Updates User1 field with DSI Collar SN#
    Call cmduser6 'Updates DRU number in TNS user6 notes
    Call cmdmissingmeter ' Updates missing meter#
    Call cmdroute  'Route
    'Call GensEx 'Marks Gens Inactive in DRU Manager when GLS expires
    Call delstatusread 'Deletes Switch Reads
    
lblwait.Visible = False
'Call fhide


lbRCS.Caption = "TNS Desktop Applications"
cmdsyncrunall.Caption = "Run All"
RCS.Visible = True
With DoCmd
    .SetWarnings False
    .OpenQuery "Date_Update_Query"
    .Close acQuery, "Date_Update_Query", acSaveYes
    .OpenTable "Date_table"
    .Close acTable, "Date_table", acSaveYes
    .SetWarnings True
End With
cl = DCount("*", "Meter_Class_Service_Multiplier_Query")
'i = DCount("*", "Accts w/Inactive_Gens and Incorrect Rate Code")


DoCmd.OpenQuery "Estimated_Query"
DoCmd.OpenQuery "AMR_Missing_IntervalReads"
DoCmd.OpenForm "lookup Tasks Queries"


lbrandate.Caption = DLookup("todaydate", "Date_table", "key = 1")


Call countrecords
If RCS.SourceObject = "" Then lbupdate.Caption = "" Else lbupdate.Caption = "Inactive Gen Accts that needs Rate Change"
If cl > 0 Then DoCmd.OpenQuery "Meter_Class_Service_Multiplier_Query", acViewNormal
FinalNote = MsgBox("Sync Complete")


'isdone:
' Call cmdexit


End Sub
Looks like you will have to move the code from a private function (in a form) to a public function (in a module). Then you can run it as an automated task. That would be my preference.

I am not sure if you leave it as a private function in a form -- possibly you can run it from in a form but the exact details I am not sure in that case - maybe maybe not.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,845
what should work:
change Private declaration for button click to Public
macros must call functions AFAIK, same as toolbars, menubars, ribbon, etc. so in a standard module,
Function RunSomeCode()
Forms![frmFormName].cmdMyButtonName_Click
End Function

There is no error trapping here or assurance that the form is open so if you use that, suggest it gets embellished somewhat. I'm just showing the basics of how it can be done.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
Thanks for all the replies.

The person that wrote all this was very talented but no longer working with us and I was asked to do a scheduled task that would run this daily and I'm trying to figure out how to make that happen.

So I think I'm getting closer, with the replies here and research.

what should work:
change Private declaration for button click to Public
macros must call functions AFAIK, same as toolbars, menubars, ribbon, etc. so in a standard module,
Function RunSomeCode()
Forms![frmFormName].cmdMyButtonName_Click
End Function

There is no error trapping here or assurance that the form is open so if you use that, suggest it gets embellished somewhat. I'm just showing the basics of how it can be done.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,845
One way I have done this in the past was to have TS open the db with a command switch because the pc for this was solely used for the purpose of updating db's in the middle of the night. It was always logged on and had its own profile. Anyway, because regular users were logged in but not the updater, I had to distinguish who was opening db - person or machine. Hence the command line switch that in the shortcut that TS used. Maybe you don't need that level of complexity, but if you can get TS to open db, startup code can run any updates. However, I suppose checks will be required for you, as ensuring no one else is logged in? Should be quite doable once you get the TS part worked out.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,667
Office Version
2013
Platform
Windows
Here is a sample of how I have done this:
https://www.mrexcel.com/forum/microsoft-access/764991-auto-trigger-ms-access-code.html

Others prefer to have a scheduled task that opens msaccess. Then trigger startup code that runs when the database opens (i.e., what micron is describing above).

You can put the code that the form calls in a public function or sub in a module. The form click can call the code but you could also then call the code without having to use the form at all.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,774
Messages
5,482,837
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top