Skip to content

Using the UpDown Control on an Access Form

How to put an UpDown control on an Access form, and use it to change the value of a number or date.

Access Archon #175


Introduction

The UpDown control (formerly called the SpinButton control) has been available since Access 97, at least if you bought the Developer Edition of Office.  However, like most other ActiveX controls, it has not been adequately documented.  In this article I describe how to put an UpDown control on an Access form, and use it to change the value of a number or date.

To place an UpDown control on an Access form, click the More Controls button on the Toolbox, and select the Microsoft UpDown control (whatever version you have), as shown in Figure A.  (If you have more than one version, select the highest numbered version):

 

Figure A.  Inserting an UpDown control on an Access form

The UpDown control initially appears in a vertical mode, but you can change it to horizontal in its properties sheet, which is opened from its context menu, as shown in Figure B:

Figure B.  Opening the UpDown control’s properties sheet

Figure C shows the control’s properties sheet, where you can change a few properties:

Figure C.  Changing the UpDown control to horizontal orientation

In some programs, you can select the control whose value is modified by the UpDown control on the Buddy tab of its properties sheet, but this property doesn’t work in Access, so you need to write code to modify the value.

After changing its properties, the control may behave oddly.  I found that after I changed its orientation to horizontal, the control didn’t look any different, and I couldn’t open its properties sheet again (there was no UpDown Object selection on its context menu).  After closing and reopening the form, I didn’t see the control at all.  However, I was able to select it using the drop-down list of controls, and found that it was right against the left margin, with a very tiny width.  I moved and resized it, and it saw that it had changed to horizontal mode.

Another form in the sample database, frmPicnicInfo, uses a vertical UpDown control to increase or decrease the value in the txtNoGuests control:

Figure D.  A form with a vertical UpDown control for changing a numeric value

To change the value of another control when you click an arrow on the UpDown control, use the UpDown control’s UpClick and DownClick events.  You won’t see these events in the Events tab of the regular properties sheet, but you can select them from the Procedure list in the module window.


VBA Code

The first set of procedures (from frmPicnics) shows how to increase or decrease a date value:

Private Sub ocxUpDown_DownClick()
‘Fires when left arrow is clicked
 
On Error GoTo ErrorHandler
   
   If IsDate(Me![txtPicnicDate].Value) = True Then
      dtePicnicOriginal = CDate(Me![txtPicnicDate].Value)
      dtePicnicNew = DateAdd(“d”, -1, dtePicnicOriginal)
      Me![txtPicnicDate].Value = dtePicnicNew
   End If
   
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox “Error No: ” & Err.Number & “; Description: ” & _
      Err.Description
   Resume ErrorHandlerExit
 
End Sub
 
Private Sub ocxUpDown_UpClick()
‘Fires when right arrow is clicked
 
On Error GoTo ErrorHandler
   
   If IsDate(Me![txtPicnicDate].Value) = True Then
      dtePicnicOriginal = CDate(Me![txtPicnicDate].Value)
      dtePicnicNew = DateAdd(“d”, 1, dtePicnicOriginal)
      Me![txtPicnicDate].Value = dtePicnicNew
   End If
      
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox “Error No: ” & Err.Number & “; Description: ” & _
      Err.Description
   Resume ErrorHandlerExit
 
End Sub
The set of procedures below (from frmPicnicInfo) increase or decrease a numeric value:
Private Sub ocxUpDown_DownClick()
‘Fires when down arrow is clicked
 
On Error GoTo ErrorHandler
   
   intInitialNoGuests = Nz(Me![txtNoGuests].Value)
   intUpdatedNoGuests = intInitialNoGuests – 1
   Me![txtNoGuests].Value = intUpdatedNoGuests
   
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox “Error No: ” & Err.Number & “; Description: ” & _
      Err.Description
   Resume ErrorHandlerExit
 
End Sub
 
Private Sub ocxUpDown_UpClick()
‘Fires when up arrow is clicked
 
On Error GoTo ErrorHandler
   
   intInitialNoGuests = Nz(Me![txtNoGuests].Value)
   intUpdatedNoGuests = intInitialNoGuests + 1
   Me![txtNoGuests].Value = intUpdatedNoGuests
   
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox “Error No: ” & Err.Number & “; Description: ” & _
      Err.Description
   Resume ErrorHandlerExit
 
End Sub
 

Notes

For Access 2007, you have another alternative for changing date values:  the pop-up Calendar control that appears next to text boxes bound to date fields.  If you open the sample database in Access 2007, you will see the little calendar next to the date field on frmPicnics, as shown in Figure E:

Figure E.  A Calendar pop-up on a Date control in Access 2007


References

The code in the sample database does not need any special references.  However, you do need some version of the Microsoft UpDown control.


Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch175.zip, which is the last entry in the table of Access Archon columns for Access Watch.











Document Name

Document Type

Place in

UpDown Control (AA 175).mdb

Access 2000 database (can also be used in higher versions of Access)

Wherever you want

About this author