UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Sliding Indicator Controls    
Sliding Indicator Controls

This article describes how you can create one or more custom Sliding Indicator controls on a form or report.

A simple example of this sort of control would be a car's Fuel Gauge: ( E.../.F ) The "E.....F" bit is the Scale, and the needle is the Indicator. If you know the fuel tank is 75% full, you would want a way of moving the Indicator to the 75% spot on the Scale. These sort of controls are very handy for visualizing information.

A Sliding Indicator essentially includes two basic elements:

  • A Scale, resembling a ruler, possibly with custom markings or colors.
  • An Indicator, resembling a pointer of some sort, which shows where, on the Scale, a value falls.

There is no built-in Sliding Indicator control in Access, so you will need to create one. You can do this with two controls (one for the Scale and one for the Indicator), and some VBA to handle the re-positioning of the indicator.

First, create your Scale control. You can use a label control with text, or a graphic image, but the important bit is that it is a a single control where the left edge represents the minimum value and the right edge represents the maximum value (or vice versa). If you want to "dress up" your scale with extra bits like text boxes or lines, feel free to do so, but you must have at least one control that is as wide as the area the indicator will be moving within. This control can even be invisible, if necessary.

Next, create your Indicator control. Again, this can be a label (perhaps a capital "V" as an arrow), or a line control, or a graphic image. The important bits about this control are:

  • The horizontal center of the control is the spot where you want the "pointer" to be pointing from.
  • The Indicator should have enough room to actually indicate the minimum and maximum values on the scale.

Position the Indicator above, below or directly on top of the scale, as you prefer. If there's any overlap, be careful that the Indicator control does not get completely hidden by Scale control.

Once your two controls are in place, you'll need to use some VBA to manipulate the Indicator control. If your indicator is going to be based on data that is entered in a textbox for example, you would use that textbox's After Update event to trigger the movement of the Indicator control. Also, you'll want to call this same event from the form's On Current event if the user can browse through records; that way the Indicator will be updated each time a new record is displayed. If your sliding indicator controls are on a report, you would just use the On Format event for the section where the controls are located.

Below is a simple User Defined Function you can use. Create a new Code Module and copy/paste this code into the module, then save the module with a name like "modSliderControl" - anything besides "SliderPosition," which is the name of the function.

Option Compare Database
Option Explicit

'   Code courtesy of UtterAccess Wiki
'   http://www.utteraccess.com/wiki/index.php/Sliding_Indicator_Controls
'   Original submission by Dennis Kuhn (aka doctor9)
'   on 8/28/12

Public Function IndicatorPosition(varValue As Variant, _
                                 ctlScale As Control, _
                                 ctlIndicator As Control, _
                                 varScaleL As Variant, _
                                 varScaleR As Variant, _
                                 Optional boolVertical As Boolean = False) As Long

'   varValue:      The value that the indicator is indicating.  This value
'                  determines where the Indicator should move to.

'   ctlScale:      The control representing the "Scale" that the indicator slides
'                  along.  For example, if your data range goes from -5 to 5,
'                  ctlScale would be a control on the form where it's left edge
'                  represents -5 and it's right edge represents 5.

'   ctlIndicator:  The control representing the "indicator" that points at the
'                  Scale, indicating a value.

'   varScaleL:     The value on the "Scale" control, found at it's
'                  leftmost edge.

'   varScaleR:     The value on the "Scale", found at it's rightmost edge.

'   boolVertical:  If TRUE, the Indicator control needs to be moved vertically on
'                  the form.  If FALSE, the Indicator control needs to be moved
'                  horizontally on the form (default).

'   Example syntax:
'       Me.lblMarker.Left = IndicatorPosition(Me.txtTestResult, _
                                       Me.imgColorBar, Me.lblMarker, 0, 14)

   Dim lngScaleLeft As Long, lngScaleWidth As Long, lngIndicatorWidth As Long

   If IsNull(varValue) Then
'       The value is Null, so hide the indicator completely
       ctlIndicator.Visible = False
'       Non-Null value - figure out where to move the indicator
       ctlIndicator.Visible = True
       If boolVertical = False Then
           lngScaleLeft = ctlScale.Left
           lngScaleWidth = ctlScale.Width
           lngIndicatorWidth = ctlIndicator.Width
           lngScaleLeft = ctlScale.Top
           lngScaleWidth = ctlScale.Height
           lngIndicatorWidth = ctlIndicator.Height
       End If

