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 excellent at presenting information in numbers and text. But, Charts go one step further in providing information in the form of pictures and a quick look at them says it all. If we know the simple rule that goes for a Chart, then we can create Charts in Access. The simple rule is, to show the change of an event over a period of time in the form of dots and lines in relation to the numbers that they represent, rather than in numbers and text itself.

You must know how to create MS-Access data into a form suitable for the preparation of Graphs. To get some familiarity, we will look into some examples that use only one set of values and the data format that is needed for the graph.

Events like the daily change of day, temperature, or change in body temperature, of patients in a hospital, to monitor their condition, or the change of the gold price over a period of time, etc., can be plotted in the form of Line/Bar Charts on Graph Paper manually. The Scale of the Chart can be determined and marked based on the maximum available value or more with a fixed interval from 0 at the bottom to the top of the Y-Axis (Vertically at the left side or right side Secondary Y-Axis of the Graph). Each day's value must be marked on the graph paper with a dot in relation to the Scale height on the left side and the dots can be joined by lines. The actual value must be placed near the dot. The date will be written at the bottom of the Graph horizontally (X-axis). The description of the Values plotted on the graph like Gold Price is placed as Legend on the Graph. Instead of Pencil and Paper, we can do the same thing with the help of Microsoft Graph Chart Object.

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 are presenting a Chart on the Company's Total Revenue from various sources like Vehicle Sales, Parts Sales & Service Sales and show the share of each value to the Total then Pie-Chart will be the ideal one for it.

But, if all the three values of the Pie-Chart (Vehicle Sales, Parts Sales & Service Sales) need to be plotted separately showing the month-wise performance of each category then Line Chart or Bar Chart will be suitable.

If the source data for the graph changes on a fixed interval, every month, then the data source object like Table or Query must be prepared to keep this aspect in mind so that the data automatically appears on the Chart when the source data changes, without manually modifying the Chart every time.

Charts in Excel and MS-Access.

Preparing Charts in Access found not as easy as in Excel. We will look into the most commonly used Chart Types: Line, Bar & Pie Chart examples in Access. Those who worked with Excel Charts may find it difficult to get adjusted to the data preparation methods of Access for charts. But when you are through with the initial difficulties I don't think you will go back to Excel anymore.

But, unlike Excel we have the advantage of several types of Queries and Macros, to automate the process, to skim out figures from large volumes of data quickly and effectively, for any type of Chart. Once it is prepared and set it up, it works every time without manual intervention.

Note: You may attach the Microsoft Excel Object Library to the Access Database library Files List so that we can make use of all the Chart Types and formatting capabilities of Excel can be made available in Access also.

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 & 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 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 on 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 a print preview 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

Introduction

Drawing a Page Border is very easy in MS Word and it is part of the Formatting Options and different artistic styles are also available. But when it comes to MS-Access we have to depend on Code to draw a Page Border before printing the Document. An Example Document image is given below:

Click to Enlarge

The above Document is imported from Northwind.mdb Database (visit the Page Saving Data on Forms not in Tables for location reference of the Database) without the Page Border (Catalog Report attached to Categories Table) and drawn a double-lined Page Border with the help of Code given below. If you would like to try it out on the same Report then Import the Categories Table and the Catalog Report from the Northwind.mdb database. The Code can be used on any Report that you wish to draw the Page Border and the border will be drawn within the Margin settings of the Page.

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 VB 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 Box Around Tabular Report Fields

Using the same method that we have used for drawing a Page Border we can draw boxes around the Tabular Fields arrangements on the Report's Detail Section without turning on the border of the fields. See the code in action on the Report that we have created in the earlier Post Highlighting Reports example below :

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 VB Module and change the Report Name in quotes to replace it with the name of your Report that you are pasting the code, 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 draws a nice Page Footer (a sample image is given below) with System Date and Number of Pages, etc.

The Page Footer Section

But, most of the time the Detail Section has to undergo changes and the Page Footer also must be changed. 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 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.

Re-Sizing Page Footer

There are times that even after drawing the Page Footer as the final step we may have second thoughts and go for modification of the Report by adding or removing fields and end up with a mismatch on the Page Footer part again. At this point either you may delete Page Footer elements and re-draw it or you may resize it with the help of the following Function:

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 above code into a Global Module and save it. Run the code as you did in creating the Page Footer above. If you encounter any error during compilation or Run time, visit the Page Command Button Animation and link the essential Library Files (7 or them) by going through the procedure explained there. Come back and re-try again.

Highlighting Reports

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

Introduction.

Analysis of data stored in Tables and providing resultant information in the form of Reports to the outside world is an important function of any Database Management System.

