Running a "Run All" using Scheduled Task

[ctackett6407]

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.

 

[xenou]

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]

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;

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

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]

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

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]

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]

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]

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]

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.