'       The Indicator position is based on:
'       * The horizontal position and width of the Indicator
'       * The width of the Indicator (we want the horizontal center of the
'         Indicator to be the point indicated).
'       * The value to be indicated vs. the min/max values represented by the Scale.

'       We assume the Indicator control is either a vertical line, or a centered
'       letter "V" - something where the horizontal center of the control is the
'       spot we want to line up with the appropriate value on the Scale.
'       The Abs(varScaleR - varScaleL) is how we allow for a scale that does
'       NOT start at zero.  For example, a scale from -5 to 5 will have a width
'       of 10 units.

       If varScaleL < varScaleR Then
'           Lower values on the left/top, higher values on the right/bottom:
'           Measure from the left side of the scale the number of units, minus
'           half the width of the indicator control.
           IndicatorPosition = CLng((lngScaleLeft - lngIndicatorWidth / 2) + _
                       ((varValue / Abs(varScaleR - varScaleL)) * lngScaleWidth))
'           Higher values on the left/top, lower values on the right/bottom:
'           Measure from the right side of the scale the number of units, plus
'           half the width of the indicator control.
           IndicatorPosition = ((lngScaleLeft + lngScaleWidth) - _
               lngIndicatorWidth / 2) - _
               ((varValue / Abs(varScaleR - varScaleL)) * lngScaleWidth)
       End If
'       Error Trap:
'       If the indicator is in danger of going off of the form (which would cause an
'       error), make the indicator act like an analog fuel gauge: stop at the max
'       or min position.
       If IndicatorPosition < lngScaleLeft - lngIndicatorWidth / 2 Then
           MsgBox "Error.  The sliding indicator value is less than the minimum."
           IndicatorPosition = lngScaleLeft - lngIndicatorWidth / 2
'           If the Scale is too far left on the form, put the indicator as far left
'           as possible.
           If IndicatorPosition < 0 Then IndicatorPosition = 0
           If IndicatorPosition > _
                       lngScaleLeft + lngScaleWidth - lngIndicatorWidth / 2 Then
               MsgBox "Error.  " & _
                 "The sliding indicator value is greater than the maximum."
               IndicatorPosition = _
                           lngScaleLeft + lngScaleWidth - lngIndicatorWidth / 2
'               If the Scale is too far right on the form, put the indicator as far
'               right as possible.
               If boolVertical = False Then
                   If IndicatorPosition > _
                                   ctlScale.Parent.Width - lngIndicatorWidth Then
                       IndicatorPosition = ctlScale.Parent.Width - lngIndicatorWidth
                   End If
                   If IndicatorPosition > _
                                   ctlScale.Parent.Height - lngIndicatorWidth Then
                       IndicatorPosition = ctlScale.Parent.Height - lngIndicatorWidth
                   End If
               End If
           End If
       End If
   End If

End Function

As the internal comments explain, you call the function with code along these lines:

Me.lblMarker.Left = IndicatorPosition(Me.txtTestResult, Me.imgColorBar, Me.lblMarker, 0, 14)

In this example, "Me.imgColorBar" is a reference to the Scale control. Note the "Me." prefix and lack of quotation marks - this is not just the control's name, but a reference to the control itself, so the function can easily get properties from this control. The same goes for "Me.lblMarker". The zero is the scale's value on the left edge, the 14 is the scale's value on the right edge, and finally "Me.txtTestResult" is a reference to the value in a textbox on the form. This can obviously be replaced with a hard-coded value, or a formula. You can pass numbers of any datatype in this argument; Single, Double, Integer or Long. The function returns a Long Integer value that should be assigned to the .Left property of your Indicator control.

The code includes a couple of lines that will cause the Indicator control to be Invisible if a Null value is passed as the final argument. This can be changed if your particular needs require the indicator to be displaying the Minimum or Maximum value even when no value exists for it.

The last argument for the function, the optional "boolVertical" can be ignored if your scale moves from left to right (or vice versa). However, if your scale is vertically oriented instead, simply include the value TRUE as this argument. For example:

Me.lblMarker.Top = IndicatorPosition(Me.txtTestResult, Me.imgColorBar, Me.lblMarker, 0, 14, True)

You can download this simple demo file (In Access 2010 format) to see various types of Sliding Indicators on both a form and a report. Media:SlidingIndicatorDemo.zip

Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 14,297 times.  This page was last modified 13:48, 30 August 2012 by doctor9. Contributions by Mark Davis  Disclaimers