Add clock control functionality to a form in Access

Ever searched for a clock control to put on a Microsoft Access form to enter time values? Or do you work with a lot of time values and wish there were an easier way to input time values? This topic describes how you can add clock control functionality to a form so you can input time values with just a few mouse clicks.

Adding the clock control functionality involves using text boxes and command buttons, and writing a few lines of code to simulate a digital clock control. This is because Access does not provide a built-in clock control.

The following illustration shows a Flight Arrival Details form that displays a clock control next to the Actual Arrival Time field.

A form with a clock

When you move to a specific record, the clock control and the ActualArrivalTime text box are updated to show the current value in the underlying date/time field. To update an actual arrival time value for a specific record, you can either type the time value directly in the ActualArrivalTime text box or use the clock control to the right of the text box to enter a value. Clicking the up and down arrows will increment and decrement the value in the hour, minute, and second text boxes. You can also change the value in the AM/PM combo box. The ActualArrivalTime text box gets updated when the time selected in the clock control changes. When you leave the record, the underlying field for the actual arrival time gets updated.

Here are the steps for creating the clock control shown in the illustration. You can change the appearance or extend the functionality of the clock control by changing the Visual Basic® for Applications (VBA) code given below.

  1. Open in Design view the form to which you want to add the clock control functionality.
  2. To the right of the control that displays the time values, add the following controls:
Control type Control name What it does
Text box txtHour

Displays the hour value.

 Note   Delete the label that is associated with the text box.

Text box txtMin

Displays the minute value.

 Note   Delete the label that is associated with the text box.

Text box txtSec

Displays the second value.

 Note   Delete the label that is associated with the text box.

Command button cmdHourUp

Increments the hour value.

 Note   Click Cancel if a wizard starts when creating any of the controls listed in this table.

Command button cmdHourDown Decrements the hour value.
Command button cmdMinUp Increments the minute value.
Command button cmdMinDown Decrements the minute value.
Command button cmdSecUp Increments the second value.
Command button cmdSecDown Decrements the second value.
Combo box cboAMPM

Lets you select AM or PM.

 Note   Delete the label that is associated with the combo box.

Label lblColon1

Displays the colon between the hour and minute values. Type a colon ( : ) inside the label.

 Note   If a green triangle appears inside the label, click it. Then click the Error Checking Options icon, and click Ignore Error.

Label lblColon2 Displays the colon between the minute and second values. Type a colon ( : ) inside the label.
Rectangle rctBase Encloses the clock controls.
  1. Size and position the individual controls as shown in the illustration. You might also want to group the controls so that it is easier to work with them. Select all the controls, including the rectangle rctBase, and, on the Format menu, click Group.
  2.  Note   If you group controls, remember that, when you want to select an individual control, you must click the control twice — the first click selects the group, and the second click selects the control.

  3. Set the Locked property of the text boxes to Yes. This will prevent a user from entering or editing a value in the clock control text boxes.

 Note   This property has been set to Yes to keep this example simple. You might want to allow the user to enter values in the text boxes, and add the necessary code to support keyboard input.

  1. Set the Caption or Picture property of the command buttons to indicate that they either increment or decrement values. If you want to display a picture on a button (as shown in the illustration), create picture files using a graphics editor such as Microsoft Paint, and then set the Picture property.
  2. Set the AutoRepeat property of the command buttons to Yes. If the user keeps the mouse button pressed down, the AutoRepeat property will cause the code to run repeatedly (and continue incrementing or decrementing the value in the corresponding text box) for the duration the mouse button is down. If this property is set to No, the value in the text boxes will increment or decrement only once per mouse click.
  3. Set the RowSourceType and RowSource properties of the combo box cboAMPM to Value List and AM;PM. Set the LimitToList property to Yes.
  4. You are now ready to write the code that is necessary to make the clock control work.

ShowAdd code to the OnClick event of the command buttons

Perform the following four steps for each of the six command buttons.

  1. Select a button, and then press F4 to display the property sheet.
  2. On the Event tab, click OnClick.
  3. Click the ellipsis (...) button next to the property to display the Choose Builder dialog box.
  4. Double-click Code Builder to open the Visual Basic Editor.
  5. Paste the code from the second column of the following table into the subroutine in the Visual Basic Editor code window that corresponds to the command button. The code calls a user-defined function named ClockChangeTime that changes the value displayed in the corresponding clock control text box. The parameters identify the button that was clicked.
Button name Code
cmdHourUp ClockChangeTime txtHour, True
cmdHourDown ClockChangeTime txtHour, False
cmdMinUp ClockChangeTime txtMin, True
cmdMinDown ClockChangeTime txtMin, False
cmdSecUp ClockChangeTime txtSec, True
cmdSecDown ClockChangeTime txtSec, False

ShowAdd code to the OnChange event of the cboAMPM combo box

  1. Select the combo box, and then press F4 to display the property sheet.
  2. On the Event tab, click OnChange.
  3. Click the ellipsis (...) button next to the property to display the Choose Builder dialog box, and then double-click Code Builder to open the Visual Basic Editor.
  4. Paste the following line of code into the subroutine in the Visual Basic Editor code window to call the user-defined function ClockSaveDate.
