August 2007 ~ LEARN MS-ACCESS TIPS AND TRICKS

MS-Access and Graph Charts

a.p.r. pillaiAugust 29, 2007Graph Charts, msaccess graphs 11 comments

Introduction

Access Reports are a powerful tool for presenting information in both numbers, and text. However, Graph Charts take it a step further by conveying data visually, often revealing insights at a glance. With a basic understanding of how charts work, you can easily create them in Access. The key principle is to illustrate the progression or change of an event over time using dots and lines that reflect corresponding values, instead of relying solely on raw numbers or text.

Before creating graphs in MS Access, it's essential to understand how to structure your data appropriately. To get familiar with this process, we'll explore a few examples that use a single set of values and demonstrate the required data format for generating meaningful graphs.

Events such as daily temperature changes, fluctuations in a patient's body temperature (for health monitoring in hospitals), or variations in gold prices over time can all be effectively visualized using line or bar charts. Traditionally, these are plotted manually on graph paper by marking each data point according to a fixed scale, starting from zero at the bottom of the Y-axis (vertical axis), which is calibrated based on the highest expected value.

Each day's data point is plotted as a dot along the Y-axis, aligned with the corresponding date on the X-axis (horizontal axis). These dots can be connected with lines to show trends, and the actual values can be labeled near each dot. A legend is typically included to describe what the graph represents (e.g., Temperature).

Instead of using paper and pencil, we can achieve the same results—more efficiently and professionally—using the Microsoft Graph Chart object in MS Access.

Preparing for a simple Chart.

We plan to monitor the daily Stock Index, and the values are recorded in an Access Table as given below.

Desc	Date	Val
Stock Index	01-08-07	1245
Stock Index	02-08-07	1455
Stock Index	03-08-07	1395
Stock Index	04-08-07	1575
Stock Index	05-08-07	1125

Desc	01-08-07	02-08-07	03-08-07	04-08-07	05-08-07
Stock Index	1245	1455	1395	1575	1125

Click to Enlarge

When the above data is plotted in the form of Charts, the contour of the Charts shows the trend of the stock index at a glance rather than reading the numbers and comparing them.

Before preparing the data for the Chart, we must decide what Type of Chart is best suited for the data.

For example, if we want to present a chart showing the company's total revenue from various sources—such as Vehicle Sales, Parts Sales, and Service Sales—and highlight each category's contribution to the overall total, a Pie Chart would be the ideal choice.

However, if we want to display the monthly performance of each category separately over time, a Line Chart or Bar Chart would be more appropriate, as they are better suited for showing trends and comparisons across periods.

If the source data for a chart changes at regular intervals—such as every month—then the underlying Table or Query should be designed to automatically reflect the updated data. This ensures that the chart dynamically displays the latest information without requiring manual modifications each time the data changes.

Charts in Excel and MS-Access.

Creating charts in Access isn't as straightforward as it is in Excel. In this section, we’ll explore the most commonly used chart types in Access: Line, Bar, and Pie charts. If you're familiar with Excel charts, you might initially find Access’s data preparation methods a bit challenging. However, once you get past the learning curve, you will find Access charts just as powerful—and might not feel the need to return to Excel at all.

However, unlike Excel, Access offers the advantage of using various types of Queries and Macros for automating the chart data preparation process. This allows us to extract and present key figures from large volumes of data quickly and efficiently, regardless of the chart type. Once the chart setup is complete, it can run consistently without requiring manual intervention.

Note: You can enhance charting capabilities in Access by attaching the Microsoft Excel Object Library to the Access database's list of References. This enables the use of Excel’s rich variety of chart types and advanced formatting options directly within Access.

Line and Bar Charts.

The Sample Source Data presented in the first format above must be changed into the 2nd format to create both Line and Bar Charts. In Excel, both formats are valid. With the help of a Cross-Tab Query, we can change the data format for our Chart.

