In this tip, you will see how you can display data in a chart as a Speedometer or Thermometer graphic.
Introduction
It is a good idea if you can display your data using chart and as they say, a picture is worth a thousand words.
Background
In some cases, you need to display a data in chart as Speedometer or Thermometer graphic, but limitation of MS Access and unavailability of these controls will need to create custom build chart, by doing this, it will add another WoW factor to your application that builds in Access using VBA.
Using the Code
'Thermometer' chart is very useful in terms of showing percentage of increment of the process from the target that it has been set. The chart provided with this article is even more beneficial because it shows two thermometers beside each other. This will help to display two numbers, data number for example if you have inventory and sales against your target as 100% per period of time.
And for test purposes only, there are two dropdown boxes you can select number and the Thermometer will move up or down. For using the chart in your application, you do not need this dropdown, you need to feed the data from data query that will be used for each thermometer.
Second chart in this article is 'Speed Meter' chart and is used to display value or percentage of score get it from data table representing the value of single record. For example, it could show percentage of salesperson, number of sales against the target and compare score with the average of the sales team.


For test purposes, there is a dropdown box and you can select any number and the speedometer needle will move accordingly.
Note: There are other backgrounds to be used if needed, they are located in GaugeMeter_Background
form. To change the background, it needs to replace Image41
on the form.
Function UpdateSpeedometer(nNeedleCurrPosition As Integer)
Dim nRatio As Double
Dim nRadians As Double
Dim nTop As Double
Dim nLeft As Double
Dim nHeight As Double
Dim nWidth As Double
Const PI As Double = 3.14159265358979
Const nMax As Integer = 1440
Const nRadius As Double = 1 * nMax
Const nNeedleHorizontal As Double = 2 * nMax
Const nNeedleVertical As Double = 2 * nMax
Const nMaxNeedlePosition As Integer = 100
On Error GoTo ErrorHandler
If nNeedleCurrPosition > nMaxNeedlePosition Then Exit Function
nRatio = nNeedleCurrPosition / nMaxNeedlePosition
nRadians = nRatio * 180 * PI / 180
nTop = nNeedleVertical - (Sin(nRadians) * nRadius)
nHeight = nNeedleVertical - nTop
If nRatio < 0.5 Then
Me.linNeedle.LineSlant = False
nLeft = nNeedleHorizontal - (Cos(nRadians) * nRadius)
nWidth = nNeedleHorizontal - nLeft
Else
Me.linNeedle.LineSlant = True
nLeft = nNeedleHorizontal
nWidth = -Cos(nRadians) * nRadius
End If
Me.linNeedle.Top = nTop
Me.linNeedle.Left = nLeft - 100
Me.linNeedle.Height = nHeight
Me.linNeedle.Width = nWidth
Me.lblScore.Caption = nNeedleCurrPosition
Exit Function
ErrorHandler:
MsgBox "There was an error updating the Speedometer! "
Exit Function
End Function
Points of Interest
Knowing and using math functions is the key to utilize this knowledge to accomplish anything you want.
History
- 24th June, 2021: Version 1.0.0