Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Word Mail-Merge with Ms-Access Table

Introduction.

Form Letters.

Form Letters are prepared from a standard template merged with addressee related information filled in on each letter.  Appointment letters, call letters for interviews or Employment Contracts etc. are some of the examples and easier to prepare them in Ms-Word Mail-Merge mode, rather than composing them individualy for each addressee.

These type of Letters will have some common body text, applicable to all the recipients and will have specific areas where personalized information can be inserted, like Name and Address, interview dates or nearest location where the candidates can attend the interview and so on.

If the interview calls should go to hundreds of prospective candidates then we need to prepare only one letter with standard body text applicable to all receipients.  Other personalized information can be automatically inserted from the attached data table, at appropriate places on each letter, at printing time.  This process is known as Mail-Merging.

We have already made an attempt to mimic this process in Microsoft Access itself with the help of VBA, some time back, and the links of those Posts are given below for your reference:

  1. Ms-Access and Mail-Merge
  2. Ms-Access and Mail-Merge-2
  3. Ms-Access and Mail-Merge-3

The Names, Addresses and other related information of each individual will be kept in an external Table.  The external Table can be prepared in any of the following Applications:

  1. MS-Word Document with Table Grid.
  2. MS-Excel Data Table.
  3. MS-Access Table.

The Table can be linked to the Form Letter so that Ms-Word can pick the required information from the Table and insert them where Table Field-Codes are placed on the body of the letter.

Sample MS-Word Table.

We have prepared a small Table in Word Document, within the Table Grid, as an example  in case if you prefer to prepare one in Word.  Here is an image of the data table with only 3 entries, similar to the one we have in Northwind.accdb Employees Table.

Click on the Image to Enlarge.


MS-Access Employees2 Table.

But, we will be using a modified Employees Ms-Access Table prepared with few new fields, for our Mail Merge Demo Run, from the original Employees Table of Northwind.accdb sample database.  The  image of the Employees2 Access Table is given below:

Click on the Image to Enlarge.

Create the Employees2 Table from the Employees Table of Northwind.accdb, with the selected fields in your Database.  Create three more fields and fill it up with sample data as shown in the above image.

Sample Letter Specimen.

A Specimen image of the Letter, without the individual Employee details is given below:

Click the Image to Enlarge it.

Create a Letter in MS-Word similar to the specimen given above and save it with the name: Employees.

Sample Merge-Printed Letter.

When we merge print the personal information on the Document, from Access Table, it will look like the sample image given below:

Click the Image to Enlarge.

Inserting the Merge Fields on Document.

Let us prepare a Mail-Merge Document in Word, with the above Employees2 Table as Source Data,  matching the above merged letter model. 

We assume that the above Employees of our Organization is awarded with an Annual increment, in appreciation of their commendable contribution to the Company’s overall growth and encourage them to do better by taking up future challenges of the company.

  1. Open the Employees Word Document, as per specimen given above, created and saved earlier.  If you have not yet created one so far, then let us do it now.
  2. Click on Start Mail-Merge from Mailings Menu and select Letter.
  3. Click on Select Recepients and select Use Existing List.
  4. Browse and find the Database where you have the above Employees2 Table is and Click Open.
  5. The list of Tables are displayed, select Employees2 Table and Click OK to select it.  Now, the Employees2 Table is attached to the Document.
  6. Next, if you click on the Edit Recepient List it will display all the records in the Employees2 Table with check-marks indicating that all the employee records are selected for Merge-Printing the Letters.  If any employee is to be excempted from the letter then remove the check-mark on that item.

    First we will insert the Address Block and Greeting Line information on the Letter, from the Merge-Fields selected from the attached Table.  Even though we can insert these to sections in two simple steps, we will do it the hard way to make things to our liking and less complicated, if the automatic method is not properly understood, for the first time user.  You may try them later by selecting Address Block and Greeting Line buttons and their optional settings to modify the output on the document.

  7. Click some where below the Reference information (Ref:ABC/HRD/2020/1234-) and above the word Dear on the Letter.
  8. Click on Insert Merge Field option in Mailings Menu and click on First Name Field.  Open the same list again and select Last Name.
  9. Click in the middle of both field-codes and tap the space bar to insert a space between First-Name and Last-Name segments of the Employee Name and position the insertion point on the next line.
  10. Select Address from the Merge-Field list for the second line.
  11. Select City for the third address-line.
  12. Insert State/Province and Country/Region on the next line, with a space in between.
  13. Position the Insertion point after the word Dear with a space, insert First Name and Last Name  fields with a space in between.

    After inserting the merge fields it will look like one of the images given below, based on the Field-Code display option setting ALT+F9.

    OR

    The second image is the expanded form of Merge Field Codes. You can toggle between these two states by pressing ALT+F9 Keys.  see that the Preview Result button is in de-selected state.  If the Preview Result button is on then the actual data will be displayed.

  14. Position the insertion point on the Designation line and insert the Job Title Merge-field.
  15. On next three lines insert Pay, Grade and Wef (stands for with-effect-from) Fields.

    When completed it will look like the following image:

    We need two more pieces of data for Reference Sequence Number and Date of the Letter.

  16. Position the insertion point at the end of the existing reference number, immediately after the dash character (Ref: ABC/HRD/2020/1234-).
  17. Select Page Number from Insert Menu and select Current Position from the displayed list.
  18. Position the insertion point after the word Date:.
  19. Click on Date & Time option, under Insert Menu and select one of the Date-format options displayed.
  20. Press Ctrl+S Keys to save the Word Document with the changes.

