Converting Private Sub to Public Function

[ctackett6407]

I have been on a mission to help automate a task I do every morning.

A person created an Access application years ago that we still use to compare two databases and kind of run all kinds of queries and tables. When I open the application I click a "Run All" button and then it just goes off and running and completing the list of commands inside the modules.

I've asked questions and poked around and found that I might be able to get this to work with Task Scheduler if I create something as a Public Function , add a macro somewhere, and then use Task Scheduler to invoke the access application with some type of parameter.

I'm been trying to figure out where to start on this as I reviewed the application the person created and everything in it is Private Sub.

Do I have to convert this or can I convert this? I'm hoping I'm going to be told to "run a replace all for Private Sub, because it's just that easy!"

 

[JLGWhiz]

You can find Windows Task Scheduler under the Administrative Tools by clicking the Windows Start button on your desktop status bar. If you enter the macro name for the macro attached to your "Run All" button as the file to schedule and set the parameters for days and timed by following the user friendly menu selections, it should resolve you issue.

 

[ctackett6407]

Doesn't the module have to be a public function? currently, everything I have is in Private Sub, this is what happens when I click the button in my Access Application;

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 delpendingcmds 'Deletes pending SDC Actions and Commands



'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

You can find Windows Task Scheduler under the Administrative Tools by clicking the Windows Start button on your desktop status bar. If you enter the macro name for the macro attached to your "Run All" button as the file to schedule and set the parameters for days and timed by following the user friendly menu selections, it should resolve you issue.

 

[JLGWhiz]

It might take some reading and experimenting on your part to accfomplish the use of the scheduler. But if the Private in the procedure name bothers you, you could simply rename the procedure to

Sub myFirstTryAtScheduling()

and copy it to a public module. Then use that name for the scheduler. There are tutorials on the web for using the scheduler if you type "Windows Task Scheduler Tutorial" in the browser window and press enter, it will bring them up for you.l

 

[ctackett6407]

All the reading I came across I was under the impression that I couldn't do any of this if it was Private, that's why I was trying to figure out how to make it public.

the steps I found were

Create a Public Function (not Sub) in the database
Create a Macro in the database to invoke the function:
Create a Windows Scheduled Task to invoke MSACCESS.EXE with the appropriate parameters

Can this be done on private?

It might take some reading and experimenting on your part to accfomplish the use of the scheduler. But if the Private in the procedure name bothers you, you could simply rename the procedure to

Sub myFirstTryAtScheduling()

and copy it to a public module. Then use that name for the scheduler. There are tutorials on the web for using the scheduler if you type "Windows Task Scheduler Tutorial" in the browser window and press enter, it will bring them up for you.l

 

[xenou]

The sub needs to be a public sub if you are going to call it from a scheduled task. Just change Private to Public. As far as I know it can be a sub or a function, shouldn't matter.

*But* if its not in a regular module (for instance, if its in a form) that could be a little tricky. Hopefully that's not the case.

 

[ctackett6407]

how can I tell if it's a regular module or not?

and to confirm, I can just go in and switch "Private" to "Public?"

Let's just say I'm jumping in head first

 

[JLGWhiz]

You have code modules for ThisWorkbook, Sheets, UserForms and public. The public ones are the one you insert and Excel gives them a number like Modul1. The others are all built in with the Excel application and the Private in front of the Sub name indicates that it will only run from events in that host object. If you open the vb editor, you will see the different modules for that workbook in the Project pane at upper left on the editor window.If it starts with "Module" it is a public code module and there can be more than one of them. These can also be used to create a new Class and would then be identified with an icon different than the public modules.

More here: Code Modules And Code Names

 

[ctackett6407]

Okay so I pulled this out, and now I'm reading that link you provided.

 

[JLGWhiz]

didn't realize you were in Access, thought you were in Excel. But any of those listed under 'Modules' should be OK to put the macro into and call it from the Task Scheduler.

This is more what I was expecting to see.