An Access Application may contain several Tables logically related, maintained separately for easier handling. When it is time to prepare Reports related tables are selected and joined on common information in them, filtered and placed the output on Reports. Meaningful and timely Reports play an important role in decision-making.

Report designing is an art in its own merit, like Form designing. MS-Access is equipped with excellent Report Designing Tools and with a little practice, you can master it.

We have been asked to prepare a report on the Company's Sales Team, to review their monthly Sales Target Achievement. A mandatory Target Amount of $100,000 is fixed on each member of the Team and the Management wants a month-end Report to review his or her performance. The management focuses on the performance of the Sales Team every month and if any member of the Team lags behind Target, they have to undergo a postmortem of their activity area to set things right for the future.

We have prepared a sample Report on the Sales Team's Month-end figures, highlighting cases that didn't meet the stipulated Target Amount of $100,000, with a red circle around them.

The finished Report image is given below:

The tabular Report above is designed with the Report Wizard and nothing special about it except to highlight Values less than $100,000 with a circle around them. A small VB Routine in the Detail_Print() Event Procedure compares the value on each row with the target figure of $100,000 when the printing/print previewing is in progress and if any of them is less than $100,000 then draws a red circle around that Value.

Prepare the Report

To prepare this report we need two Tables from the Northwind.mdb database. If you are not sure where the file can be located, visit the earlier Post: Saving Data on Forms not in Table for the location reference of the Northwind.mdb database. Import the following two Tables from the Northwind.mdb database.

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 on it 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;

Go through steps 2 & 3 above, to create another Query from the source data of the first Query SalesReportQ0, Copy and Paste the following SQL String into the Query's SQL Window and save it 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:
Click on the Total Field and display the Property Sheet. Change the following Property Values:
- Name: Total
- Border Color: 12632256
Display the VB Module of the Report, click on the Code Toolbar Button above, or select Code from View Menu.

Copy and paste the following Code into the VB Module of the Report:

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.
Open the report in Print Preview to see how it works. The Circle is drawn based on the size of the Total Textbox. If the control is too wide then upper and lower parts of the elliptical shape may not appear within the control boundary. To correct this problem you can reduce the width of the Total Field if it is too wide, or you may reduce the value in sngAspect = 0.3, to 0.25. You may increase or decrease this value and try out to perfectly fit the elliptical shape, within the textbox boundary.

Conditional Formatting

If you prefer Color Coding the Sales Values, rather than struggling with the above technique, then there is an easier way to do it, known as Conditional Formatting (MS-Office 2000 or later).

Let us say, we want all values above $100,000 should appear in Green Color, below $100,000 Red, and all $100,000s should print in normal Color. A sample image is given 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 control down to set more conditions. Up to three conditions you can set on the same field, with Different Formats. After setting the values, 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 we cannot use the conditional formatting method and need to go from drawing the elliptical shape method. In both examples given above, we are comparing individual values to a constant ($100,000) and then draw a circle or change color with conditional formatting.

Assume that we are running a Service Station and we have Service Contracts with Companies servicing their vehicles regularly for a certain period of time or the Vehicle's Odometer reading reaches a certain level. Till that time the Vehicle comes to servicing on a fixed interval of 3 months or after every 5000 kilometers. etc.

The management asks us to prepare a Report on all Vehicles on Service Contracts showing the Date of Service, Odometer Reading & Expenses. The Report is sorted on Service Date and if there is any Odometer recording found in Error (i.e. The meter reading is recorded less than the earlier service-time recording) that must be highlighted in the Report to quickly identify them for investigation. It can be tampering with the meter or it can be recorded wrongly by the Servicing personnel. In either case, it should be highlighted.

In this case, we have to compare each value with the previous meter reading and if the current value is found less than the previous one then it must be highlighted with a circle. There may be a repetition of lesser values than we have, to continue putting circles around all those values till we reach a value greater than the one that we are holding as key. In this kind of situation, the conditional formatting method cannot be used.

Download

Highlighting_Report.zip

SAVING DATA ON FORMS NOT IN TABLE

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

Introduction

We normally save data in Tables, even though we enter them on Forms. We cannot save data on Forms as we do on Tables. But, we can definitely save some useful information on the Form itself without the support of the Data Table to store the values.

Let us look into a situation where we need such a facility for our day-to-day activities. Assume that we want to open the Employees Form in data editing mode (not in data entry mode) with the last record that we were working on during the earlier session, to continue our unfinished work from there. To do this, we must save the key information for the last worked record on the Form before closing the Employees Form.

