Running a "Run All" using Scheduled Task
Results 1 to 9 of 9

Thread: Running a "Run All" using Scheduled Task
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Running a "Run All" using Scheduled Task

    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.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,437
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    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:
    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
    'lbwait.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
    






    Quote Originally Posted by xenou View Post
    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.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,437
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    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
    Quote Originally Posted by xenou View Post
    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.

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,673
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    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.

    Quote Originally Posted by Micron View Post
    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.

  8. #8
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,673
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    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.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,437
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Running a "Run All" using Scheduled Task

    Here is a sample of how I have done this:
    https://www.mrexcel.com/forum/micros...cess-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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •