MS-Access and Mail Merge-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Form Letters

A Form Letter can be anything like an Invitation to the Members of a Club or a Notice to the Share Holders of the Company or a Circular to the Head of the Departments of the Organization or a forwarding memo of Departmental Telephone Expenses Statement and so on. In all these cases the body text of the letter will be standard, but the addressee information will change on every page of the document.

When we plan for a Form Letter in Microsoft Access we must carefully organize five different elements of this task.

Design a Table and add a single record to hold the main-letter contents like Memo Reference, Date, Subject, Body Text, and Letter Footer Information like Department Head who will sign the document, etc.
Design a Form for the above Table for editing the Letter Body Text, and for changing the contents as and when it becomes necessary.
Processing of data and designing a sub-report (like a statement on department-wise telephone expenses) if needed to insert into the body of the main letter. If this is not required then this step can be omitted.
To select the required fields for the document contents, join the Address Book Table with the Letter table (created in step 1).
Design the Main Form Letter.

A sample image of the completed Form Letter, that we are going to prepare, is given below. Take a look at it so that you will have a general idea, of how we are progressing through the steps to create it:

Click to Enlarge

The Design Task

We need two sample tables, Employees and Orders from the Northwind.mdb Database. (Refer to my earlier post Saving Data on Forms not in Table for the exact location reference to the sample file). Import the above Tables from the Northwind.mdb database.

If you check the above letter the Addressee information is picked from the Employees Table and the statement appearing in the middle is prepared from the Orders Table. In both tables, EmployeeID is a common field and is used here to link the embedded statement with the EmployeeID of the Employees Table, so that the correct statement related to the Employee will appear in the letter. All other information; including Memo Reference, Date, Subject, letter body text, and others; is inserted from the Letter Table that I have mentioned above.

Design a Table with the following Field Structure and save it with the name Letter:
Open the Table in the datasheet view and enter HRD/ABCD/001 in the FRefNo Field to introduce a record in the Table and close it.
Click on the Letter Table and select Form from Insert Menu and select Design View from the displayed list.
Design the Form like the sample image given below.

Click to Enlarge
Write a Macro to close the form Letter. Select the Close Button, display the property sheet, change the Name Property to cmdClose, and set the macro to the On Click Event of the Close Button. Click on the Preview button, display the Property sheet, and change the Name Property to cmdPreview. We will write a small Sub-Routine later for the Preview button. Display the Property Sheet of the Subject TextBox and set the Scroll Bars Property= Vertical. Make the same change for Para1 and Para2 Text Boxes as well. Save the Form with the name Letter.
Letter Image is given below with the sample data filled in:

Click to Enlarge
Inserting a Statement
We will make use of three columns of data, EmployeeID, OrderDate, and Freight, from Orders Table to prepare a statement to insert into the Form Letter as shown in the first image above. We will pretend the Freight Value is the Sales Figure of each employee and will extract the Year from the OrderDate Field taking it as Sales Year. We need three Queries to prepare a Report Table. Copy and paste the SQL String given below one by one into the SQL Window of new Queries and save it with the Query Names as indicated:
- Query Name: Yearwise_FreightQ0
```
SELECT Orders.EmployeeID,
 Val(Format([OrderDate],"yyyy")) AS [Year],
 Sum(Orders.Freight) AS Sales
FROM Orders
GROUP BY Orders.EmployeeID, Val(Format([OrderDate],"yyyy"));
```
- Query Name: Yearwise_FreightQ1
```
TRANSFORM Sum(Yearwise_FreightQ0.Sales) AS SumOfSales
SELECT Yearwise_FreightQ0.EmployeeID
FROM Yearwise_FreightQ0
GROUP BY Yearwise_FreightQ0.EmployeeID
PIVOT Yearwise_FreightQ0.Year;
```
- Query Name: Yearwise_IncentiveQ
```
SELECT YearWise_FreightQ1.* INTO Yearwise_Incentive
FROM YearWise_FreightQ1;
```
NB: You must be careful how you specify the Target Table name for a Make-Table Query and the name of the Query itself. Both should not be the same, otherwise, you will run into trouble when you run the Query. I have added the letter Q at the end of the Query name Yearwise_IncentiveQ, you can use any name you prefer.

