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 tested the Form Letter preparation process in the earlier article. All the objects created during that process will also be required for implementing Mail Merge in Access, which means there is now less work involved. I hope you have understood the intricacies of the procedure and how the various objects and methods work together to generate the Form Letter. Since the major design tasks are already complete, we can now focus on implementing the Mail Merge functionality with just a few minor adjustments in two or three places.
Below are a few examples demonstrating how to insert field values from the report's source query or table and incorporate built-in functions directly into the body of the letter using the editing form. To use the Mail Merge method effectively, the user simply needs to follow a few basic rules.
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 they can be placed without a space too.
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 using 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, 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 used as parameters to Built-in Functions, like DLOOKUP().
Expressions involving data field names enclosed in square brackets (
[ ]) can be used with arithmetic operators (+,-,*,/) in numerical calculations, and with the ampersand (&) for text concatenation. When using built-in functions, the field references must be passed as parameters within the function.
Example: To calculate the expiry date of a 90-day credit period, use the following expression:
{Format([InvoiceDate] + 90, "dd/mm/yyyy")}
Usage Example-2:
A statement of year-wise special sales incentive for [TitleOfCourtesy] [FirstName] [LastName], amounting to ${DLOOKUP("TotalAmt", "Yearwise_Incentive", "EmployeeID = " & [EmployeeID])}, has been credited to his personal account during {Format(DateAdd("m", -1, Date()), "mmm-yyyy")}. His request for transfer to his hometown, [City], has been approved effective {Date() + 15}.
Output: A statement of Year-Wise special Sales incentive for Dr. Andrew Fuller, amounting to $8696.41, has been credited to his personal Account during Sep-2007. His request for transfer to his hometown, Tacoma, has been approved effective 10/30/2007.
Don'ts:
Note: Square brackets [ ] and curly braces { } should only be used in the letter body to enclose field names and built-in functions, respectively. They should not be used elsewhere in the text.
Note: A basic validation check is performed on the input text to ensure matching pairs of square brackets [ ] and curly braces { }. However, if you accidentally leave out a closing bracket ']' in one field and an opening bracket '[' In another, the validation may not catch the error, resulting in unexpected issues during processing. In such cases, carefully review and correct the text before rerunning the report.
Always print a trial copy of the letter and thoroughly verify the output for accuracy before proceeding with the final print.
As a final step, we will make a few adjustments to the following components:
-
The Crosstab Query
-
The Letter Editing Form
-
The Report Design
Update the SQL of the Yearwise_FreightQ1 Query:
Open the Yearwise_FreightQ1 Query in SQL View, delete the existing SQL string, and copy and paste the updated SQL string provided below into the SQL window. This modification will add a new column that calculates the Total Amount by summing the sales figures (Freight values) for the years 1996, 1997, and 1998.
After pasting the SQL, save the query.
Note: Ensure you overwrite the old SQL code completely to avoid syntax errors.
Open the Mail editing Form (Letter) in 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:
Inserting a Combo Box for Field Name Reference
To help users insert field names accurately into the letter body text, follow these steps:
-
Turn Off Control Wizards
On the Toolbox, make sure the Control Wizards (the magic wand icon) is turned off.Click the wand icon to toggle it off before inserting the Combo Box.
-
Insert the Combo Box
-
Click on the Combo Box control in the Toolbox.
-
Draw the Combo Box on the form — place it anywhere convenient, such as to the left of the body text area (
Para1andPara2).
-
-
Set Combo Box Properties
With the Combo Box selected, open the Property Sheet (F4If not visible) and update the following properties:
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 the 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 from 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 VBA Code into the VBA 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 SubSave the Report after the changes.
Open a new Global VBA 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 FunctionPublic 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
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.
In Case of Errors
Note: If you encounter errors while compiling or running the program for the first time, it may be due to missing library references. To fix this, ensure all essential library files are correctly linked to your project.
For a list of required library files and step-by-step guidance on linking them, visit the Page Command Button Animation article.
The validation program performs a basic check on the input data and displays warnings if any inconsistencies or errors are detected.
Open the letter editing form (Letter) and test the examples described earlier by inserting field names, built-in functions, or expressions using field values as parameters. Click the Preview command button to generate the report and verify that the merged output appears correctly.
Although simple in design, this program is highly effective within its controlled environment. It is user-friendly and serves as a powerful tool for enhancing your projects.
Any suggestions for improvement of the program are welcome.
Download Mailmerge.zip
ReminderPopup.zip
