John W. Colby
jwcolby at colbyconsulting.com
Sat Jan 17 09:51:17 CST 2004
Hengky, I have whipped out a pair of classes to do something close to what you are asking for. The first class is used to track each thing that needs to be run (assuming once a day). In order to create a class, go to the VB Editor, click Insert / Class. Into that paste the following (you may need to get rid of the option statements), then save it as clsWakeup Option Compare Database Option Explicit ' 'This class holds information about a single wakeup ' Private mdteLastRan As Date 'The last date a wakeup was done Private mdteTimeToRun As Date 'The time to say wakeup Private mstrProcessName As String 'The process name needing woken up ' 'The class_Initialize is similar to Form_Open in that it always runs ONE time as a class opens 'Use this to initialize the LastDateRan variable ' Private Sub Class_Initialize() mdteLastRan = Date - 1 End Sub ' 'Each instance of this class will hold a time to run (wakeup) 'And a process to wakeup ' Function Init(ldteTimeToRun As Date, lstrProcessName As String) mdteTimeToRun = ldteTimeToRun mstrProcessName = lstrProcessName End Function ' 'This function checks whether the process has run in the last 24 hours 'If so it returns true and passes back the process name in a string variable 'passed in ' Function Run(lstrProcessName) As Boolean 'Hasn't run today so check if the time is > mdetTimeToRun If Time() > mdteTimeToRun Then If Date > mdteLastRan Then 'Mark mdteLastRun = Now() mdteLastRan = Date 'and return true lstrProcessName = mstrProcessName Run = True End If End If End Function *************** The class above in instantiated once for each call to be placed in your case The next class is a "supervisor" class that contains a collection of clsWakeup (the class above). It is instantiated once in a form. The supervisor class manages the collection of wakeup classes, adding them in on demand, and closing them down as the form closes. It also polls each clsWakeup in the collection once every time its method CheckWakeup is called. Again, in the editor click insert / class and paste the following in. *************** Option Compare Database Option Explicit 'A collection to hold instances of clsWakeup Private colClsWakeup As Collection 'An event to raise if a clsWakeup instance says its time to wakeup Public Event ProcessTime(lstrProcessName As String) ' 'The class_Initialize is similar to Form_Open in that it always runs ONE time as a class opens 'Use this to create the collection to hold the wakeup classes ' Private Sub Class_Initialize() Set colClsWakeup = New Collection End Sub ' 'Class_Terminate is similar to Form_Close and always runs ONCE as the class terminates 'Use it to unload all the clsWakeup instances in the collection ' Private Sub Class_Terminate() term End Sub ' 'As the supervisor class closes, it must unload all the clsWakeup instances in its collection ' Function term() On Error Resume Next While colClsWakeup.Count > 0 colClsWakeup.Remove (1) Wend End Function ' 'This function is responsible for creating one instance of clsWakeup every time it is called ' Function NewWakeup(ldteTimeToRun As Date, lstrProcessName As String) Dim lclsWakeup As clsWakeup Set lclsWakeup = New clsWakeup 'It then initializes that instance with the TimeToRun and the ProcessName lclsWakeup.Init ldteTimeToRun, lstrProcessName 'And finally, it saves a pointer to the clsWakeup instance just created in the collection colClsWakeup.Add lclsWakeup, lstrProcessName End Function ' 'CheckWakeup is called by a timer tick on a form. It cycles through all the instances of clsWakeup 'in its collection asking each instance if it's time for that instance to run. ' Function CheckWakeup() Dim lclsWakeup As clsWakeup Dim lstrProcessName As String For Each lclsWakeup In colClsWakeup 'Check each instance of clsWakeup If lclsWakeup.Run(lstrProcessName) Then 'If time to run then RaiseEvent ProcessTime(lstrProcessName) 'Raise an event telling the world End If Next lclsWakeup End Function ************** OK, so the class above must be created once in a form whose timer will call the CheckWakup method to look for wakeups to be performed. Notice that I use Raisevent to pass back to the calling form each object in the class that needs servicing. So, now we need a form to allow us to manually "program" the supervisor with times and processes to wakeup, a status to show that something is happening, and a control to display that a process was awakened. You need to build a form with 4 text boxes named txtNewTime and txtProcessName (used to enter the time and process name to wake up), txtStatus to display a status message, and txtWakingUp to display the name of the process waking up. Also a Command6 command button to close the form. Then in the text editor to display the form's module and paste the following in: ************** Option Compare Database Option Explicit ' 'Dimension a supervisor class Withevents ' Private WithEvents fclsWakeupSupervisor As clsWakeupSupervisor ' 'When the form closes we need to unload the supervisor class ' Private Sub Form_Close() Set fclsWakeupSupervisor = Nothing End Sub ' 'When the form opens we need to create an instance of the supervisor class ' Private Sub Form_Open(Cancel As Integer) Set fclsWakeupSupervisor = New clsWakeupSupervisor End Sub ' 'The close button (standard stuff) ' Private Sub Command6_Click() On Error GoTo Err_Command6_Click DoCmd.Close Exit_Command6_Click: Exit Sub Err_Command6_Click: MsgBox Err.Description Resume Exit_Command6_Click End Sub ' 'The form's timer event will be used to call the supervisor class' CheckWakeup method ' Private Sub Form_Timer() 'Erase the last value to make clear that it only was set once / 24 hours txtWakingUp.Value = "" 'And write a status to the status box to make clear that we are calling this every 10 seconds txtStatus.Value = "Checked wakeup list at: " & Now 'Then call the supervisor's CheckWakup method fclsWakeupSupervisor.CheckWakeup End Sub ' 'This is a very simple way of programming the processes that need to be wakened every day ' Private Sub txtProcessName_AfterUpdate() 'pass in the time to do the wakeup and the process name of the process to wake up '!!!!notice no error handling such as was anything entered in time!!!! fclsWakeupSupervisor.NewWakeup txtNewTime.Value, txtProcessName.Value 'if the form timer is not initialized, then set the interval to 10 seconds If Me.TimerInterval = 0 Then Me.TimerInterval = 10000 End If End Sub ' 'This is the event from the supervisor that will be raised if any process needs to be awakened. ' Private Sub fclsWakeupSupervisor_ProcessTime(lstrProcessName As String) txtWakingUp.Value = lstrProcessName End Sub ****************************************** Ok, so the idea is that the user opens the form, which loads the supervisor class. The user then enters times and process names into the two text boxes for this purpose. As soon as the first process is entered the timer starts ticking and every 10 seconds checks are made whether to wake someone up. If any process needs to be awakened, fclsWakeupSupervisor_ProcessTime(lstrProcessName As String) is called passing in the name of the process to awaken. The developer would put a function call in fclsWakeupSupervisor_ProcessTime to do whatever they want done once a day. I will eventually get this up on my web site as a Withevents demo. For now I am sending the entire database to John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hengky Lie Sent: Friday, January 16, 2004 8:54 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Wake up Call Program How to build a class ? Sorry, my Access level still below than average :-( John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hengky Lie Sent: Friday, January 16, 2004 8:54 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Wake up Call Program How to build a class ? Sorry, my Access level still below than average :-(