With the first Query, we are selecting the data from Orders Table and creating a year-wise summary of Freight Value, and changing its name to Sales. The second one is a Crosstab Query which transposes the data, year-wise. Since we cannot directly use Crosstab Query for Report we are creating a table Yearwise_Incentive with a Make-table query.
After creating and saving the third Query, double-click on it to run and create the output table Yearwise_Incentive (Click OK to warning messages).
Design a Report using the Yearwise_Incentive Table as shown below. The size of the Report is approximately 6.5" wide. Do not use Page Header/Footer Sections of the Report. Put the Field Heading Labels on the Report Header Section. Save the Report with the name Yearwise_Incntv.

Click to Enlarge

We need one more Query to join both the Employees Table and the Letter Table that we created at the beginning. Copy and Paste the following SQL String into the SQL Window of a new Query and save it with the name LetterQ.

SELECT Letter.*,
 Employees.EmployeeID,
 Employees.TitleOfCourtesy,
 Employees.FirstName,
 Employees.LastName,
 Employees.Title,
 Employees.Address,
 Employees.City,
 Employees.Region,
 Employees.PostalCode,
 Employees.Country
FROM Employees, Letter;

After saving the Query open it in the design view and check how both tables are placed on the Query design surface. Remember, we have created only one record in the Letter Table but there are 9 records in the Employees table. In the Query design area, we have placed both queries without joining them together with a common field and placed fields from both tables on the columns. When you open the Query in the Datasheet view you can see the Letter Table's single record is repeated for each record in the Employees table.

NB: If there are more records in the Letter Table, then those records will repeat for each record in the Employees table, which will result in printing more than one letter for each employee.
Design the Main letter using the LetterQ query as shown below.
Look carefully at the image. The Report Header/Footer Sections, as well as Page Header/Footer Sections of the Report, are not used in the Report; instead, the header part of the Memo is designed within the FirstName Header Section. To create the FirstName Header/Footer Sections, Click on the Sorting and Grouping Toolbar Button or select the same option from the View Menu. On the Displayed Control select FirstName in the Field/Expression Column and in the Group Properties below, select Yes in the Group Header and Group Footer properties. When the FirstName Header Section appears, point the mouse over the Detail Section horizontal bar to change the mouse into a cross, click and drag the detail section down to get enough space to design our Letter header Section.

Click to Enlarge
The design is complete when we finish the following few more steps:
Inserting the Statement
- Insert the Yearwise_incntv statement that we designed earlier between Para1 and Para2 above and link the statement with the EmployeeID field that we have placed separately in the FirstName Header Section. To insert the Yearwise_incntv statement in the main report move the main report to the right of the Database Window and position it side-by-side (click on the Report Tab, if it is not visible) click and drag the statement and place it between Para1 and Para2 and properly place it as shown above. Re-size the report width, if it got expanded to the right.
- Click on the Yearwise_incntv statement, display the Property sheet, and insert EmployeeID in Link Child Field and Link Master Field properties.
- Display the property sheet of the EmployeeID control in the Header section and set its Visible Property = False.
- Display the Property Sheet of the Subject control and set the Can Grow, Can Shrink properties to Yes.
- Change the Can Grow, Can Shrink properties of Para1 and Para2 also to Yes.
- Click on the FirstName Footer, display the property sheet, and change the Force New Page property value to After Section.
- Save the Report with the name Letter.
Preview of Report
Open the Report in Print Preview, and advance the Pages by clicking on the Page Control at the bottom. Check whether the Employee's name appearing in the Address section at the top, changes on each page and the name appearing in the statement in the middle of the paragraphs is also the same as above. Close the Report.
Open the Letter Form in the design view.
Click on the Preview button and display the Property Sheet. Select [Event Procedure] on the On Click Event property and write the following code in the Form Module and save the Report:
```
Private Sub cmdPreview_Click()
   Me.Refresh
   DoCmd.OpenReport "Letter", acViewPreview
End Sub
```
Open the Letter Form in a normal view. Make some changes in the body text. Click on the Preview button. The changes that you have made in the text should be reflected in the previewed letter also.

Your Report display should match the sample image given at the top of this page.

With this article, we have set the stage for Merging of Data Field Values and Built-in Functions into the body text of the Letter and Printing it, which we will try next.