|
|
|
Visualize Yes/No data with something better than a checkbox. Use the VBA CIRCLE method to draw circles in the Detail section of an Access report, so the drawing changes according to the data on each record. A simple circle can do a lot of different things, and this is just the tip of the iceburg!
The download ACCDB database has a query and report to draw circle examples for YesNo data, as shown above. The data comes from a table of numbers and is created with expressions.
If you explore, you'll also find the beginning of a clock ... the face without the mechanics. This is drawn by code in a standard module just using the Circle method. All you have to do is call it, and send it your control so the code knows how big and where to put the clock (the easy but not most efficient way). Down the road, Line can be used to draw the hands, and then the clock can be more useful.
| File name | Description and Link |
|---|---|
| ReportDraw_CIRCLE_YesNo_s4p__ACCDB.zip | Accdb in a Zip file
with VBA to draw each of the examples using Circle
on an Access report.
download ACCDB |
| r_CIRCLE_1_YesNo.pdf | Access report as a PDF showing circles
for each record of a report.
r_CIRCLE_1_YesNo.pdf |
This report shows various ways to indicate True or False using a circle. Some of these examples aren't practical, but are shown to share different uses of Circle with you. Ideally, the code to call 'standard' drawings would be in a standard module, and we'll get there ... . This example has all the code behind the report so it's easier to learn.
... and if you really don't want a circle at all, but a bigger checkbox, this report does that too. See the big orange or yellow textbox that looks like a checkbox next to the tiny standard checkbox on the left? The ControlSource is an expression specifying a Unicode character using the ChrW function. It obviously doesn't use the Circle method, and is easily scaled by changing the Font Size.
=IIf( [ValueYesNo] <> False, ChrW(9745), ChrW(9744) )
oh, and because it's a textbox, it can have Conditional Formatting. Here, False has an orange background, and True is colored yellow. Or the background color can be set to something specific, or changed with VBA. The font color can also be specified.
Here is the design view of the report. Label controls are used to define where circles will be drawn. They aren't necessary but its easier to visualize where the circles will be. Change the size and position of each drawing by changing each respective control.
Draw circle, ellipse, or arc
Report.Circle Step (x,y), Radius, Color, StartAngle, EndAngle, Aspect
As a preamble to the video that covers drawing the circle diagrams shown on this page (in production), I made a quick video about using a big checkbox that can be colored and sized instead of the built-in checkbox control. This is great when all you want is something bigger or to specify other formatting like background or font color.
Displaying bigger checkboxes is even easier on a report! The ControlSource can be simply set to the expression that results in what you want to show based on data.
Watch on YouTube: Big Checkbox in Access with Conditional Formatting (2:18)
Since colors are used on every record, their Long Integer RGB values are defined in the ReportHeader_Format event. Circles are drawn (or not) on each record using the Detail_Print event.
Option Compare Database Option Explicit ' Draw Circles to show various methods to indicate True or False values '*************** Code Start ***************************************************** ' code behind report: r_CIRCLE_1_YesNo ' Report Draw Reference: ' https://msaccessgurus.com/VBA/ReportDraw_Reference.htm ' VBA and download with this example: ' https://msaccessgurus.com/VBA/Draw_Circle_1_YesNo.htm '------------------------------------------------------------------------------- ' Purpose : draw using the Circle method ' change center, radius, ' FillStyle, FillColor, and specified Color ' Author : crystal (strive4peace) ' Code List: www.msaccessgurus.com/code.htm '------------------------------------------------------------------------------- ' LICENSE ' You may freely use and share this code, but not sell it. ' Keep attribution. Use at your own risk. '------------------------------------------------------------------------------- '------------------------------------------------------------------------------- ' Module variables for Color, define PI '------------------------------------------------------------------------------- Private mnColorRed As Long _ ,mnColorBlueMedium As Long _ ,mnColorYellow As Long _ ,mnColorBlack As Long _ ,mnColorGray As Long Private Const PI = 3.14159 'usually this would be global '------------------------------------------------------------------------------- ' ReportHeader_Format '------------------------------------------------------------------------------- Private Sub ReportHeader_Format(Cancel As Integer,FormatCount As Integer) 'define colors mnColorRed = RGB(255,0,0) mnColorBlueMedium = RGB(0,100,255) mnColorYellow = RGB(255,255,0) mnColorBlack = 0 mnColorGray = RGB(200,200,200) End Sub '------------------------------------------------------------------------------- ' Detail_Print '------------------------------------------------------------------------------- Private Sub Detail_Print(Cancel As Integer,PrintCount As Integer) 'draw circles on each record depending if the value is True or False '220628 strive4peace Dim iValue As Integer _ ,xCenter As Single _ ,yCenter As Single _ ,sgRadius As Single Dim xLeft As Single _ ,xWidth As Single _ ,yTop As Single _ ,yHeight As Single _ ,nColor As Long _ ,i As Integer ' iValue is ValueYesNo ' (xCenter, yCenter) is the circle center coordinate ' sgRadius is the circle radius ' use Label controls for boundaries With Me '----------- set up drawing space .ScaleMode = 1 'twips, default .DrawWidth = 1 'pixel 'it circle is filled, color it black 'get Value iValue = Nz(.ValueYesNo,0)
| True= |  | False= | Nothing |
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ShowOrNot
'----------- Label_ShowOrNot
If iValue <> 0 Then
'if value is True then draw a circle
' in the middle of the
' Label_ShowOrNot control
With .Label_ShowOrNot
'--- radius of circle is limited by width or height
If .Width > .Height Then
sgRadius = .Height / 2
Else
sgRadius = .Width / 2
End If
'--- center coordinate for the circle
xCenter = .Left + .Width / 2
yCenter = .Top + .Height / 2
End With
'filled circle
.FillStyle = 0 'opaque
'fill color is blue
.FillColor = mnColorBlueMedium
'----- draw a circle
' middle is (xCenter, yCenter)
' radius is sgRadius
' outline color is blue
Me.Circle (xCenter,yCenter),sgRadius,mnColorBlueMedium
End If
| True= |  | False= |  |
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FilledOpen '----------- Label_FilledOpen With .Label_FilledOpen 'draw an open or closed circle in the middle of ' the Label_FilledOpen control '--- radius of circle is limited by width or height If .Width > .Height Then sgRadius = .Height / 2 Else sgRadius = .Width / 2 End If '--- center coordinate for the circle xCenter = .Left + .Width / 2 yCenter = .Top + .Height / 2 End With If iValue <> 0 Then 'if value is True then filled red circle .FillStyle = 0 'opaque .FillColor = mnColorRed Else 'if the value is False, then an open circle .FillStyle = 1 'transparent End If '----- draw a circle ' middle is (xCenter, yCenter) ' radius is sgRadius ' outline color is red Me.Circle (xCenter,yCenter),sgRadius,mnColorRed
| True= |  | False= |  |
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ OnOff ' height is split in 2 circles '----------- Label_FilledOpen With .Label_OnOff '--- radius of circle is limited by width or half height If .Width > .Height / 2 Then sgRadius = .Height / 4 Else sgRadius = .Width / 2 End If 'horizontal center is the same for both xCenter = .Left + .Width / 2 'yCenter will be calculated yTop = .Top yHeight = .Height End With .FillStyle = 0 'opaque '----- Top and Bottom Circles For i = 0 To 1 'Top then bottom 'Circle vertical center yCenter = yTop + yHeight / 4 _ + i * sgRadius * 2 'top circle is ON 'bottom circle is OFF If iValue <> 0 And i = 0 Then 'True is filled black circle nColor = mnColorBlack Else nColor = mnColorGray End If '----- draw a circle ' middle is (xCenter, yCenter) ' radius is sgRadius ' outline color is nColor .FillColor = nColor Me.Circle (xCenter,yCenter),sgRadius,nColor Next i
| True= |  | False= |  |
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Switch ' width is split in 2 circles '----------- Label_Switch With .Label_Switch '--- radius of circle is limited by height or half width If .Width / 2 > .Height Then sgRadius = .Height / 2 Else sgRadius = .Width / 4 End If 'vertical center is the same for both yCenter = .Top + .Height / 2 'xCenter will be calculated xLeft = .Left xWidth = .Width End With '----- Left and Right Circles For i = 0 To 1 'Left then right 'Circle horizontal center xCenter = xLeft + xWidth / 4 _ + i * sgRadius * 2 'left circle is OFF 'right circle is ON 'if drawing left circle, color is gray 'if drawing right circle, color is black If i = 0 Then nColor = mnColorGray Else nColor = mnColorBlack End If ' if value is false and on left then fill ' if value is true and on right, fill If iValue = 0 And i = 0 _ Or iValue <> 0 And i = 1 _ Then .FillStyle = 0 'opaque Else .FillStyle = 1 'transparent End If '----- draw a circle ' middle is (xCenter, yCenter) ' radius is sgRadius ' outline color is nColor .FillColor = nColor Me.Circle (xCenter,yCenter),sgRadius,nColor Next i
| True= |  | False= |  |
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TopBottom '----------- Label_TopBottom With .Label_TopBottom '--- radius of circle is limited by height or width If .Width > .Height Then sgRadius = .Height / 2 Else sgRadius = .Width / 2 End If 'center is the same for both xCenter = .Left + .Width / 2 yCenter = .Top + .Height / 2 End With .FillStyle = 0 'opaque 'if filled, it will be yellow .FillColor = mnColorYellow '----- Top and Bottom Half Filled Circles '----- draw a circle ' middle is (xCenter, yCenter) ' radius is sgRadius ' outline color is Blue If iValue <> 0 Then 'top half is True ' angle is 0 to PI ' negative indicates fill, the angle is positive ' instead of 0, use very small number Me.Circle (xCenter,yCenter),sgRadius,mnColorBlueMedium _ ,-0.000000001,-PI Else 'bottom half is False ' angle is PI to 2*PI Me.Circle (xCenter,yCenter),sgRadius,mnColorBlueMedium _ ,PI,2 * PI End If
| True= |  | False= |  |
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LeftRight '----------- Label_LeftRight With .Label_LeftRight '--- radius of circle is limited by height or width If .Width > .Height Then sgRadius = .Height / 2 Else sgRadius = .Width / 2 End If 'center is the same for both xCenter = .Left + .Width / 2 yCenter = .Top + .Height / 2 End With .FillStyle = 0 'opaque .FillColor = mnColorBlack '----- Left and Right Half Filled Circles '----- draw a circle ' middle is (xCenter, yCenter) ' radius is sgRadius ' outline color is Blue ' angle is negative to indicate fill If iValue <> 0 Then 'right is true 'angle2 has to be >= angle1 'and circle can't go past 360° or 2 Pi radians 'so it is drawn in 2 parts Me.Circle (xCenter,yCenter),sgRadius,mnColorBlack _ ,-0.0000001,-PI / 2 Me.Circle (xCenter,yCenter),sgRadius,mnColorBlack _ ,-3 / 2 * PI,-2 * PI Else 'left is false Me.Circle (xCenter,yCenter),sgRadius,mnColorBlack _ ,-PI / 2,-3 / 2 * PI End If ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ End With 'Me End Sub '*************** Code End *******************************************************
Report Draw Reference for VBA syntax and help for drawing on Access reports.
True or False is easy -- just one or another. There is a lot you can do with simple circles though. These are ideas to get you started.
Before we can go to more complex drawings, the Line method needs to be explored. And then drawings can have hands on a clock, needles, section lines, and other visual indicators that Circle can't do alone.
If you like this basic circle page, please express your appreciation, thank you.
here's the link for this page in case you want to copy it:
https://msaccessgurus.com/VBA/Draw_Circle_1_YesNo.htm
Let's connect and team-develop your application together. I teach you how to do it yourself. As needed, while we build something great together, I'll pull in code and features from my vast libraries, cutting out lots of development time.
Do you want to incorporate more drawings on your reports?
Do you want some ideas?
For training and programming,
email me at training@msAccessGurus
Donations are always welcome
~ crystal
the simplest way is best, but usually the hardest to see