Creating a Spin Box Control

Many Windows dialog boxes offer a spin box control, which lets you change a number without typing. Oddly enough, there's no spin box control in the Controls group to let you create such a control on your Access forms. If you want to add a spin box control to an Access form, you have to fudge it. Writing the code for the spin buttons is easy; creating the little buttons is the real challenge.

We've used numerous techniques to create the spin buttons. We've imported ActiveX controls, used command buttons with special characters, like up or down arrows, and even used transparent-background GIFs to put a tiny arrow on each command button. Because the spin buttons are so tiny, though, getting the command button to look right is difficult.

We finally just gave in and drew each button as a tiny graphical image. (It really doesn't matter whether you use a command button or a picture for the spin button because buttons and pictures both have On Click event properties to which you can tie code.) Figure 9-8 shows buttons that we drew for this example magnified 800 percent in Paint. The lower half of that figure shows the buttons in place on a form. To get the button images onto the form, we just used the Image control in the Controls group on the (Form Design Tools) Design tab.

Figure 9-8:

Spin button images in a program (top) and on a form (bottom).

Figure 9-8:

Spin button images in a program (top) and on a form (bottom).

Spin buttons

Regardless of whether you use command buttons or pictures to get spin buttons onto a form, getting them to work is the same. You can name each button or image as you would any other control (via the Name property on the All tab of the property sheet). We named our two image controls

SpinUpBttn and SpinDownBttn.

After you have the controls on the form in Design view, click the Spin Up image control, click Events in the property sheet, click the On Click event property, click the Code button, and then choose Code Builder. The VBA Editor opens with the cursor already in a procedure named SpinUpBttn_Click() (assuming that you named your Spin Up button SpinUpBttn). In our example, we want each click of the Spin Up button to increase the value in the LabelsToSkip control by 1.

Use an lf...Then...End If statement to put an upper limit on how high the value can go. We chose 80 as an upper limit (because we doubt that many label sheets offer more than 80 labels per page), but you can set your upper limit to any value you want. The following code increases the value in the

LabelsToSkip control each time a user clicks the form's SpinUpBttn control:

Private Sub SpinUpBttn_Click()

'Increase LabelsToSkip by 1 to a maximum of 80. If MelLabelsToSkip.Value < 80 Then

MelLabelsToSkip.value = Me.LabelsToSkip.Value + 1 End If End Sub

After writing the code for the SpinUpBttn and returning to your form in Design view, click the SpinDownBttn control on your form. Again, get to that control's On Click event property in the property sheet and write a routine like the one that follows. In that example, we put a lower limit of 0 (zero) on the value in the LabelsToSkip control:

Private Sub SpinDownBttn_Click()

'Decrease LabelsToSkip by 1 to a minimum of 0. If MelLabelsToSkip.Value > 0 Then

MelLabelsToSkip.value = Me.LabelsToSkip.Value - 1 End If End Sub

The following code shows both procedures in place in the class module for our Fancy SkipLabels dialog box example. Again, the biggest trick to getting spin buttons on a form is getting buttons that are small enough to fit next to the control. After you have a command button or picture in place, you can program its On Click event procedure to increase or decrease the value in a numeric field by 1 with each click.

Private Sub SpinDownBttn_Click()

'Decrease LabelsToSkip by 1 to a minimum of 0. 'If MelLabelsToSkip.Value > 0 Then

MelLabelsToSkip.Value = Me.LabelsToSkipValue - 1 End If End Sub

Private Sub SpinUpBttn_Click()

'Increase LabelsToSkip by 1 to a maximum of 80. If MelLabelsToSkip.Value < 80 Then

MelLabelsToSkip.Value = Me.LabelsToSkip.Value + 1 End If End Sub

+1 0

Responses

  • NEGASSI
    How would you write a code to a control in access2007?
    8 years ago

Post a comment