ClockSaveDate

ShowAdd code to the OnCurrent event of the form

  1. Double-click the form selector to display the property sheet.
  2. On the Event tab, click OnCurrent.
  3. Click the ellipsis (...) button next to the property to display the Choose Builder dialog box, and then double-click Code Builder to open the Visual Basic Editor.
  4. Paste the following line of code into the subroutine in the Visual Basic Editor code window to call the user-defined function ClockLoadDate, which will update the clock controls. Replace ActualArrivalTime with the name of the control that is bound to the underlying date/time field.
ClockLoadDate Nz(ActualArrivalTime, "")

ShowWrite the user-defined functions

The clock controls use a few user-defined functions. The code for these functions is given below. Scroll to the top of the code window, then paste the following lines of code.

 Note   The code below refers to the text box control ActualArrivalTime that is bound to the underlying date/time field. Replace the references with the name of the bound control that is on your form.

'// This routine will change the time part + or - 1.
Public Sub ClockChangeTime(oCtl As TextBox, fIncrement As Boolean)
    Dim iChange As Integer
    
    'See if we are adding or subtracting.
    If fIncrement Then
        iChange = 1
    Else
        iChange = -1
    End If
    
    'Find out what part of the time we are supposed to act on.
    Select Case oCtl.Name
        'Check for the hour.
        Case Is = "txtHour"
            'Check to see if we need to change am/pm.
            If txtHour = 11 And iChange = 1 Then ClockFlipAMPM
            If txtHour = 12 And iChange = -1 Then ClockFlipAMPM
            
            'Change the hour.
            txtHour = txtHour + iChange
            
            'See if we need to roll over.
            If txtHour <= 0 Then
                txtHour = 12
            ElseIf txtHour >= 13 Then
                txtHour = 1
            End If
            
        'Check for the minutes.
        Case Is = "txtMin"
            txtMin = txtMin + iChange
        
            'See if we need to roll over.
            If txtMin <= -1 Then
                txtMin = 59
                ClockChangeTime txtHour, False
            ElseIf txtMin >= 60 Then
                txtMin = 0
                ClockChangeTime txtHour, True
            End If
        
        'Check for the seconds.
        Case Is = "txtSec"
            txtSec = txtSec + iChange
            
            'See if we need to roll over.
            If txtSec <= -1 Then
                txtSec = 59
                ClockChangeTime txtMin, False
            ElseIf txtSec >= 60 Then
                txtSec = 0
                ClockChangeTime txtMin, True
            End If
    End Select
    
    'Be sure we have 0's in front.
    ClockAddLeadingZeros
    
    'Save the date back to the recordset.
    ClockSaveDate
    
    'Let's refresh in case the user is holding down the button.
    DoEvents
End Sub

'// Adds 0's to the front of the time parts.
Private Sub ClockAddLeadingZeros()
     If Len(txtHour) <> 2 Then txtHour = "0" & txtHour
     If Len(txtMin) <> 2 Then txtMin = "0" & txtMin
     If Len(txtSec) <> 2 Then txtSec = "0" & txtSec
End Sub

'// Used to write the date back to the form value.
Private Sub ClockSaveDate()
    If cboAMPM = "PM" And txtHour <> 12 Then
        ActualArrivalTime.Value = TimeSerial(txtHour + 12, txtMin, txtSec)
    Else
        ActualArrivalTime.Value = TimeSerial(txtHour, txtMin, txtSec)
    End If
End Sub

'// Called to initalize the textboxes with the date.
'// Can be called on oncurrent event to sync with form.
'// recordset movement
Private Sub ClockLoadDate(sDate As String)
    'Check to see if we have a valid date.
    If IsDate(sDate) Then
        'Load the hour.
        txtHour = Hour(sDate)
        If txtHour = 0 Then txtHour = 12
        If txtHour > 12 Then txtHour = txtHour - 12
        
        'Load the minutes and seconds.
        txtMin = Minute(sDate)
        txtSec = Second(sDate)
        
        'Load the AM/PM.
        If Hour(sDate) < 12 Then
            cboAMPM.Value = "AM"
        Else
            cboAMPM.Value = "PM"
        End If
        
        'Be sure we have 0's in front.
        ClockAddLeadingZeros
    Else
        'Not a valid date; set a default.
        txtHour = "12"
        txtMin = "00"
        txtSec = "00"
        cboAMPM = "AM"
    End If
End Sub

'// Used to flip am/pm when rolling over between the 2.
Sub ClockFlipAMPM()
    If cboAMPM.Value = "AM" Then
       cboAMPM.Value = "PM"
    Else
        cboAMPM.Value = "AM"
    End If
End Sub
  1. Close the Visual Basic Editor window, and switch to Form view.
  2. Test the form. The clock control should display and update the date/time field as you move from record to record.

If you want to add a calendar to a form, report, or page, to display and set dates using a calendar instead of typing in a text box, see Using the calendar control in Access.

 
 
Applies to:
Access 2003