WklSpinEdit

Dim myWklSpinEdit As WklSpinEdit
Set myWklSpinEdit = Me!ControlName.Object

A number with up and down arrows, bound like an Access text box - limits, a step, decimal places and an optional unit after the number, for example a session length in hours or the lifetime of a key in days.

None

Bind it through Control Source like a text box: Access writes the field into Value on every record change. The first change of the user asks Access whether the field may be edited; a locked control (Locked) or a form without edits (AllowEdits = False) refuses. Every change marks the record as changed, and Access writes the value into the field when the record is saved at the latest.

The arrows, Up/Down (one Step) and Page Up/Page Down (ten steps) never leave MinValue and MaxValue; the mouse wheel steps while the field has the focus. A typed number is taken while it is valid. When the user leaves the field with a text that is no number or lies outside the limits, the value the field had when the user entered it comes back and InvalidEntry occurs - the control shows no message box of its own, so the form decides how to tell the user. Esc puts back the value before the user's first change; a second Esc goes to Access.

Unit is display text: the field keeps the number in that unit. Enabled, Locked, Visible, TabStop and the tooltip (ControlTipText) are Access's own properties of the control.

An ActiveX control: inserted into a form in design view (Form Design › Controls › ActiveX Controls); reports are not supported. VBA reaches it through the Object property of the control, where ControlName is the name of the control on the form.

' Once, in design view: the lifetime of a self-issued key in days.
With Forms!frmKey!spnDays
    .ControlSource = "LaufzeitTage"
    .Object.MinValue = 1
    .Object.MaxValue = 3650
    .Object.Step = 30
    .Object.Unit = "Tage"
End With
 
' Form module: tell the user why an entry was refused.
Private Sub spnDays_InvalidEntry(ByVal Text As String, ByVal Message As String)
    Me!lblHint.Caption = Message
End Sub

None

Property Get Value() As Variant
Property Let Value(RHS As Variant)

Gets or sets the value: the number, or Null for an empty field.

None

Property value: Variant – A Long when DecimalPlaces is 0 and the number is whole, else a Double; Null for an empty field. Access writes the field of ControlSource into it on every record change; code may set it. Neither marks the record as changed. A value that is no number raises wkeInvalidValue.

A value set by code or by Access is shown as it is, also outside the limits: the limits check what the user enters, as the validation rule of an Access field does not check data that is already stored.

Read/write.

None

Declared by WklSpinEdit.

Property Get Caption() As String
Property Let Caption(RHS As String)

Gets or sets the name screen readers hear for the field.

None

Property value: String – The name; empty (default) uses the control's name.

Read/write.

None

Declared by WklSpinEdit.

Property Get MinValue() As Variant
Property Let MinValue(RHS As Variant)

Gets or sets the smallest value the user can enter.

None

Property value: Variant – A number, or Null (default) for no lower limit. A limit above MaxValue raises wkeInvalidValue.

Read/write.

None

Declared by WklSpinEdit.

Property Get MaxValue() As Variant
Property Let MaxValue(RHS As Variant)

Gets or sets the largest value the user can enter.

None

Property value: Variant – A number, or Null (default) for no upper limit. A limit below MinValue raises wkeInvalidValue.

Read/write.

None

Declared by WklSpinEdit.

Property Get Step() As Double
Property Let Step(RHS As Double)

Gets or sets the change of one press of an arrow or Up/Down.

None

Property value: Double – A number greater than 0; default 1. Another number raises wkeInvalidValue.

A value off the step grid moves to the next grid value in the direction of the press (2.5 with step 1 goes up to 3 and down to 2); the grid starts at MinValue, else at 0. From an empty field the first press shows the start value: MinValue when it is above 0, else 0.

Read/write.

None

Declared by WklSpinEdit.

Property Get DecimalPlaces() As Long
Property Let DecimalPlaces(RHS As Long)

Gets or sets the decimal places a value is rounded to and shown with.

None

Property value: Long – 0 (default) to 10. Another number raises wkeInvalidValue.

Values are rounded half away from zero (0.25 with one decimal place is 0.3). The number is shown in the user's regional format without thousands separator; one the user types is accepted.

Read/write.

None

Declared by WklSpinEdit.

Property Get Unit() As String
Property Let Unit(RHS As String)

Gets or sets the unit shown after the number.

None

Property value: String – A text, for example “Tage”; empty (default) for none.

Display only: the field keeps the number in that unit. The user may type the number with or without the unit.

Read/write.

None

Declared by WklSpinEdit.

Property Get BackColor() As OLE_COLOR
Property Let BackColor(RHS As OLE_COLOR)

Gets or sets the background color of the text.

None

Property value: OLE_COLOR – An OLE color; default the window background of Windows.

Read/write.

None

Declared by WklSpinEdit.

Property Get ForeColor() As OLE_COLOR
Property Let ForeColor(RHS As OLE_COLOR)

Gets or sets the color of the text.

None

Property value: OLE_COLOR – An OLE color; default the window text of Windows.

Read/write.

None

Declared by WklSpinEdit.

Property Get FontName() As String
Property Let FontName(RHS As String)

Gets or sets the font of the text.

None

Property value: String – The font name; default Segoe UI. An empty name raises wkeInvalidValue.

Read/write.

None

Declared by WklSpinEdit.

Property Get FontSize() As Long
Property Let FontSize(RHS As Long)

Gets or sets the size of the font.

None

Property value: Long – Points, 6 to 72; default 9. Another size raises wkeInvalidValue.

Read/write.

None

Declared by WklSpinEdit.

Property Get Text() As String

Returns the text shown - number and unit, or what the user is typing.

None

Property value: String – The text; an empty text for Null.

Read-only.

None

Declared by WklSpinEdit.

Property Get ColorScheme() As WklColorScheme
Property Let ColorScheme(RHS As WklColorScheme)

Gets or sets the color scheme the control draws in.

None

Property value: `WklColorScheme` – wcoAutomatic (default), wcoLight, wcoDark or wcoHighContrast.

wcoAutomatic follows, in this order, Windows high contrast mode, then the color set of the database design (Access: Design › Themes or Colors), and draws light when the database has no design or the control is on an Excel or Word UserForm. Office's own design setting and the Windows app mode are not followed: a database looks the same on every PC. In high contrast mode the control always uses the system colors. The scheme colors background and text (while BackColor and ForeColor keep the system defaults), the frame and the arrow buttons; a BackColor you set wins over the scheme. Every text keeps a contrast of at least 4.5:1. The control reads the database design after it is first drawn, never while Access draws the form, and again when the form opens anew. The value is saved with the form.

Read/write.

None

Declared by WklSpinEdit.

Event Change()

Occurs when the user changed the value, after the change was reported to Access.

None

Occurs for an arrow, a key, the mouse wheel and every typed text that is a valid number - not for a value set by code or by Access. While the user types “4000” into a field with MaxValue = 3650, the valid steps 4, 40 and 400 each raise Change; leaving the field then brings back the value from entering it and raises Change once more.

In a form module: Private Sub <ControlName>_Change(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklSpinEdit.

Event InvalidEntry(ByVal Text As String, ByVal Message As String)

Occurs when the user left the field with a text that is no number or lies outside the limits; the field shows the value it had when the user entered it again.

In a form module: Private Sub <ControlName>_InvalidEntry(ByVal Text As String, ByVal Message As String). The control shows no message box: show Message where it suits the form, for example in a label beside the field.

In a form module: Private Sub <ControlName>_InvalidEntry(...). Parameters passed by reference can be changed by the procedure.

None

Declared by WklSpinEdit.