Sub Forms > MS Access Time Control

Navigation:  Sub Forms >

MS Access Time Control Form                                                    

Previous pageReturn to chapter overviewNext page

by Garry Robinson

 This article explains how to deploy the following 24 Hour Clock popup sub form in your application.

 Find out about the download here

Figure 1 - Garry's fx_time popup form


24 Hour Time Control For Microsoft Access


A few years ago I was involved in a truck weighbridge entry system for a mining company in Australia.  As part of that project, I developed a form that was used to enter times into the time fields in a Access database.  This form subsequently ended up being used at a number of sites of large Australian trucking  company to clock in the drivers and to enter the times that each job commenced and was completed.  This form was written in Access 2 and has stood the test of time through to Access 97.  The other really good thing about this form is that while it acts like a Active X control (i.e. the calendar wizard), it doesn’t have the additional overheads of management that come with these "Smart Controls".  Experience has taught me that whilst these external controls might initially offer some additional features that jazz up your application, at some time in the future, they are going to cause a problem. This is especially when you start shipping your application to remote locations.

The purpose of this article is to first introduce the TimeClock form so that you can use it in your own systems and then to explain how it works.  This article will also be of use if you intend building your own Access wizards as the techniques for managing last control and last form and sub form are very similar.


The Accompanying Database


An  Access 2007 database called timeclock.accdb with the following objects


DriversClock                A table of drivers names, dates, times and odometer readings that is used with the test form

TlkpDrivers                A lookup table that is used to hold the drivers names

FX_driversClock                The main form in the application that is used for the data entry

FXL2_time24                The 24 hour time clock form



Using The 24 Hour Time Clock ?


Initially you will probably need is to establish a time only field in a database table.  The Time Clock will not return the date as it is assumed that the date is stored separately in a different field.   To do this make a date/time field in your table and then change the format of this field to "Short Time".

To fire up this form, I use the double click event on the time fields.  The code looks like this


Private Sub loginTime_DblClick(Cancel As Integer)

   DoCmd.OpenForm "FXL2_time24"

End Sub


Initially it is recommended that you add some text to the ToolTips for the time field such as

"Time that the driver logs in - Double Click to fire up clock"

That’s all the code that you need to initiate the control on a normal form.  When the user has made their time selections, the TimeClock will return the selection to the current time field.

If you are going to fire up the TimeClock in a subform, the double click code to drive the control is as follows


DoCmd OpenForm "FX_time24", , , , , , "sFrmObject_FXDriversClock"


In this case you are passing the name of the subform control on the main form using the OpenArgs  argument of the DoCmd openForm call.


The Layout Of The 24 Hour Clock Form


The Form is made up of a option group that holds all the hours, an option group that holds all the five minute intervals plus a Confirm and Quit button.  Clicking on any of the hour option makes the variable that holds the users new selection change.  Having just worked on a project in VB 5 where you had to write code to handle all the buttons using loops, it is good to return to the easier Access option groups where the option group has an on click and double click event.

The two important properties of the form that make it behave like a control are found "Other" section of the form properties and they are


Pop Up = Yes

Modal = Yes


The Pop Up and modal properties make sure that the form appears on top of all other forms and ensures that you have to close the Form before you can do anything else inside your Access application.  This then allows you to pass the users time selections back to the calling field when you make the selection.  If you are going to modify the code behind the form, you will find it easier to turn the PopUp and Modal properties off whilst you are doing this.


Finding Out About The Current  Controls


Immediately the TimeClock opens, a subroutine called LastControl is initiated which finds the last control used on the data entry form.  It does this using the Screen Object (search for Screen in Access help).  The screen object returns details about the form that currently has focus and the control currently in use (the time text box).  The properties of the Screen object are very useful when you are building Access Wizards and Addins as you sometimes have to know where the application was prior to calling the Wizard.


   lastFrm = Screen.ActiveForm.Name

   lastSubFormCtl = Me.OpenArgs

   lastCtl = Screen.ActiveControl.Name


Now if the TimeClock was called from a subroutine, you will need to pass the name of the Control that hold the sub form through to this form using the OpenArgs argument.  This is handled as follows


    If IsNull(lastSubFormCtl) Then

'     No subform has been defined in the OPENARGS of docmd openForm call

      fieldTime = Forms(lastFrm)(lastCtl)


'     Forms![main form name]![subform control name].Form![control name]

      fieldTime = Forms(lastFrm)(lastSubFormCtl).Form(lastCtl)

    End If


Finally the software looks at the last field and places the time into the common variable that holds the time.


   lastFieldTime = fieldTime

   fieldTime = Format(fieldTime, "Short time")


Fit The Time To The Hours and Minutes Option Groups


The next requirement is to split the hours and minutes and round the minutes to the nearest five to suit the option group entry selection systems.  For this use the string handling routines  "Mid and Left" and "Int" to round the time to five minutes.  The two option group are called optHrs and optMins and when these are set with the current time or the time that was last entered in the field, the option boxes will change to match the time


   startPos = IIf(Mid(fieldTime, 2, 1) = ":", 1, 2)


   hoursVar = Left(fieldTime, startPos)

   Me![optHrs] = hoursVar


   minutesVar = Mid(fieldTime, startPos + 2, 2)

   minutesVar = Int(minutesVar / 5)

   minutesVar = minutesVar * 5


   Me![optMins] = minutesVar


And the final thing that I do is to set the Caption for the form Window with the help of the Format statement to manipulate the current option group settings


Me.Caption = "Time      " & lastCtl & " ... " & _

Format(hoursVar, "00") & ":" & Format(minutesVar, "00")


The Form Close Event


After the user has made their selection using the Confirm button or decided not to make a change using the Quit button, we need to close down the form and return the new time to the Time field on the last control.  This code shows how you can return the answer to a field in a subform if required.



  If returnTime Then

    fieldTime = Format(hoursVar, "00") & ":" & Format(minutesVar, "00")

    If IsNull(lastSubFormCtl) Then

'     No subform has been defined in the   OPENARGS of docmd openForm call

      Forms(lastFrm)(lastCtl) = fieldTime


'     Forms![main form name]![subform control name].Form![control name]

      Forms(lastFrm)(lastSubFormCtl).Form(lastCtl) = fieldTime

    End If

  End If






The main lesson that I have learned from the use of this type of form is that if the technology is totally internal, it can be reused in many versions of Access at many sites with very little ongoing maintenance.  This compares very favorably with the use of external OLE or Active X objects where the controls have to be installed separately to the Access application.  


The download for this article is here


Other Articles that Are Related

About Time
When DateAdd Doesn't