Import the Employees Table and Employees Form from the NorthWind.mdb sample database located at C:\Program Files\Microsoft Office\Office\Samples (MS-Office 2000) or C:\Program Files\Microsoft Office\Office11\Samples (MS-Office 2003). Make a copy of this File to your current working folder so that whenever you need information from this file, you can easily locate it.

Property Sheets

We already know that the Database Objects like Forms, Reports, Tables, and others have properties and we can modify the Property Values by editing them manually at design time on the Property Sheet or change many of them through the program, during the run time. Besides these properties, there are others maintained by MS-Access and some of these are visible to us, like Name (always shown in the Table, Forms, and other Tabs of the database), Description, Created, Modified, and Object Type on the database window when you select the Icon Type as Detail for display.

Name and Description we can change the database window itself by right-clicking on the object and selecting Rename/Properties options from the shortcut menu.

Invisible Properties

There are other Properties like Owner " visible on the property sheet " Permissions, All Permissions, and others not visible but we can examine those properties with the help of the Code.

We can modify the Properties of Forms or Reports Collection Objects through VB Code only when they are loaded into Memory (when open in design view or Normal View). In such cases, we refer to the Properties in relation to the Form Object or Report Object. Examine the Menu Bar/Toolbar set-up programs in Custom Menubars & Toolbars2 Topic. There, we have opened Forms and Reports one by one in Design View to insert Names of the Menu Bars & Toolbars in their respective Properties and saved them automatically with the help of the Code.

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

Even though the above structural hierarchy appears to be somewhat difficult to understand. The relationship between one and the other, when we relate them to the Objects that we are already familiar with, but we will learn it in no time. Let us look at them from the Database Object onwards.

