Show All

Hide All

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.

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.

   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.

   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.

4. 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.
5. 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.
6. Set the RowSourceType and RowSource properties of the combo box cboAMPM to Value List and AM;PM. Set the LimitToList property to Yes.
7. You are now ready to write the code that is necessary to make the clock control work.

   Add 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

   Add 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

   Add 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, "")

   Write 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

   
   
8. Close the Visual Basic Editor window, and switch to Form view.
9. 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.