Continuation of MS-Access and Mail Merge-2
Continued from 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 into 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 in 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.
- Use of at least one space between field names is recommended, if they are placed next to each other, 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 other 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 A/c., is given below for information. His request for transfer to his home town [City]&[Zip] has been approved.
Output: A Statement of Year-wise special Sales Incentive, credited to Dr. Andrew Fuller's personal A/c., is given below for information. His request for transfer to his home town Tacoma&9801 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 A/c. 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 Year-wise special Sales incentive of Dr. Andrew Fuller, for a sum of $8696.41 is credited into his personal A/c. 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.
- Copy and paste the SQL String given below into the SQL window of Yearwise_FreightQ1 Query that we have created earlier overwriting the old SQL String and save 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;
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 at 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"
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
Both Controls now show as Unbound Text Boxes.
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
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 on linking those files to your Project.The Validation Program performs a simple validation check on the input data and gives out warnings if something found not in order.
Open the Text editing screen: Letter and try out 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.
No comments:
Post a Comment
Comments subject to moderation before publishing.