The database we already know, let us call it Northwind.mdb
Containers:
When we look at the left side of a database (older versions on the top) we can see the above-listed Tabs, each of them we can call a Container, and all of them put together as a Group, as you have guessed, is Containers Collection. Queries are part of the Tables-Container; there is no Queries Container Object. When we want to refer to the Forms Container in the Program we will write:
```
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 Containers Objects together, in its hierarchical order.
When we click on the Forms Tab on the database window we can see all the Forms that we have created and we call all of them together as Documents Collection.
One particular Form is a Document. Individual Table, Report, and Scripts (Macros) all are referred to as Document, a member of the Documents Collection under their respective Container. To set a reference to our Employees Form (or Document) to 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 hard now to visualize the relationship between the elements of the diagram that we have drawn above and the database components that we see every time we open it?

Every day we work with these objects in an orderly fashion, like you open the Database first, click on the Forms Tab (Container) to display the Forms (Documents), and click on a single Form (Document) and open it. So physically you have to follow that route map to work with any single object in the database window. The only difference is we are using different class names in Programs.

Once we are able to refer to the Employees Form this way we can display the Form's Property Names and the Values they contain or create a new Custom Property and add it to the collection of existing built-in properties to store the values that we want 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 you have to 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 display the Immediate Window (debug window). Click anywhere within the program and press F5 to run it. The Employees Document's property Names and Values are printed in the debug window. When running the program, while attempting to print some property values the program may stop with an error. To sidestep that problem we have added an On Error Resume Next statement to continue executing the program ignoring the Error. Examine the output in the debug window.

Creating a Custom Property

Next step, we will create a Custom Property, alongside the built-in properties that we have displayed, to store the EmployeeID Value. Copy and paste the following code into the same Global module that you have pasted the above 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 examine the code before running it. The first statement Set db = Currentdb points to the active database that we are working on. The Employees Form is a Document-Object, a member of the Documents Collection of Forms Container Object. After setting the Document reference into the doc object we call the CreateProperty() method of the Document object to create a Custom Property with the Name, Data Type & Initial Value that we have provided and stores it in a Property Object type variable prop. The new Custom property is not yet added to the Document's Properties collection. The next step calls the Append method of Properties object and the Custom Property in prop variable is passed on to the method to add it to the Properties Collection and refreshes it in the next line of code.

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

If you will run the program again, an error message will display saying that the Property EmpCode already exists. You can now run the first program, that we have created at the beginning to display the properties of Employees Form in the debug window, to print the EmpCode property and its initial value alongside the built-in properties. If you want to create more custom properties, change the Property Name, data type constants (dbinteger, dblong, dbtext, dbdate, dbdouble, and dbsingle), and initial value for the new property and run the program again with the change.

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 that we are currently working on King Robert's (EmployeeID No. 7) Record and close the Form. Before we close the Form we must save EmployeeID 7 (or EmployeeID of the current record) into the Custom Property EmpCode.
When we open the Form again, we will read the EmployeeID back from the EmpCode property and use the EmployeeID to search and find the Employee record and make that record current on the form, so that we can continue our work on that record onwards.

We need two programs on the Employees Form Module. Copy and paste the following Programs into the Employees Form Module (you may delete the existing code in the Form Module, which came with the Form when Imported), before pasting our Code) and save the Form:

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

It is time to test our program and check whether the whole project works according to our original plan. Open the Employees Form in Normal View and click on the Record Navigator to move the records further down into a record (not necessary that it should be 7) and note down the EmployeeID and then close the Form when you are still on that record.

When you open the Form again the current record should be the one that you have noted the Employee ID.

How it works

In the Form_Load() Event procedure, first, we are reading the value stored in the EmpCode Custom property and storing it in ECode Variable. Next, we are making use of the RecordsetClone Object of the Form, a parallel recordset, that is created when a form is opened with Record Source from a Table or Query, and assigning it to the rst Recordset object so that we can make use of the object's methods like FindFirst to search and find the record that we are looking for.

A Unique identifier known as a Bookmark is created for each record every time when the Form is open with the Record Source from a Table or Query. There are two sets of them, one is in the Form-based records and another set for the RecordsetClone, and both work independently.

We cannot search for our record on the Form-based Recordset, instead, we must look for it in the RecorsetClone Object of the Form. We make use of the FindFirst method of this object to locate the record that we are looking for, with the statement: rst.FindFirst "EmployeeID = " & Ecode. We have to test and find whether the search was successful or not. So, the next statement If Not rst.NoMatch then (we are using the rst.NoMatch method of the RecordsetClone object) to test and determine the success of the search. If found successful, then read the Bookmark of the record that we have found in the RecordsetClone and use it to set the Bookmark of the Form. The record on the Form automatically moves to the record that we were working on in the earlier session and becomes current on the Form.

DataSavingForm.zip

Edit Data in Zoom-In Control

a.p.r. pillaiAugust 02, 2007Custom Wizards, Forms, msaccess forms 8 comments

Introduction

While designing a Form, Report, or Query certain properties of these objects can Zoom in and edit the property values in a big window, like Record Source Property, Filter or Order By property, etc. When you right-click on these properties a Shortcut Menu will open up with the Zoom option on it, (see the image below) and when clicked we can edit the property values comfortably in a big window.

But this option is not available when editing data on Forms, for large Field Type like Memo or text fields that hold more data than it displays.

Click to Enlarge

The Zoom Tool Button is available under the Properties Category of Forms, Reports, Query, and Index groups in the Built-in Shortcut Menus. We can make a copy of this button and paste it on our Custom Shortcut Menu or on the built-in Form View Control Shortcut Menu, as we did for Animated Floating Calendar, and use it on our Forms if needed.

Designing the Zoom Control

But we will do it differently. We are going to design our own Zoom In Control and it will be interesting to explore and find out how this thing works?

We can create this very easily. We need a small Form, two small VB Routines, and a Shortcut Menu Button, similar to the one that we have created in the Form View Control sub-menu of our Animated Floating Calendar.

Open a new Form and Design a Text Box and two Command Buttons as shown below:

Click to Enlarge

Set the properties of the Form, Text Box, and Command Buttons as given below:

Display the Form's Property Sheet (Press Alt+ENTER) and change the following Form Properties:
- Caption = Zoom
- Default View = Single Form
- Allow Edits = Yes
- Allow Deletions = No
- Allow Additions = No
- Data Entry = No
- Scroll Bar = Neither
- Record Selectors = No
- Navigation Buttons = No
- Dividing Lines = No
- Auto Resize = Yes
- Auto Center = Yes
- Popup = Yes
- Modal = Yes
- Border Style = Dialog
- Control Box = Yes
- Min Max Buttons = None
- Close Button = Yes
- Whats this Button = No
- Width = 5.4583"
- Allow Design Changes= Design View Only
- Movable = Yes
Click on the Detail Section of the Form and change the Height and other properties as shown below:
- Height = 2.4167"
Text Box and Command Buttons Properties
Text Box Properties:
- Name = txtZoom
- Left = 0.2083"
- Top = 0.1667"
- Width = 4.1875"
- Height = 2.0417"
Command Button1 :
- Name = cmdOK
- Caption = OK
Command Button2 :
- Name = cmdCancel
- Caption = Cancel
Display the Form's VB Module (Click on the Code Toolbar Button above (when the Form is still in Design View) or select Code from View Menu), copy and paste the code given below into the Form Module, and save the Form with the name Zoom.
```
Private Sub cmdCancel_Click()
    DoCmd.Close acForm, "Zoom"