Create a Table with the Field Names Desc, Date, and Val, and save it with the name Stock1.
Key in the six records from the sample data above, as we normally maintain data in Access Tables.
Display the SQL Window of a new Query (don't select a table or Query from the list displayed), copy and paste the SQL String of a Cross-Tab Query given below into the SQL Window, and save it with the name BarChartQ
.
```
TRANSFORM Sum(Stock1.Val) AS SumOfVal
SELECT Stock1.Desc
FROM Stock1
GROUP BY Stock1.Desc
PIVOT Stock1.Date;
```
Open a new Report in the design view. Select Object. . ., from the Insert Menu (Chart Option on the View Menu uses only six Fields for a Chart), select Microsoft Graph Chart, and click OK. A Chart Object with default values is inserted into the Report.
Click outside the chart on the report to de-select the chart from Edit mode. Click again on the chart to select it, display the property sheet, and change the following values:
- Size Mode = Zoom.
- Row Source Type = Table/Query
- Row Source = BarChartQ
- Column Heads = Yes
- Left = 0.3"
- Top = 0.3"
- Width = 6.0"
- Height = 4.0"
Double-click on the Chart. The Chart Formatting Toolbar will appear at the top. Select Column Chart from the Chart Type Toolbar Options, or Right-Click on an empty area within the Chart and select Chart Type from the Shortcut Menu, and select Column Chart.

Click to Enlarge
In the design view, the Chart object displays a chart with sample data in an Excel-like data Sheet (if not visible, double-click on the chart). But, when the Report is print-previewed, the Chart will appear with the actual data from the Table/Query we have attached as the Source. To see the result of the Source Data in Design View, also copy and paste the Source Data into the Excel-like Cells.

Minimize the Report that we are working on. Click on the Query Tab. Double-Click on the BarChartQ to open it. Click at the left top corner of the Datasheet display to select full data. Click the Copy Toolbar Button or select Copy from Edit Menu. Restore the Report with the Chart and double-click on the Chart to display the Data Grid. Click on the left top corner of the Grid to select all the cells. Click the Paste button on the Toolbar or select Paste from Edit Menu. Click on the left border of the extra two lines below and select cut from Edit Menu to remove the unwanted data. Now the Chart in the Design view displays the actual chart values.
Chart Formatting for Visually Pleasing Look
We will format the Chart to give it a better look.
If the Chart is still in the same stage when you have pasted the Chart Values in the Grid (otherwise double-click on it to modify the Chart Options) right-click at the center of the Graph where the shaded area is and away from the Chart Bars. The Plot Area is highlighted; select Format Plot Area . . ., from the Shortcut Menu. Put a check-mark on the None option under Area Options and click OK to close the options menu.
Right-click on one of the Bars and select Format Data Series . . ., Click on the Fill Effect Button and select Gradient Tab.
Click Vertical under Shading Styles and Click on the Right Bottom Style, out of four Styles displayed. If you want to change the Color of the Bars you can select the color of your choice from the color palette. Accept the default One Color Option without change.
Select the Data Labels Tab. Put a check-mark in the Value Option and click OK to update the change on the Graph.
If the Data Label Font is large then right-click on one of the labels and select Font, Font Style, Size, etc., from the Menu, and change the values to your liking (say 10 points). Repeat the same change in X-Axis Labels, Y-Axis Labels, and Legend.
Right-Click on the Chart outside the Plot Area and select Chart Options.
Type NYSE Index in the Chart Title Control.
Save the Report with the Chart and open it in Print Preview.

Click to Enlarge
Add two more records to the Chart's Table Stock1. Print Preview the Report with the Chart again and check whether the newly added records are appearing as new Bars on the Chart.

You can create a Line-Chart from the same Bar Chart. Click on the Chart in Design View, Copy and Paste another instance of the same Chart below the Bar Chart. Double-click on the new Chart to Edit the Chart Options. Right-click on an empty area of the Chart away from the plot area, click on the Chart Type, and select Line Chart.

Reports - Page Border

a.p.r. pillaiAugust 23, 2007msaccess reports, Reports 3 comments

Drawing a Page Border in MS Access.

Drawing a page border in Microsoft Word is a simple task—it’s available as part of the built-in Formatting Options, and you can even choose from a variety of artistic styles.

However, when it comes to Microsoft Access, there are no direct formatting tools for adding a decorative or functional border around a printed Report Page. Instead, we must rely on VBA code to draw the page border programmatically at runtime, just before the report is printed.

The example image below illustrates a report with a custom-drawn page border created using this method.

Click to Enlarge

The Catalog Report shown above was imported from the Northwind.mdb database (refer to the article Saving Data on Forms Not in Tables for database location details). Originally, this report did not include a page border. We enhanced it by drawing a double-lined page border using the VBA code provided below.

If you'd like to try this yourself, import both the Categories table and the Catalog Report from the Northwind.mdb database into your project. The code can be used on any report to draw a page border, and it automatically aligns the border within the page’s margin settings.

The VBA Code.

Public Function PageBorder(ByVal strReportName As String)
Dim Rpt As Report, lngColor As Long
Dim sngTop As Single, sngLeft As Single
Dim sngwidth As Single, sngheight As Single

On Error GoTo PageBorder_Err

'DRAW DOUBLE LINED BORDER
Set Rpt = Reports(strReportName)
'Set scale to pixels
Rpt.ScaleMode = 3 
'Top inside edge
sngTop = Rpt.ScaleTop
'Left inside edge
sngLeft = Rpt.ScaleLeft
'Width inside edge
sngwidth = Rpt.ScaleWidth
'Height inside edge
sngheight = Rpt.ScaleHeight
'color value
lngColor = RGB(0, 0, 255)
'Draw page Border
Rpt.Line (sngTop, sngLeft)-(sngwidth, sngheight), lngColor, B

sngTop = Rpt.ScaleTop + 10
sngLeft = Rpt.ScaleLeft + 10
sngwidth = Rpt.ScaleWidth - 10
sngheight = Rpt.ScaleHeight - 10

Rpt.Line (sngTop, sngLeft)-(sngwidth, sngheight), lngColor, B

PageBorder_Exit:
Exit Function

PageBorder_Err:
MsgBox Err.Description, , "PageBorder"
Resume PageBorder_Exit
End Function

Copy and paste the above Code into a Global Module and save it. Copy the following lines into the VBA Module of the Report that you wish to draw the page border:

Private Sub Report_Page()
    PageBorder "Catalog"
End Sub

Replace the name "Catalog" with the name of your Report. Print Preview or Print the Report.

Draw a Box Around Tabular Report Fields.

Using the same technique employed to draw a page border, we can also draw boxes around tabular field arrangements in the Detail section of a report, without enabling the borders of individual controls. This allows for a cleaner and more flexible design.

You can see this method in action on the report we created in the previous post: Highlighting Reports. The example below demonstrates how the code draws precise rectangular outlines around each row of data during print preview or printing, enhancing the report’s readability and structure.

The DrawBox() Function.

A separate Function with the name DrawBox() is created, with the same Code without the code for the second box. Copy and paste the following Code into a Global Module and save it:

Public Function DrawBox(ByVal strName As String)
Dim Rpt As Report, lngColor As Long
Dim sngTop As Single, sngLeft As Single
Dim sngwidth As Single, sngheight As Single

On Error GoTo DrawBox_Err

    Set Rpt = Reports(strName)    
' Set scale to pixels.    
Rpt.ScaleMode = 3    
' Top inside edge.    
sngTop = Rpt.ScaleTop ' + 5    
' Left inside edge.    
sngLeft = Rpt.ScaleLeft ' + 5    
' Width inside edge.

sngwidth = Rpt.ScaleWidth ' - 10    
' Height inside edge.    
sngheight = Rpt.ScaleHeight '- 10    
' Make color red.    
lngColor = RGB(255, 0, 0)    
' Draw line as a box.    
Rpt.Line (sngTop, sngLeft)-(sngwidth, sngheight), lngColor, B

DrawBox_Exit:
Exit Function

DrawBox_Err:
MsgBox Err.Description, , "DrawBox"
Resume DrawBox_Exit
End Function

Copy and paste the following code into the Report's VBA Module and change the Report Name in quotes to replace it with the Report's Name, to run the code in Detail_Print() Event Procedure:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
        DrawBox "SalesTarget2"
End Sub

When the built-in Report Wizard prepares a Report, it creates a nice Page Footer (a sample image is provided below) with the system date, number of Pages, and other details.

The Page Footer Section.

However, most of the time, the Detail Section needs to undergo changes, and the Page Footer must also be updated. When a Report is designed manually without the help of Wizards, then the Page Footer decoration must be hand-drawn. To make things a little easier, I have written two Functions, and the Code is given below:

Public Function DrawPageFooter(ByVal strName As String)
'--------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 22/08/2007
'Remarks: Draws Report Page Footer
'--------------------------------------------------------------
Dim lngWidth As Long, ctrwidth As Long, ctrlCount As Long
Dim j As Long, obj As Report, cdb As Database
Dim lngleft As Long, lngtop As Long, LineCtrl As Control, ctrl As Control
Dim rptSection As Section, leftmost As Long, lngheight As Long
Dim rightmost As Long, RightIndx As Integer
'Note : The Controls appearing in Detail Section from left to Right
'       are not indexed 0 to nn in the order of placing,
'       instead 1st control placed in the Section has index value 0
'       irrespective of its current position.

'On Error GoTo DrawPageFooter_Err

Set cdb = CurrentDb
Set obj = Reports(strName)
Set rptSection = obj.Section(acDetail)

ctrlCount = rptSection.Controls.Count - 1

lngleft = rptSection.Controls(0).Leftrightmost = rptSection.Controls(0).Left
'indexed 0 control may not be the leftmost control on the Form/Report
'so find the leftmost control's left value
For j = 0 To ctrlCount 
    leftmost = rptSection.Controls(j).Left

    If leftmost < lngleft Then
        lngleft = leftmost
    End If
 If leftmost > rightmost Then
   rightmost = leftmost
   RightIndx = j
 End If
Next

lngtop = 0.0208 * 1440lng
Width = 0: ctrwidth = 0

   lngWidth = rightmost + rptSection.Controls(RightIndx).Width
   lngWidth = lngWidth - lngleft

  Set LineCtrl = CreateReportControl(strName, acLine, acPageFooter, "", "", lngleft, lngtop, lngWidth, 0)
  Set ctrl = LineCtrl
  LineCtrl.BorderColor = 12632256
  LineCtrl.BorderWidth = 2
  LineCtrl.Name = "ULINE"

lngtop = 0.0418 * 1440
lngleft = LineCtrl.Left
lngWidth = 2 * 1440
lngheight = 0.229 * 1440

'draw Page Number control at the Report footer

Set LineCtrl = CreateReportControl(strName, acTextBox, acPageFooter, "", "", lngleft, lngtop, lngWidth, lngheight)
With LineCtrl
   .ControlSource = "='Page : ' & [page] & ' / ' & [pages]"
   .Name = "PageNo"
   .FontName = "Arial"
   .FontSize = 10
   .FontWeight = 700
   .TextAlign = 1
End With
'draw Date Control at the right edge of the Line Control
'calculate left position of Date control

lngleft = (LineCtrl.Left + ctrl.Width) - lngWidth
Set LineCtrl = CreateReportControl(strName, acTextBox, acPageFooter,  "", "", lngleft, lngtop, lngWidth, lngheight)
With LineCtrl
   .ControlSource = "='Date : ' & Format(Date(),'dd/mm/yyyy')"
   .Name = "Dated"
   .FontName = "Arial"
   .FontSize = 10
   .FontWeight = 700
   .TextAlign = 3
End With

DrawPageFooter_Exit:
Exit Function

DrawPageFooter_Err:
MsgBox Err.Description, , "DrawPageFooter"
Resume DrawPageFooter_Exit
End Function

Copy and paste the above Code into a Global Module and save it. When the Report design is complete, except for the Page Footer part, call this Function (still keeping the Report open in Design View) from the Debug Window (Immediate Window) or run the code from a Button_Click() Event Procedure by giving the Report Name as a Parameter to the function as follows:

DrawPageFooter "ReportName"

The Program will check through the length of the Controls present in the Detail Section of the Report and add up the length of all fields to calculate the total length of the Page Footer. The Program assumes that the Controls are arranged close together without leaving gaps between them.

Resizing Page Footer.

There are times when, even after designing the Page Footer as the final step, we revisit the report for modifications—such as adding or removing fields—which may result in a misaligned or overlapping Page Footer. When this happens, you have two options:

Delete and redraw the Page Footer elements manually, or
Resize and realign them programmatically using the following utility function.

This function streamlines the process of dynamically adjusting the layout, helping you save time while maintaining a consistent and professional appearance across all pages.

Public Function ResizePageFooter(ByVal strName As String)
Dim RWidth As Long, sect As Section, Rpt As Report
Dim ctrlCount As Integer, j As Integer, RW As Long, LW As Long

On Error GoTo ResizePageFooter_Err

Set Rpt = Reports(strName)
Set sect = Rpt.Section(acDetail)ctrlCount = sect.Controls.Count - 1
RWidth = sect.Controls(0).Width + sect.Controls(0).Left
LW = sect.Controls(0).Left
For j = 0 To ctrlCount
With sect.Controls(j)
    RW = .Left + .Width
    If RW > RWidth Then
       RWidth = RW
    End If
    If .Left < LW Then
       LW = .Left
    End If
End With
Next
RWidth = RWidth - LW
Set sect = Rpt.Section(acPageFooter)
With sect 
	.Controls("ULine")
   .Width = RWidth
   .Controls("Dated").Left = RWidth - .Controls("Dated").Width + LW
End With

ResizePageFooter_Exit:
Exit Function

ResizePageFooter_Err:
MsgBox Err.Description, "ResizePageFooter"
Resume ResizePageFooter_Exit
End Function

Copy and paste the code into a Global Module, then save it.
To run the code, follow the same procedure you used earlier when creating the Page Footer.

If you encounter any compilation or runtime errors, it's likely due to missing references.
In that case, visit the post titled Command Button Animation and follow the steps outlined there to link the seven essential library files required for proper execution.
Once the references are set, return to this module and run the program again.

Highlighting Reports

a.p.r. pillaiAugust 16, 2007msaccess reports, Reports No comments

Introduction.

Analyzing data stored in tables and presenting the results through reports is a key function of any Database Management System.

An Access application may contain multiple logically related tables, maintained separately to simplify data management. When it's time to generate reports, these related tables are selected and joined using common fields. The data is then filtered and formatted for output reports. Well-designed, timely reports play a crucial role in effective decision-making.

Designing reports, like form design, is an art in its own right. MS Access provides powerful report design tools that, with a little practice, you can master effectively.

Let’s consider a scenario where we are tasked with creating a report on the company’s Sales Team to review their monthly sales target achievements. Each team member is assigned a mandatory monthly sales target of $100,000. At the end of each month, management requires a performance review report. If any team member falls short of this target, their performance is closely examined to identify and address potential issues in their assigned area.

We have prepared a sample report displaying the Sales Team’s month-end performance figures. Records of those who failed to meet the $100,000 target are visually highlighted with a red circle for easy identification.

The finished Report image is given below:

The tabular report shown above was created using the Report Wizard and is mostly straightforward in its design. However, what makes it stand out is the visual emphasis placed on values falling below the $100,000 target—each such value is marked with a red circle.

This effect is achieved with a simple VBA routine placed in the Detail_Print() event procedure. During the report’s printing or print preview, the routine evaluates each row's sales figure. If a value is found to be less than $100,000, the code dynamically draws a red circle around it, helping reviewers quickly identify underperformers.

Prepare the Report.

To prepare this report, we will need two tables from the Northwind.mdb sample database. If you're unsure of the file's location, please refer to the earlier post titled "Saving Data on Forms Not in Table" for details on where to find the Northwind.mdb file.

Once located, open your current database and import the following two tables from the Northwind.mdb file:

Orders
Order Details

Click on the Query Tab in the Database Window.
Click New to create a new Query and click the Close button on the Show Table dialog control to close it without selecting a Table from the list.
Click on the Toolbar button with the SQL label to display the Query's SQL Window.

Copy and paste the following SQL String into the SQL window and save it with the Query Name: SalesReportQ0.

SELECT Orders.OrderID,
 Orders.EmployeeID,
 [Order Details].UnitPrice,
 [Order Details].Quantity,
 [UnitPrice]*[Quantity] AS Extended_Price
FROM Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID;

Follow Steps 2 and 3 outlined above to create a new query based on the source data from the first query named SalesReportQ0.
1. Open the Query Design window.
2. Add the SalesReportQ0 query as the data source.
3. Switch to SQL View.
4. Copy and paste the SQL string provided below into the SQL window.
5. Save the query with the name: SalesReportQ.
```
SELECT SalesReportQ0.EmployeeID,
 Sum(SalesReportQ0.Extended_Price) AS Total
FROM SalesReportQ0
GROUP BY SalesReportQ0.EmployeeID;
```
Click on the SalesReportQ Query and select a Report from the Insert Menu. Select Auto Report: Tabular on the Report Wizard and make changes to the design to look like the sample image given below:

Step: Modify the Total Field Properties

Click on the Total field on your report design (in Design View).
Press F4 (or right-click and choose Properties) to display the Property Sheet.

On the Format tab of the Property Sheet, change the following values:

Name: Total
Border Color: 12632256

Add VBA Code to Report Module

Open the Report in Design View.
On the Ribbon, go to View → Code
(Or click the “View Code” button on the toolbar above.)
In the Visual Basic Editor, paste the following code into the Report’s code module:

vba

Option Compare Database
Dim MinTarget As Double, curVal As Double

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'  Draw ellipse around controls that meet specified criteria.
Dim ctl As Control, ctl2 As Control, intPrintCircle As Boolean
Dim sngAspect As Single, sngYOffset As Single
Dim intEllipseHeight As Integer, intEllipseWidth As Integer
Dim sngXCoord As Single, sngYCoord As Single, mNewSpeedo As Long

On Error GoTo Detail_Print_Err

MinTarget = 100000
curVal = Me![Total]
If curVal < MinTarget Then
  intPrintCircle = True
Else
  intPrintCircle = False
End If
        
    Set ctl2 = Me.Total
If Not IsNull(ctl2) Then
    Set ctl = ctl2
    
    If intPrintCircle Then
   ' change this value to adjust the oval shape of the circle.
    sngAspect = 0.25 'Increase/Decrease this value to adjust the shape
    
    ' Get Height and Width of Control
    intEllipseHeight = Me.Total.Height
    intEllipseWidth = Me.Total.Width
    
    'Calculate Center X,Y Corordinate
    sngXCoord = ctl.Left + (intEllipseWidth \ 2)
    sngYCoord = Me.Total.Top + (intEllipseHeight \ 2)
    
    ' Draw Ellipse within the Control
            Me.Circle (sngXCoord, sngYCoord), intEllipseWidth \ 2, _
            RGB(255, 0, 0), , , sngAspect
           intPrintCircle = False
        End If
    End If

Detail_Print_Exit:
Exit Sub

Detail_Print_Err:
MsgBox Err.Description, , "Detail_Print"
Resume Detail_Print_Exit
End Sub

Save the report with the Name: Sales Report.

Testing and Fine-Tuning the Report

Open the Report in Print Preview to see the red circles in action.
The circle is drawn relative to the size of the Total textbox. If the textbox is too wide, the top and bottom parts of the ellipse might not be visible within the control boundary.

Fixing Ellipse Display Issues

To ensure the circle (ellipse) fits neatly around the value:
- Option 1: Reduce the width of the Total textbox slightly.
- Option 2: Adjust the aspect ratio used for drawing the ellipse.
For example, if you're using a sngAspect variable in your code like this:
```
vba
sngAspect = 0.3
```
Try changing it to a smaller value, such as:
```
vba
sngAspect = 0.25
```
A smaller aspect ratio makes the ellipse taller (more circular), while a larger one makes it flatter. You can experiment with values (e.g., 0.2 to 0.4) to find the best fit for your textbox.

Tip:

After each change, open the report in Print Preview to visually verify the result.

Conditional Formatting

If you prefer to highlight sales values using color coding instead of drawing circles, there's a simpler and more effective method called Conditional Formatting—available in MS Access 2000 and later.

For example, suppose we want to visually emphasize performance as follows:

Green for sales above $100,000
Red for sales below $100,000
The default (black) for sales is exactly $100,000

This can be achieved without writing a single line of code.

A sample image demonstrating this formatting is shown below:

Make a copy of the Sales Report and delete the Code from the VB Module.
Click on the Total textbox in Design View.
Select Conditional Formatting. . ., from the Format Menu.
Set the conditions and the Color as shown below:
After setting the first condition, click Add >> to expand the Conditional Formatting dialog box and define additional conditions. You can set up to three different conditions on the same field, each with its own distinct formatting. Once you've configured all the desired conditions and formatting options, click OK to save the changes.
Open the Report in Print Preview to display the Values in different colors based on the conditions that we set on the control.

When Conditional Formatting is not feasible

There are situations where Conditional Formatting cannot be used, and we must rely on the method of drawing elliptical shapes to highlight data. In both of the earlier examples, we were comparing individual values to a constant value ($100,000) and either drawing a circle or applying color formatting accordingly.

Now, imagine we’re running a Vehicle Service Station that holds Service Contracts with various companies to regularly service their vehicles. These services are scheduled either at fixed time intervals (e.g., every 3 months) or when a certain odometer reading is reached (e.g., every 5,000 kilometers).

Management requests a report showing each vehicle's Service Date, Odometer Reading, and Service Expenses. The report must be sorted by Service Date, and if any odometer reading is lower than the previous service's reading, it should be highlighted for investigation. This discrepancy may indicate meter tampering or data entry errors by service personnel.

In such cases, each odometer reading must be compared with the previous reading. If a lower value is detected, it should be circled in the report. This comparison continues throughout the report, and all such occurrences must be marked until a higher reading appears, which then becomes the new reference.

This type of dynamic, row-to-row comparison is not possible using Conditional Formatting, as it cannot access or compare values across different rows. Therefore, custom VBA code using the Detail_Print() event and drawing methods is essential for such reports.

Download

Highlighting_Report.zip

SAVING DATA ON FORMS NOT IN TABLE

a.p.r. pillaiAugust 09, 2007Forms, msaccess forms 1 comment

Introduction.

We typically store data in Tables, even though we interact with them through Forms. Unlike Tables, Forms are not designed to store data themselves. However, Forms can hold certain useful values independently, without relying on a data table.

Let’s consider a practical example where this capability becomes valuable. Suppose you want the Employees form to open in data editing mode (not data entry mode), and automatically display the last record you were working on during your previous session. This would let you resume your unfinished work right where you left off. To make this happen, we need to save the key identifying information of that last record on the Form before it is closed.

Import the Employees table and Employees form from the NorthWind.mdb sample database. This file is typically located in one of the following folders, depending on your version of Microsoft Office:

MS Office 2000:
C:\Program Files\Microsoft Office\Office\Samples
MS Office 2003:
C:\Program Files\Microsoft Office\Office11\Samples

Before proceeding, make a copy of the NorthWind.mdb file and place it in your current working folder. This ensures that you have easy access to the file whenever you need to extract forms, tables, or other sample objects from it.

Property Sheets.

We already know that database objects such as Forms, Reports, Tables, and others have properties that can be modified. These properties can either be edited manually at design time using the Property Sheet or programmatically changed at runtime.

In addition to these editable properties, Microsoft Access maintains other internal properties for each object. Some of these are visible in the database window when the view mode is set to "Details", including:

Name
Description
Created
Modified
Object Type

Among these, Name and Description can be easily modified directly in the database window. Just right-click on the object, and choose Rename or Properties from the shortcut menu.

Invisible Properties

There are additional properties, such as Owner—which is visible on the Property Sheet—as well as Permissions, AllPermissions, and several others that are not directly visible. However, we can examine and access these hidden properties using VBA code.

We can modify the properties of Form or Report objects through VBA code only when they are loaded into memory, either in Design View or Normal View. In such cases, these properties are referenced to the Form or Report object itself. For example, in the Custom Menu Bars & Toolbars 2 topic, we examined a program that opens Forms and Reports one by one in Design View, updates their Menu Bar and Toolbar property settings, and then saves the changes automatically using VBA.

But, there is another approach to refer to these objects. A graphical representation of these Objects' hierarchy is shown below:

Although the structural hierarchy mentioned above may initially seem a bit complex, the relationships between the components become easier to grasp when we relate them to familiar database objects. With a little practice, understanding these connections will come naturally. Let’s begin by examining the structure starting from the Database Object.

The database we already know, let us call it Northwind.mdb
Containers:
When we look at the left side of the database window (or at the top, in older versions), we can see several tabs representing different object types—each of these is referred to as a Container. Collectively, these make up the Containers Collection. It’s worth noting that Queries are stored within the Tables Container; there is no separate Queries Container object. When we want to refer to the Forms container in code, we would use:
```
Dim db as database, ctr as Container, doc as Document

Set db = Currentdb
Set ctr = db.Containers("Forms")
```
A dot (.) links the db (Database) and the Container Objects together, in its hierarchical order.
When we click on the Forms tab in the database window, we see a list of all the Forms we have created. Collectively, these are referred to as the Documents Collection under the Forms Container. Each individual form is referred to as a Document. Similarly, individual Tables, Reports, and Macros are also referred to as Documents, each belonging to the Documents Collection of their respective containers.
To set a reference to our Employees form (i.e., the document named "Employees") using a Document object variable, we write:

Set doc = db.Containers("Forms").Documents("Employees")
The Container Object reference is inserted between the Database and Document references. Or you may write:
Set ctr = db.Containers("Forms")
Set doc = ctr.Documents("Employees")
After setting a reference to the Document Object Employees in doc, we can work with the Properties of the Employees Document.

Is it still difficult to visualize the relationship between the elements shown in the diagram above and the actual database components we see every time we open Microsoft Access?

Every day, we work with these objects in an orderly fashion. For example, you first open the database, then click on the Forms tab (which represents the Container) to display all available Forms (referred to as Documents), and finally select and open a specific Form (Document). Physically, this is the route you follow to access and work with any individual object in the database window. The only difference, when programming, is that we refer to these same elements using their corresponding class names in code.

Once we can reference the Employees form this way, we can display its property names along with their corresponding values. Additionally, we can create a custom property and add it to the collection of existing built-in properties. This allows us to store and retrieve custom values from the form for later use.

Displaying Property Values

First, let us write a small program to print the Employees Form's built-in properties in the debug window.

Before continuing, ensure that all the essential Library Files are linked to your Database, particularly Microsoft DAO x.xx Object Library, where x.xx is the Version Number of your installation.

NB: Visit the Page Command Button Animation and follow the procedure to link the Library Files listed there (7 of them), and then come back and continue.

Copy and paste the following Code into a Global Module and save the Module:

Public Function DisplayProperties()
Dim db As Database, doc As Document
Dim i As Integer, j As Integer

On Error Resume Next

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")

   i = doc.Properties.Count

   For j = 0 To i - 1
       Debug.Print doc.Properties(j).Name, doc.Properties(j).Value
   Next

Set doc = Nothing
Set db = Nothing

End Function

Press Ctrl+G to open the Immediate Window (also known as the debug window). Click anywhere within the program code and press F5 to run it. The property names and their values of the Employees document will be printed in the debug window.

During execution, the program might encounter errors while attempting to print certain property values. To avoid interruptions, we have included the statement On Error Resume Next, which allows the program to skip over the error and continue running.

After execution, review the output displayed in the Immediate Window.

Creating a Custom Property.

Next, we will create a custom property alongside the built-in properties previously displayed. This custom property will store the EmployeeID value for future reference.

Copy and paste the following code into the same standard (global) module where you added the earlier program:

Public Function CreateProp()
Dim db As Database, doc As Document
Dim prop As Property

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")
Set prop = doc.CreateProperty("EmpCode", dbLong, 1)
doc.Properties.Append prop
doc.Properties.Refresh

Set doc = Nothing
Set prop = Nothing
Set db = Nothing

End Function

Let us review the code before running it:

The first statement Set db = CurrentDb establishes a reference to the currently active database.
The Employees Form is treated as a Document object, which is a member of the Documents collection within the Forms container.
After assigning a reference to the target form (document) to the doc variable, we use the CreateProperty() Method to define a new custom property. This method requires the property name, data type, and initial value, and returns a Property object, stored in the prop variable.
At this stage, the custom property is created but not yet added to the form’s Properties collection.
To do that, we call the Append method of the Properties collection and pass the prop object to it. This action adds the custom property to the collection.
Finally, Refresh The method of the collection object is called to update the collection, ensuring the new property is recognized and accessible.

Click anywhere within the above Code and press F5 to run the program, to create the EmpCode Property, with data type Long-Integer and with an initial value of 1.

If you run the program again without changes, it will display an error message indicating that the custom property EmpCode already exists.

To verify that the property was created successfully:

Run the first program we created earlier that displays the properties of the Employees form in the Immediate (debug) window.
You will now see EmpCode listed among the built-in properties, along with its assigned initial value.

If you wish to create additional custom properties, simply:

Change the property name (e.g., from "EmpCode" to "DeptCode"),
Adjust the data type constant (such as dbInteger, dbLong, dbText, dbDate, dbDouble, or dbSingle),
And set the initial value for the property.

Then, run the program with the updated values. Each new custom property you define will be stored on the form and can be accessed or updated later through code.

Saving EmployeeID in Custom Property

Now that we have created the Custom Property for EmployeeID, let us proceed with our original plan of opening the form with the last worked record.

Assume we are currently working on King Robert’s record (EmployeeID = 7) and are about to close the form. Before closing, we need to store the current record’s EmployeeID (7) into the custom property EmpCode.
Later, when the form is reopened, we can read the saved EmployeeID value from the EmpCode property and use it to locate and navigate back to the same record, making it the current record on the form. This allows us to resume our work exactly where we left off.

We need to add two VBA procedures to the Employees Form’s module.

Please follow these steps:

Open the Employees Form in Design View.
Open the Form’s Code Module (Right-click on the form → "Build Event" → choose "Code Builder").
Delete any existing code that came with the form during import (if present).
Copy and paste the two VBA procedures below into the form’s module.
Save the form after pasting the code.

Here are the two procedures:

Private Sub Form_Unload(Cancel As Integer)
'------------------------------------------------------------
' Author  : a.p.r. pillai
' Date    : 05/08/2007
' Remarks : Saving Data in Custom Property EmpCode
'-------------------------------------------------------------
Dim db As Database, doc As Document
Dim EC As Long

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")
doc.Properties("EmpCode").Value = Me![EmployeeID]

Set db = Nothing
Set doc = Nothing
End Sub

Private Sub Form_Load()
'---------------------------------------------------
'Author    : a.p.r. pillai
'Date      : 05/08/2007
'Remarks   : Read Custom Property EmpCode Value
'          : and use it to find the Record.
'---------------------------------------------------
Dim db As Database, doc As Document
Dim rst As Recordset, ECode As Long

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")
ECode = doc.Properties("EmpCode").Value

Set rst = Me.RecordsetClone
rst.FindFirst "EmployeeID = " & ECode

If Not rst.noMatch Then
    Me.Bookmark = rst.Bookmark
End If
rst.Close

Set rst = Nothing
Set doc = Nothing
Set db = Nothing

End Sub

✅ Time to Test the Program

Let’s verify if the project behaves as expected:

Open the Employees Form in Normal View.
Use the Record Navigator at the bottom to move to any record further down—just note the EmployeeID of the current record (for example, 7).
Close the form while still on that record.

🔁 Now for the real test:

Reopen the Employees Form.

If everything is working correctly, the form should automatically position itself on the same record (with the same EmployeeID) that you were working on before closing the form.

🧠 How It Works

In the `Form_Load()` Event procedure:

Retrieve the Saved Record Key:

The value stored in the custom property `EmpCode` (which holds the `EmployeeID` of the last active record) is read and assigned to the variable `ECode`.

Access the Underlying Recordset:

We use the form’s `RecordsetClone` object, which is an internal copy of the form’s data source. This is a parallel recordset that reflects the data displayed on the form.

This `RecordsetClone` is assigned to the object variable `rst`.

Locate the Saved Record:

Using the `FindFirst` method of the recordset, we search for the record where `EmployeeID = ECode`.

If found, we set the form’s `Bookmark` property to that of the matching record in the clone. This synchronizes the form’s current record with the one found in the `RecordsetClone`.

✅ This mechanism allows the form to reopen at the same record the user was last working on, making it easy to resume unfinished work.

📌 Understanding Bookmarks in Access Forms

Every time a form is opened with a Record Source (Table or Query), each record is automatically assigned a unique identifier called a Bookmark.

There are two separate sets of Bookmarks:
1. Form's own recordset – used to display and navigate records on the form.
2. RecordsetClone – an internal, parallel copy of the form’s data used in code for searching, filtering, or comparison.

Even though they point to the same data, the Form RecordsetClone maintains its own independent Bookmark values. However, you can synchronize them by assigning the 'Recordsetclone' Bookmark to the Form's Bookmark (not the other way around) — a technique often used to locate and focus on a specific record from code.

We cannot search directly on the form’s underlying recordset. Instead, we must use the form’s RecordsetClone object. To locate the specific record, we use the FindFirst method of the RecordsetClone, like this:

vba
rst.FindFirst "EmployeeID = " & Ecode

After executing the search, we check whether the record was found using:

vba
If Not rst.NoMatch Then

This tests the success of the search operation. If a match is found, we retrieve the Bookmark of the located record from the RecordsetClone and assign it to the form’s Bookmark property. This action moves the form's current record pointer to the located record, effectively returning the user to the same record they were working on during the previous session.

DataSavingForm.zip