Merge-Printing the Letter.

Now, we are ready to Merge-Print the Document for each selected employee on the Employees2 Table.

  1. Click on Preview Result button, with the Zoom Glass image, under Mailings Menu. 
  2. If it is still on Field-Code display, then Press ALT+F9 Keys to Fill-in the first Employees data, wherever we have inserted the Merge-Fields on the Document.
  3. Now, you are viewing the sample Document you will get when Merge-Print the Document on the Printer,  one Document for each employee.
  4. Now, Click on Finish & Merge button and select Edit Individual Document. . .  and select All.

All the Documents are merged with Employees information and you are allowed to edit the Pages, if needed.  Can insert spaces between lines or inserted information can be formatted in Bold letters.  If Letter head is not appearing properly then insert a line and so on.

Once you are finished editing, select Print from Office Button, then Preview or Print the Document on Printer.

If you have selected Send EMail Message at Step-4 above then you can send the Document as Email Message.

  1. MS-Access and E-Mail
  2. Invoke Word-Mail Merge from Access2007
  3. Automated Email Alerts
Share:

Auto-Numbers in Query Column Version-2

Introduction

During January 2010 I have published a Function: QrySeq() - Auto-Numbering in Query Column  on this website and well-received by the readers all through these years.  While going through it again I thought it can be written with less Code and improve it’s performance by using a better approach, other than Variant Array.

When the function QrySeq() is called from a query record the program searches through the Array of Unique Keys and look for the matching key, passed from the record as parameter, finds it and returns the sequence number from Array-Element to the calling record.

If the Query have large Volume of records this process may take more time, because every time the program looks for the Key Value from the beginning of the Array.

The New Version is with the Name: QryAutoNum()

Using Collection Object  instead of Array.

You can find a detailed discussion on Collection Object in Ms-Access and Collection Object Basics Page.

Here we will have a brief introduction to know what it is and how it is used in VBA.  The Collection Object is a versatile Object that can hold, in general terms, any Values, Numeric or String Values, Class Module Objects or collection of other Objects.  The Collection Object is instantiated in VBA programs in the following manner:

'declare a Collection Object. Dim ABC as Collection 'create an instance of Collection Object in Memory Set ABC = New Collection 'We can Add built-in data types: Numeric, Strings etc ‘or Objects like Class Module Objects, ‘or other Collection Object as Items to the Collection Object.

'Use the Add method to add a Collection Item to the Object. ABC.Add 25 ABC.Add "ms-accesstips" 'When Collection Object Items added this way, ‘it can be retrieved only in the added order. For j = 1 to ABC.Count 'gets the count of Items Debug.Print ABC(J)’ retrieve in Item index Order. Next 'When ADDed an Item with a String Key 'we can use the Key value to retrieve the Items Randomly. 'But, usage of Key is optional. ABC.Add 25, "1" ABC.Add "ms-Accesstips", "2" x = "2" Debug.Print ABC(x) Result: ms-accesstips

