Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access and Mail Merge-2

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.

  1. 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.
  2. Design a Form for the above Table for editing the Letter Body Text, for changing contents as and when it becomes necessary.
  3. 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.
  4. To select the required fields for the document contents, join the Address Book Table with the Letter table (created in step-1).
  5. 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, as how we are progressing through the steps to create it:

The Design Task

We need two sample tables, Employees and Orders from the Northwind.mdb Database. (Refer my earlier post Saving Data on Forms not in Table for the exact location reference of the sample file). Import the above Tables from 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 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; Memo Reference, Date, Subject, letter body text and others; are inserted from the Letter Table that I have mentioned above.

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

    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 Text Box 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.

  5. Letter Image given below with the sample data filled-in:

    Inserting a Statement

  6. 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 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 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 creates year-wise summary of Freight Value and changing its name to Sales. 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.

  7. 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).
  8. 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.
  9. We need one more Query to join both Employees Table and the Letter Table that we have 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.*,
    FROM Employees, Letter;
  10. After saving the Query open it in 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. On 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 Datasheet view you can see the Letter Table's single record is repeating 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.

  11. 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 on 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 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.

    The design is complete when we finish the following the few more steps:

    Inserting the Statement

    • Insert Yearwise_incntv statement that we have designed earlier between Para1 and Para2 above and link the statement with the EmployeeID field that we have placed separately at the FirstName Header Section. To insert the Yearwise_incntv statement into 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

  12. Open the Report in Print Preview, advance the Pages by clicking on the Page Control at the bottom. Check whether the Employee's name appearing at 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 same as above. Close the Report.
  13. Open the Letter Form in design view.
  14. 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()
       DoCmd.OpenReport "Letter", acViewPreview
    End Sub
  15. Open the Letter Form in normal view. Make some changes in the body text. Click on the Preview button. The changes that you have made in the text should reflect in the previewed letter also.

Your Report display should match with 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 Print it, which we will try next.


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...


Blog Archive

Recent Posts