End Sub

Private Sub cmdOK_Click()
   CloseZoom
End Sub
```
Create a Custom Toolbar Button
Create a Custom Toolbar Button (see earlier Post Custom Menu Bars and Toolbars) either on the Toolbar above or on your Custom Shortcut Menu, if you have created one earlier, or create a button on the Form View Control submenu under the Forms heading in the Built-in Shortcut Menu, where we have placed a new button for our Animated Floating Calendar.
Right-Click on the button and display properties. Type =ZoomOpen() in the Action control and close the Toolbar Customize Dialogue control.

Copy and paste the following VB Code in a Global Module and save it.

Public Function ZoomOpen()
'------------------------------------------------------
'  Author  : a.p.r. pillai
'  Date    : 29/07/2007
'  Remarks: Open Zoom Control with Active Field's Data
'------------------------------------------------------ 
Dim varVal, ctl As Control, intFontWeight As Integer
Dim strFont As String, intFontSize As Integer
Dim BoolFontstyle As Boolean, boolFontUnderline As Boolean

On Error GoTo ZoomOpen_Err

Set ctl = Screen.ActiveControl   
strFont = ctl.FontName   
intFontSize = ctl.FontSize   
intFontWeight = ctl.FontWeight   
BoolFontstyle = ctl.FontItalic   
boolFontUnderline = ctl.FontUnderline

varVal = Screen.ActiveControl.Value   
DoCmd.OpenForm "Zoom", acNormal

With Screen.ActiveForm.Controls("TxtZoom")
   .Value = varVal
   .FontName = strFont
   .FontSize = intFontSize
   .FontWeight = intFontWeight
   .FontItalic = BoolFontstyle
   .FontUnderline = boolFontUnderline
End With

ZoomOpen_Exit:
Exit Function

ZoomOpen_Err:
Resume ZoomOpen_Exit
End Function

Public Function CloseZoom()
'------------------------------------------------------
'  Author : a.p.r. pillai
'  Date   : 29/07/2007
'  Save Edited Data back into the Source Field
'------------------------------------------------------ 
Dim vartxtZoom, strControl As String

On Error GoTo CloseZoom _Err
'copy the Edited Data into the Variable vartxtZoom 
vartxtZoom = Forms("Zoom").Controls("txtZoom").Value
'close the Zoom Form 
DoCmd.Close acForm, Screen.ActiveForm.NAME

'The Source Data field become active again 
If Screen.ActiveControl.Locked = True Then
   strControl = Screen.ActiveControl.NAME
   MsgBox "Read-Only Field. Changes will not be Saved.", "Control : " & strControl
   GoTo CloseZoom _Exit 
Else
    If IsNull(vartxtZoom) = False And Len(vartxtZoom) > 0 Then
        Screen.ActiveControl.Text = vartxtZoom
    End If 
End If

CloseZoom _Exit:
Exit Function

CloseZoom _Err:
Resume CloseZoom _Exit
End Function

Open one of your Data Editing Forms, preferably with a memo field having 2 or 3 lines of text. Right-Click the memo field (or any field that you prefer) to display the Shortcut Menu with the button that we have created. If you have created a Toolbar button above, then select a field on the form to make that field active, and then click on the Toolbar Button above. I have added a new button on my Custom Shortcut Menu and designed a Button Image (see Shortcut Menu image below), showing a small area zooming in into a big window, but the finished Button Image looks like a CRT Monitor.

Click to Enlarge
When the Zoom button is clicked it Calls the first Function =ZoomOpen() and the Zoom In window will open up with the active field's data in the txtZoom Text Box for editing. The font properties of the parent control will be copied into Zoom In Control.
After adding/editing the text, click the OK button on the Control to save the edited data into the source field and to close the Zoom Control. If the Cancel Button is clicked, then the Zoom Control is closed without making any changes to the source field data.

Click to Enlarge

If the active field is locked from editing, the Zoom In control will still open with the field data. But, while attempting to save changes, it will display a message indicating the read-only status of the field and close the control without saving the contents.

If the Source Field is locked from editing, then it is a better idea not to open the Zoom In Window at all with the read-only field data. Otherwise, after the user makes so many changes, and then tries to save them, issuing a warning message at that point is meaningless and will adversely affect the reputation of our Zoom In Control.

Before opening the Zoom window, check the Active Field's Editing Status, whether locked or not, if it is then displayed an appropriate message do not open the Zoom In Control at all. This change, I leave it to you as an Exercise. Modify the code yourself and try it. If you could not make it then let me know.