So, we will use Collection Object to add the Query Auto-Numbers with the Unique Key Values as Collection Object Key.  With this approach we can retrieve the Auto-Numbers directly, rather than struggling with Arrays and it’s complicated storing/retrieving steps.

The QryAutoNum() Function Code.

Option Compare Database
Option Explicit

Dim C As Collection

Public Function QryAutoNum(ByVal KeyValue As Variant, ByVal KeyfldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query Column Ver.-2
'Author : a.p.r. pillai
'Date : Dec. 2019
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be UNIQUE Numeric/String Type Values from Query Column
'2 : Column Name  - the Field Name in Quotes from where Unique Values taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with Unique Field Values
'            - as First Parameter
'            - Need to Save the Query, if changes made, before opening
'            - in normal View.
'-------------------------------------------------------------------
Static K As Long, Y As Long, fld As String
On Error GoTo QryAutoNum_Err

Y = DCount("*", QryName) ' get count of records for control purpose

'If KeyfldName Param is different from saved name in variable: fld
'or Value in K more than count of records in Variable: Y
'then it assumes that the QryAutoNum() is called from a different Query
'or a repeat run of the same Query. In either case the Control Variable
'and Collection Object needs re-initializing.
If KeyfldName <> fld Or K > Y Then
'initialize Control Variable
'and Collection Object
    K = 0
    Set C = Nothing
    'save incoming KeyfldName
    fld = KeyfldName
End If

'if KeyValue parameter is Numeric Type then convert
'it to string type, Collection Object needs it's Key as String Type.
If IsNumeric(KeyValue) Then
    KeyValue = CStr(KeyValue)
End If

K = K + 1
If K = 1 Then
Dim j As Long, db As Database, rst As Recordset
Dim varKey As Variant

Set C = New Collection

Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)

'Add recordlevel AutoNumber with Unique KeyValue
'to Collection Object, in AutoNumber, KeyValue Pair
While Not rst.BOF And Not rst.EOF
    j = j + 1 ' increment Auto Number
    
    'Get key value from record
    varKey = rst.Fields(KeyfldName).Value
    
    'if numeric key convert it to string
    If IsNumeric(varKey) Then
      varKey = CStr(varKey)
    End If
    
    'Add AutoNumber, KeyValue pair to Collection Object
    C.Add j, varKey
    
    rst.MoveNext
Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

'Retrieve AutoNumber from Collection Object
'using the KeyValue.  Works like Primary Key of Table
    QryAutoNum = C(KeyValue)
Else
    QryAutoNum = C(KeyValue)
End If

If K = Y Then 'All record level AutoNumbers are Returned
    K = K + 1 ' increment control variable
End If

QryAutoNum_Exit:
Exit Function

QryAutoNum_Err:
MsgBox Err & " : " & Err.Description, , "QryAutoNum"
Resume QryAutoNum_Exit

End Function

Sample Source Query SQL.

With the NorthWind Products Table.

SELECT Products.ID, 
Products.Category, 
Mid([Product Name],18) AS PName, 
Sum(Products.[Standard Cost]) AS StandardCost, 
QryAutoNum([ID],"ID",
"Product_AutoNumQ") AS QrySeq
FROM Products
GROUP BY Products.ID, Products.Category, Mid([Product Name],18)
ORDER BY Products.Category, Mid([Product Name],18);

Review of VBA Code Line-By-Line.

On the Global Area of the Module we have declared a Collection Object with Object Variable C.

The QryAutoNum() Function declaration is the same as our earlier QrySeq() with three Parameters.

  1. Unique Key Value either Numeric or String as first Parameter.
  2. The Key Value Field’s Name in String Format.
  3. The Query Name in String Format.

The returned Auto-Number is in Long Integer format.

Three Static Variables,  K and Y declared as Long Integers and fld declared as String Variable.

All the three Variables controls the Code execution paths and determines when to initialize Collection Object and control variables.

The DCount() Function takes a count of records in the Query in Variable Y.

