Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access and Mail Merge-3

Continuation of MS-Access and Mail Merge-2

Continued from the previous post, MS-Access and Mail Merge-2.

MAIL MERGE IN MS-ACCESS

We have already designed and tried the Form Letter preparation procedure through the earlier article. We need all those Objects for the Mail Merge in Access and here we have less work to do. I hope you understood the intricacies of the procedure explained there and how all those objects and methods work together to prepare the Form Letter. Since we are through with the major designing tasks already; we can concentrate on the implementation of the Mail Merge method with some minor changes in two or three places.

I will give a few examples below as how to insert Field Values from the Report source Query/Table and how to use Built-in Functions in the letter body text on the editing Form. The user needs only a few simple rules to keep in mind to use the Mail Merge method.

I will insert the Main Program Codes at the end of this Article. You may Copy and Paste them into the appropriate locations in your Project as suggested there.

Usage of Field Values to merge with the Text:

  • Data Field Names must be enclosed in square brackets [] and there should not be any typographical errors in Field Names.

  • If field names are placed next to each other, use at least one space between them, but can be used without space also.

  • Field Names must be picked from the Report Source Data Table/Query only and can be inserted anywhere in the text.

  • Data field values from another Table/Query can be inserted with the help of built-in Functions (like DLOOKUP()), see the usage of built-in functions given below.

  • If Field Names are joined with & + ' * / (for character or numerical values) it will not be treated as an expression, instead, the symbol will appear between the field values. (See Usage of built-in functions for expressions involving Field Values).

Usage Example -1: A Statement of Year-wise special Sales Incentive, credited to [TitleofCourtesy] [Firstname] [LastName]'s personal Account, is given below for information. His request for transfer to his hometown [City]&[Zip] has been approved.

Output: A Statement of Year-wise special Sales Incentive, credited to Dr. Andrew Fuller's personal Account is given below for information. His request for transfer, to his hometown, Tacoma9801 has been approved.

NB: The inserted text will not appear in bold as shown above, it is used here for highlighting the results only.

Usage of Built-in Functions to Merge the result with the Letter text:

  • Built-in Functions must be enclosed in {} brackets.

  • Nested Functions should have only the outermost pair of {} brackets, like in {Format(Date(),"mmm-yy")}

  • Data Field Names enclosed in [] can be inserted as parameters for Built-in Functions, like DLOOKUP().

  • Expressions involving Data Field Names enclosed in [] can be joined with + - * / symbols in numerical expressions, & in text data expressions and must be inserted as parameters to the built-in Functions.

Example: To find the 90 days credit period expiry date: {Format([InvoiceDate]+90,"dd/mm/yyyy")}

Usage Example-2: A statement of Year-wise special Sales incentive, of [TitleofCourtesy] [FirstName] [LastName], for a sum of ${DLOOKUP("TotalAmt","Yearwise_Incentive","EmployeeID = " & [EmployeeID])} is credited into his personal Account during {Format(DateAdd("m",-1,Date()),"mmm-yyyy")}, is given below for information. His request for transfer to his home town [City], has been approved effective {Date()+15}.

Output: A statement of Yearwise special Sales incentive of Dr. Andrew Fuller, for a sum of $8696.41 credits into his personal Account during Sep-2007, is given below for information. His request for transfer to his home town Tacoma has been approved effective 30/10/2007.

Don'ts:

[] And {} brackets should not be used within the letter body text anywhere other than enclosing Field Names and Built-in Functions respectively.