If the KeyFldName is different from the saved name in Variable fld then it assumes that the function call is from a new Query Record.  If the field name is same but the value in variable K is greater than Y  then the earlier Query is calling the function QryAutoNum() for a repeat of the earlier run.   In either case the control Variable K is reset to zero, the Collection Object with existing Items are cleared from memory.  The new Key field name received in KeyFldName variable is saved in fld variable for later validation check.

Next, if the KeyValue parameter value is numeric then it is converted to String format in the statement: KeyValue = Cstr(KeyValue). The Collection Object Item Key must be in string format.

Next, the variable K is incremented by one.  When the value in K=1 it assumes that this is the first call of this function, from the first record of a Query.  When this is the case the main process of this function starts.

The local temporary Variables are declared here and their values are not preserved between calls of this function from different records of the query.

The Collection Object, declared on Standard Module’s  Global area is instantiated in memory, with the statement Set C = New Collection.

The Query recordset is opened to read records one-by-one. The local variable J to create Auto-Numbers and Add it to the Collection Object for each record.  The Unique Key Value, read from the recordset,  into variable varKey, is added to the Collection Object as it’s Key Value.

If the varKey Value is Numeric Type then it is converted to String format.

The Auto-Number Value in Variable J and the string value in variable varKey are added to the Collection Object in the following statement, as it’s Item, Key pair:

C.Add J, varKey

This process is repeated for all the records in the Query.  The Auto-Numbers are generated for all the records and added them into the Collection Object, one after the other.  All this work is done during first call of the function from the first record of the query.

Did you notice that we are reading the Unique Key value of each record directly from the recordset within the While . . . Wend Loop to add them to the Collection Object.  After adding Auto-Numbers for all records the recordset and Database Objects are closed.

Remember, we are still at the first call of the function from the first record of the query and the first parameter variable KeyValue still holds the first record Key Value.

The next statement QryAutoNum = C(KeyValue) retrieves Collection Object’s first Item Auto-Number Value 1, using the Unique Key Value in parameter variable KeyValue, and returns it to the function calling record. This will happen only once because the variable K will be greater than one on subsequent calls of this function.

So, the Function calls from second record onwards will take the ELSE path of the If K=1 Then statement and retrieves the Auto-Numbers from Collection Object, using the KeyValue passed as Parameter, and returns it to respective records in the Query.

It works very fast because we can directly pick the item value, using the Collection Object Key, rather than searching for the Key, through the Array from the beginning to find the one we want.

When all the record Auto-Number values are returned the value in control variable K = Y. We have already taken the count of records of the Query, in Variable Y in the beginning of the program. At this point we increment the value in variable K by 1, to make it more than the value in Variable Y.  Since, K and Y are Static Variables their values are not lost after the last record call is over and remains in memory.  If the same Query is run a second time the test on these variable values can determine whether we need to reset the variable values and clear the earlier Collection Object from memory for a fresh start of the process all over again.

If the QryAutoNum() function is called from the same Query again the Static Variables and Collection Object is cleared from memory, preparing for a fresh run of the Function for the same Query or for a different Query.

The sample Report Image using the above Query as Source is given below for reference

You can use the Query as Source for Report or Form. 

A sample demo database, with all the Objects and VBA Code, is attached for Downloading and for trying out the Code.


  1. Auto-Numbering in Query Column
  2. Product Group Sequence with Auto-Numbers.
  3. Preparing Rank List.
  4. Auto-Number with Date and Sequence Number.
  5. Auto-Number with Date and Sequence Number-2.
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Empty Gadget

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports Downloads msaccess tips Accesstips Objects Collection Object Property Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work VBA msaccess How Tos Combo Boxes Dictionary Object Graph Charts List Boxes Query msaccessQuery Calculation Command Buttons Emails and Alerts Form Report Command Button Data RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Fields Item Key msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances Macros Menus Microsoft Records Recordset SubForm Table Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Mail Merge Methods Numbering System Security Split 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 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

Word Mail-Merge with Ms-Access Table

Introduction. Form Letters. Form Letters are prepared from a standard template merged with addressee related information filled in on each l...

Labels

Blog Archive

Recent Posts