NB: A simple validation check is performed on the input text for matching pairs of [] and {} and if you leave out one closing ] in one Field and one opening [ in a different Field the validation check will not detect it and you will end up with Errors. In that case, find out the errors, correct them, and re-run.

Always print a trial run page of the letter and check the output thoroughly for accuracy before the final print.

We need to make a few changes to the Objects as a final step, the Cross-tab Query, the Letter editing Screen, and the Report Design.

  1. Copy and paste the SQL String given below into the SQL window of the Yearwise_FreightQ1 Query that we created earlier overwriting the old SQL String and saving the Query. This change is to create a new column for the Total Amount of 1996, 1997 & 1998 figures.

TRANSFORM Sum(Yearwise_FreightQ0.Sales) AS SumOfSales
SELECT Yearwise_FreightQ0.EmployeeID,
 Sum(Yearwise_FreightQ0.Sales) AS TotalAmt
FROM Yearwise_FreightQ0
GROUP BY Yearwise_FreightQ0.EmployeeID
PIVOT Yearwise_FreightQ0.Year;
  • After saving the Yearwise_FreightQ1 run the Make-table Query: Yearwise_IncentiveQ by double-clicking on it to create the output table Yearwise_incentive with the new column TotalAmt, which we have used within the DLOOKUP() Function in the usage Example-2 above.

  • Open the letter editing Form (Letter) in the design view, and create a Combo Box to display the Report Source Data Field Names, which the Users can reference and type correctly in the Body Text. See the image below:

    Turn off the Control Wizards (the Magic Want Symbol) on the Toolbox, if it is On, before clicking on the combo box control on the toolbox. Draw a Combo Box on the Form anywhere convenient on the design surface (I have placed it on the left side of the body text area). Display the Property Sheet of the Combo Box and change the following property values:

    • Name = cboFields

    • Row Source Type = Field List

    • Row Source = LetterQ

    • Column Count = 1

    • Column Width = 1"

    • List Rows = 8

    • List Width = 1"

  • Copy and Paste the following revised Code into the VB Module of the above Form (Letter) for the cmdPreview Button:

    Private Sub cmdPreview_Click()
    On Error Resume Next
    Me.Refresh
    DoCmd.OpenReport "Letter", acViewPreview
    
    If Err > 0 Then
      MsgBox "Errors in Report source Data."
      Err.clear
    End If
    
    End Sub
  • Save the Form with the above change.

  • Open the Report named Letter in design view. Click on Para1 text box control and remove the field name Para1 from the Control Source property. Change the Name property to Para1x.

  • Remove the Field Name Para2 of the second Text Box's Control Source property and change its Name Property to Para2x.

    Both Controls now show as Unbound Text Boxes.

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

    Private Sub Report_Open(Cancel As Integer)
    Dim xPara1, xPara2, ErrFlag1 As Boolean
    Dim ErrFlag2 As Boolean, x
    
    On Error Resume Next
    
    xPara1 = DLookup("Para1", "LetterQ")
    xPara2 = DLookup("Para2", "LetterQ")
    
    'submit para1 for parsing
    ErrFlag1 = False
    x = MailMerge(xPara1)
    Me![Para1x].ControlSource = x
    
    If Err > 0 Then
        ErrFlag1 = True
        Err.Clear
    End If
    
    'submit para2 for parsing
    ErrFlag2 = False
    x = MailMerge(xPara2)
    Me![Para2x].ControlSource = x
    
    If Err > 0 Then
        ErrFlag2 = True
        Err.Clear
    End If
    
    If ErrFlag1 Or ErrFlag2 Then
       MsgOK "Errors Found, Correct them and re-try."
    End If
    
    End Sub
  • Save the Report after the changes.

  • Open a new Global VB Module in your Project. Copy and paste the following Main Programs and save the Module. The lines of code above the Function MailMerge() are Global Declarations and must appear at the topmost area of the Module.

    Type ParaTxt
        text As Variant
        status As Boolean
    End Type
    
    Type SpecRec
        LsStart As Integer
        Lsend As Integer
        LfStart As Integer
        Lfend As Integer
        Str As String
        fun As String
    End Type
    
    Dim V As ParaTxt, DatF() As SpecRec, DatF2() As SpecRec
    
    Public Function MailMerge(ByVal inpara) As String  
    '------------------------------------------------------  
    'Author : a.p.r. pillai  
    'Date   : 01-10-2007  
    'Remarks: Scan and Parse Text  
    '------------------------------------------------------
    Dim i As Integer, k As Long, L As Long
    Dim i2 As Integer, xpara, ypara, yxpara
    Dim j As Integer, xchar As String
    Dim qot As String, size As Long
    Dim curlbon As Boolean
    
    On Error GoTo MailMerge_Err
    
    yxpara = inpara
    
    V.text = inpara
    V.status = True
    
    qot = Chr$(34)
    
    strValidate 'run validation check
    
    If V.status Then
      MailMerge = yxpara
      Exit Function
    End If
    
    'scan for Merged Fields
    'ignore if embedded within built-in Function
    
    xpara = V.text
    
    i = 0
    For j = 1 To Len(xpara)
      xchar = Mid(xpara, j, 1)
      If xchar = "{" Then
         curlbon = True
      End If
      If xchar = "[" And curlbon = False Then
         i = i + 1
      ElseIf xchar = "}" And curlbon = True Then
          curlbon = False
      End If
    Next
    
    If i > 0 Then
      i = i + 1
      ReDim DatF2(1 To i)
    Else
      GoTo chkFunction
    End If
    
    'Parse embedded fields
    L = 1: curlbon = False
    For j = 1 To Len(xpara)
      If j = 1 Then
        DatF2(L).LsStart = 1
      End If
    
      xchar = Mid(xpara, j, 1)
      If xchar = "{" Then
         curlbon = True
      End If
      If xchar = "[" And curlbon = False Then
        DatF2(L).Lsend = j - 1
           size = DatF2(L).Lsend - DatF2(L).LsStart + 1
           DatF2(L).Str = Mid(xpara, DatF2(L).LsStart, size)
           DatF2(L).LfStart = j
      End If
      If xchar = "]" And curlbon = False Then
           DatF2(L).Lfend = j
           size = DatF2(L).Lfend - DatF2(L).LfStart + 1
           DatF2(L).fun = Mid(xpara, DatF2(L).LfStart, size)
           L = L + 1
           DatF2(L).LsStart = j + 1
      End If
      If xchar = "}" And curlbon = True Then
          curlbon = False
      End If
    
    Next
    DatF2(L).Str = Mid(xpara, DatF2(L).LsStart)
    DatF2(L).fun = ""
    
    'create output from parsed string
    ypara = ""
    For j = 1 To L - 1
      If j = 1 Then
        ypara = DatF2(j).Str & qot & " & " & DatF2(j).fun
      Else
        ypara = ypara & " & " & qot & DatF2(j).Str & qot & " & " & DatF2(j).fun
      End If
    Next
    
    ypara = ypara & " & " & qot & DatF2(j).Str
    If Len(DatF2(j).fun) > 0 Then
       ypara = ypara & qot & " & " & DatF2(j).fun
    End If
    
    xpara = ypara
    
    chkFunction:
    
    'scan for embedded built-in functions
    i2 = 0
    For j = 1 To Len(xpara)
      If Mid(xpara, j, 1) = "{" Then
        i2 = i2 + 1
      End If
    Next
    
    If i2 > 0 Then
      i2 = i2 + 1
      ReDim DatF(1 To i2)
    Else
      GoTo Finish
    End If
    
    'parse built-in functions
    L = 1
    For j = 1 To Len(xpara)
      If j = 1 Then
        DatF(L).LsStart = 1
      End If
      If Mid(xpara, j, 1) = "{" Then
        DatF(L).Lsend = j - 1
           size = DatF(L).Lsend - DatF(L).LsStart + 1
           DatF(L).Str = Mid(xpara, DatF(L).LsStart, size)
        DatF(L).LfStart = j + 1
      End If
      If Mid(xpara, j, 1) = "}" Then
        DatF(L).Lfend = j - 1
           size = DatF(L).Lfend - DatF(L).LfStart + 1
           DatF(L).fun = Mid(xpara, DatF(L).LfStart, size)
        L = L + 1
        DatF(L).LsStart = j + 1
      End If
    Next
    DatF(L).Str = Mid(xpara, DatF(L).LsStart)
    DatF(L).fun = ""
    
    'format the paragraph
    ypara = ""
    For j = 1 To L - 1
      If j = 1 Then
        ypara = DatF(j).Str & qot & " & " & DatF(j).fun
      Else
        ypara = ypara & " & " & qot & DatF(j).Str & qot & " & " & DatF(j).fun
      End If
    Next
    
    ypara = ypara & " & " & qot & DatF(j).StrIf
     Len(DatF(j).fun) > 0 Then
       ypara = ypara & qot & " & " & DatF(j).fun
    End If
    
    Finish:
    
    'if there is no value for merging then
    If i2 = 0 And i = 0 Then
      ypara = yxpara
    End If
    
    xpara = "=" & qot & ypara & qot
    
    MailMerge = xpara
    
    MailMerge_Exit:
    Exit Function
    
    MailMerge_Err:
    MsgBox Err.Description, , "MailMerge()"
    MailMerge = ""
    Resume MailMerge_Exit
    End Function

    Public Function strValidate()  
    '------------------------------------------------------  
    'Author : a.p.r. pillai  
    'Date   : 01-10-2007  
    'Remarks: Pre-parsing validation check  
    'Returned Valule = False, if no errors in Expressions  
    '------------------------------------------------------   
    Dim xpara, j As Long, xchar As String   
    Dim msg As String, flag As  Boolean   
    Dim SBopen As Integer, SBCIose As Integer   
    Dim CBopen As Integer, CBclose As Integer   
    Dim str1 As String, str2 As String
    
       On Error GoTo strValidate_Err
    
        xpara = V.text
        xpara = Trim(xpara)
    
        SBopen = 0: SBCIose = 0
        CBopen = 0: CBclose = 0
        str1 = "missing for built-in Function(s)."
        str2 = "missing for Fieldname(s)."
    
        For j = 1 To Len(xpara)
            xchar = Mid(xpara, j, 1)
           Select Case xchar
                Case "["
                    SBopen = SBopen + 1
                Case "]"
                   SBCIose = SBCIose + 1
                Case "{"
                    CBopen = CBopen + 1
                Case "}"
                    CBclose = CBclose + 1
            End Select
        Next
        msg = ""
        If SBopen = SBCIose Then
           GoTo nextstep
       Else
           If SBopen > SBCIose Then
             msg = "1. Closing ] " & str2
             flag = True
           Else
             msg = "1. Opening [ " & str2
             flag = True
          End If
       End If
    nextstep:
        If CBopen = CBclose Then
           GoTo FinalStep
        Else
           If CBopen > CBclose Then
             If flag Then
              msg = msg & vbCr & "2. Closing } " & str1
             Else
               msg = "1. Closing } " & str1
             flag = True
             End If
           Else
            If flag Then
                msg = msg & vbCr & "2. Opening { " & str1
            Else
                msg = "1. Opening { " & str1
               flag = True
             End If
           End If
       End If
    
    FinalStep:
       If flag Then
          msg = "Errors found in field/function definitions." & vbCr & vbCr & msg & vbCr & vbCr & "Program Aborted. " & vbCr & "Correct the errors and re-try."
          MsgBox msg
          V.status = True
        Exit Function
       End If
    
      V.status = False
    
    strValidate_Exit:
    Exit Function
    
    strValidate_Err:
    MsgBox Err.Description, , "strValidateQ"
    strValidate = True
    Resume strValidate_Exit
    End Function
  • In Case of Errors

    If you end up with errors when you Compile/Run the Programs for the first time, try linking the essential Library Files to your Project and try again. Visit the Page Command Button Animation for a list of Library Files and for help linking those files to your Project.

    The Validation Program performs a simple validation check on the input data and gives out warnings if something is found not in order.

    Open the Text editing screen: Letter and try out the examples explained above by inserting Field Names, Built-in Functions, and Functions that use Field Values as parameters and click the Preview Command Button to open the Report with merged values and check whether the output is coming correctly as expected.

    Even though the program is very simple in its implementation it is very effective within the controlled environment, User friendly, and will be a Powerful Tool in your Projects.

    Any suggestions for improvement of the program are welcome.

    Downloads



    Share:

    No comments:

    Post a Comment

    Comments subject to moderation before publishing.

    PRESENTATION: ACCESS USER GROUPS (EUROPE)

    Translate

    PageRank

    Post Feed


    Search

    Popular Posts

    Blog Archive

    Powered by Blogger.

    Labels

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