Dynamic Report

Introduction.

Designing reports in Microsoft Access is quite straightforward, as we already know. Once a process is in place—using macros or programs to prepare the source data for a standard report—the rest becomes routine. You simply design the report and add it to the report list. After that, every time a user runs the report, it’s ready for preview or printing.

Although the underlying data may change based on parameters such as the reporting period, the report structure itself remains unchanged. There is no need to redesign the report or modify its data source, since all standard elements—such as the main heading, reporting period, preparation date, and page numbers—are already incorporated during the initial design phase.

We are now going to explore a type of report that cannot be planned using the usual rules or based on a fixed data structure. In this case, the structure of the source data is unpredictable—field names may change every time the report is generated. When the data structure is dynamic, you cannot permanently place field names in the report design, as is typically done.

To make matters more challenging, the number of fields included in the report may also vary. An even more complex requirement is calculating summary values—both at the group level and as grand totals in the report footer—when the structure and content of the data are constantly changing.

When someone asks for something difficult, the easy way out is to say it's not possible—and many users might accept that, especially if they’re unfamiliar with programming. But in the software world, when someone says “no,” it often just means they don’t yet know how to do it. I’ve been there myself. Still, once I say it can’t be done, the thought nags at me. I start asking: Can I really let it go, or is there a way to make it work?

Often, it just takes time—time to think, plan, organize, test a few ideas, and gradually build something functional. The number of steps doesn’t matter; what matters is the final result. Once it works, I can always go back and refine it. That said, there are situations where we must honestly accept the limits and stick to “no”—but only after we’ve explored every possibility.

Let’s get down to problem-solving instead of going in circles. I was just being a little philosophical earlier. Honestly, the task isn’t as big as it might sound now, especially after all that bragging.

Report Preparation.

Before diving into the Report preparation, let me first show you some sample data and the Report format we’ll be working with. The sample records are taken from two familiar tables in the Northwind.mdb database—Employees and Orders, which we’ve already used in earlier examples.

In the above table, the Shipped Date ranges from July 10, 1996, to May 6, 1998. Our task is to prepare a report formatted as shown below, organized Employee-wise, Year-wise, and Month-wise.

sample data table view

Click to Enlarge

When a sample Report is designed using the above data as a source, it looks like the image given below.

Click to Enlarge

The report is designed to display data for 12 months, with the data selection criteria set between 199607 (July 1996) and 199706 (June 1997) in the report’s underlying query. The Detail section includes the necessary fields, while the Report Footer contains controls configured with summary formulas to calculate month-wise totals. The field headings are labeled using month-year formats for clarity. Up to this point, everything functions smoothly — the report opens in Print Preview or prints without any issues.

Report Data Selection Criteria

However, when the data selection criteria are changed to target a different period, the report fails to run. It throws an error at the first field that does not match any of the existing TextBox control sources on the report layout and refuses to open in Print Preview or Print mode. This occurs because the field names in the source query no longer align with those hard-coded in the report design, making it unsuitable for handling dynamic or varying data structures.

An alternative approach is to create a Report Table with fixed field names such as M1, M2, through M12, representing data for January to December. An Append Query can be used to populate this table with the desired report data. The report itself can then be designed using these permanent field names, ensuring a consistent structure.

To dynamically reflect the reporting period in the field headers, we need a way to define header labels using formulas based on the report period parameters. However, this method introduces a constraint: the user must generate report data within a single calendar year—either for the full 12 months or a shorter period—without spanning across multiple years. This limitation ensures that the field mappings remain consistent and avoids mismatches during report generation.

If the user attempts a trial run with a date range that crosses over a calendar year, the data for the earlier months will appear at the right end of the report, while the data for the later months will be placed at the beginning. This reversal disrupts the logical flow of the report. Additionally, dynamically setting the field headings to correctly reflect the shifted months becomes another challenge to manage.

In the end, we cannot consider this a well-structured report—or to put it plainly, it is not user-friendly. The lack of alignment between the data layout and the user's expectations diminishes the overall usability of the report.

Sample Data Preparation Queries.

We found a practical remedy for this issue by introducing a small VBA routine in the report’s code module. This routine dynamically adjusts the final layout of the report based on the structure of the underlying source data. It is executed automatically each time the report is opened for preview or print, ensuring the design always aligns with the current dataset, regardless of how the fields or reporting period vary.

1. To get started with the program, import the Employees and Orders tables from the Northwind.mdb sample database, if they are not already available in your project. If you're unsure about the location of the Northwind database, refer to the Saving Data on Forms, Not in the Table page for detailed guidance on where to find it.

2. Copy and paste the following SQL string into the SQL View of a new query, and save the query with the name indicated below:

   Query Name: FreightValueQ0
   
   SELECT [FirstName] & " " & [LastName] AS EmpName,
    Val(Format([ShippedDate],"yyyymm")) AS yyyymm,
    Orders.Freight
   FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
   WHERE (((Val(Format([ShippedDate],"yyyymm"))) Between 199607 And 199706));
   
   Query Name : FreightV_CrossQ
   TRANSFORM Sum(FreightValueQ0.Freight) AS SumOfFreight
   SELECT FreightValueQ0.EmpName
   FROM FreightValueQ0
   WHERE (((FreightValueQ0.yyyymm)<>""))
   GROUP BY FreightValueQ0.EmpName
   PIVOT FreightValueQ0.yyyymm;

   Report Design Task

3. After creating the above queries one by one, design a Report based on the FreightV_CrossQ query as its data source. A sample layout of the Report design is shown below.

   

   Click to Enlarge

4. In the Detail section of the Report, create 13 Text Box controls. Make the leftmost Text Box wider to accommodate the Employee’s Name, and use the remaining 12 boxes for monthly data (Month 1 to Month 12). Rename the Text Boxes using the Name property as follows: M00 for the Employee Name field, and M01 through M12 For the monthly fields, moving left to right. Ensure that the Control Source property of all these Text Boxes remains empty at this stage.

5. In the Report Footer Section, create 13 TextBox controls and name them from T00, T01 to T12. Leave the Control Source Property empty.

6. In the Header Section, create 13 Label Controls, name them L00, L01 to L12, and leave the Caption Property empty.

7. Create a Label at the Top and set the Caption property value to EMPLOYEE-WISE FREIGHT VALUE LISTING.

8. While the Report is still in design view, select the Save option from the File menu and save the Report with the name FreightVal_Rpt.

9. Select Code from the View menu to display the VBA Module of the Report.

10. Copy and Paste the following Code into the VBA Module:

    Automating Report Control's Data Reference Setting.

    Private Sub Report_Open(Cancel As Integer)
    Dim db As Database, Qrydef As QueryDef, fldcount As Integer
    Dim rpt As Report, j As Integer, k As Integer
    Dim fldname As String, ctrl As Control, dtsrl As Date
    Dim strlbl As String, fsort() As String
    
    On Error GoTo Report_Open_Err
    
    Set db = CurrentDb
    Set Qrydef = db.QueryDefs("FreightV_CrossQ")
    fldcount = Qrydef.Fields.Count - 1
    
    If fldcount > 12 Then
       MsgBox "Report Period exceeding 12 months will not appear on the Report."
       fldcount = 12
    End If
    
    Set rpt = Me
    
    ReDim fsort(0 To fldcount) As String
    For j = 0 To fldcount
        fldname = Qrydef.Fields(j).Name
        fsort(j) = fldname
    Next
    
    'Sort Field names in Ascending Order
    For j = 1 To fldcount - 1
        For k = j + 1 To fldcount
            If fsort(k) < fsort(j) Then
                fsort(j) = fsort(k)
            End If
        Next
    Next
    
    For j = 0 To fldcount
    'Monthwise Data    
    Set ctrl = rpt.Controls("M" & Format(j, "00"))
        ctrl.ControlSource = fsort(j)
        Set ctrl = rpt.Controls("T" & Format(j, "00"))
        If j = 0 Then
            ctrl.ControlSource = "=" & Chr$(34) & " TOTAL = " & Chr$(34)
        Else
            ctrl.ControlSource = "=SUM([" & fsort(j) & "])"
        End If
    
    'Header labels
    If j = 0 Then
        Me("L" & Format(j, "00")).Caption = "Employee Name"
    Else
        dtsrl = DateSerial(Mid(fsort(j), 1, 4), Right(fsort(j), 2), 1)
        strlbl = Format(dtsrl, "mmm-yy")
        Me("L" & Format(j, "00")).Caption = strlbl
    End If
    Next
    
    Report_Open_Exit:
    Exit Sub
    
    Report_Open_Err:
    MsgBox Err.Description, , "Report_0pen()"
    Resume Report_Open_Exit
    End Sub

11. After copying the code, minimize the VBA Editor window and open the Property Sheet of the Report. Check whether the On Open property is set to [Event Procedure]. If it is not, the code has not been correctly linked to the event. In that case, select [Event Procedure] from the drop-down list, then click the Build button (...) to open the code window.

    In the code module that appears, you will see an empty Report_Open() subroutine. Cut the code you previously pasted (excluding the top and bottom lines that were copied from the web page), and insert it within this Report_Open() ... End Sub block. Finally, remove any orphaned or duplicate lines, save the changes, and close the editor.

    Print Previewing the Report.

12. Open the Report in Print Preview. The Report should display the values from the source Query, with the correct heading labels and the Report Footer summary calculated accurately.

13. Open the first Query in Design View and modify the criteria to set different date ranges, ensuring that the selected range does not exceed 12 months (a shorter period is acceptable). Then, run the Report to verify the output.

If the selected period exceeds 12 months, the Report will display a message indicating that the selection exceeds the allowed range. It will then proceed to open with the data that fits within the maximum number of available fields.

If the selected period is less than 12 months, the unused rightmost controls will remain blank. In either case, the Report will still open, allowing the user to view the available contents.

Download Demo Database.

Download Demo Database

• Highlighting Reports
• Saving Data on Forms Not in a Table
• Edit Data in a Zoomed Control - Access 2003
• Custom Menus and Tool Bars-2 - Access 2003
• Custom Menus and Tool Bars -Access 2003

Read More

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 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, as well as how to incorporate built-in functions directly into the body text of the letter using the editing form. To effectively use the Mail Merge method, 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 in between, 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 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, 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 square brackets ([ ]) can be used with arithmetic operators (+, -, *, /) in numerical calculations, and with the ampersand (&) for text concatenation. When using built-in functions, these 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:

1. 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.

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.

2. 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:

   

   Click to Enlarge

   🧭 Inserting a Combo Box for Field Name Reference

   To help users insert field names accurately into the letter body text, follow these steps:

   1. 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.

   2. 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 (Para1 and Para2).

   3. Set Combo Box Properties
      With the Combo Box selected, open the Property Sheet (F4 If 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"

3. 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

4. Save the Form with the above change.

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

6. 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.

   

   Click to Enlarge

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

8. Save the Report after the changes.

9. 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 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

Note: If you encounter errors when you compile or run 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 as expected.

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.

Downloads


Download Mailmerge.zip




• Saving Data on Forms Not in a Table
• Edit Data in a Zoomed-in Control
• Custom Menus and Tool Bars-2
• Custom Menus and Tool Bars
• Calendar and Toolbars

Read More

MS-Access and Mail Merge-2

Form Letters

A form letter can serve various purposes, such as an invitation to club members, a notice to company shareholders, a circular to department heads, or a forwarding memo for departmental telephone expense statements. In all these cases, the body of the letter remains the same, while the recipient’s details vary from page to page.

When planning a form letter in Microsoft Access, it's essential to organize the following five elements:

1. Design a Letter Table.
   Create a table to hold the main content of the letter. This table should include fields such as Memo Reference, Date, Subject, Body Text, Footer information (e.g., Department Head's name), and other relevant details. Enter a single record into this table to serve as the letter's template.

2. Create a Letter Editing Form.
   Design a form based on the Letter Table to allow easy editing of the body text and other details. This makes updating the letter content straightforward whenever changes are needed.

3. Prepare a Sub-Report (if applicable).
   If the letter requires inserting dynamic content, like a department-wise telephone expenses statement, design and process the necessary sub-report. If no such statement is needed, this step can be skipped.

4. Join Letter and Address Data.
   Use a query to join the Letter Table with the Address Book or recipient list. This will allow you to merge personalized address data with the letter content.

5. Design the Main Form Letter Report.
   Finally, design the report that serves as the form letter. Insert the editable letter fields, recipient address fields, and any sub-reports (if used). Format the layout to suit your printing requirements.

   A sample image of the completed form letter we're aiming to create is shown below. Take a moment to review it—it will give you a clear understanding of the final result and how each step in the process contributes to building the letter.

Click to Enlarge

The Design Task

We need two sample tables, Employees and Orders, from the Northwind.mdb Database. (Refer to my earlier post 'Saving Data on Forms not in Table' for the exact location reference to the sample file), Import the above Tables from the Northwind.mdb database.

As you can see in the form letter above, the addressee information is drawn from the Employees table. The statement shown in the middle of the letter is generated from the Orders table. Both tables share a common field, EmployeeID, which is used to link the embedded statement to the corresponding employee. This ensures that the statement displayed in the letter pertains to the correct individual. All other content—including the memo reference, date, subject, body text, and footer details—is sourced from the Letter table mentioned earlier.

1. Design a Table with the following Field Structure and save it with the name Letter:
   

2. Open the Table in the 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 the Insert Menu, and select Design View from the displayed list.

4. Design a Form, like the sample Form image shown below.

Click to Enlarge

1. Create a macro to close the form by following these steps:

2. Select the Close button, open its Property Sheet, change its Name property to 'cmdClose', and assign a macro to its On Click event to close the form.

3. Next, select the Preview button, display its Property Sheet, and rename it to 'cmdPreview'. We will add a small VBA subroutine for this button later.

4. For the Subject text box, open its Property Sheet and set the Scroll Bars property to Vertical. Make the same change for the Para1  Para2 text boxes to allow scrolling for longer text.

5. Finally, save the form with the name Letter.

6. The Letter Image is given below with the sample data filled in:

   

   Click to Enlarge

   Inserting a Statement.

7. To prepare the embedded statement for the Form Letter (as shown in the earlier image), we will use three columns from the Orders table: EmployeeID, OrderDate, and Freight. For demonstration purposes, we will treat the Freight value as the Sales Figure for each employee and extract the year from the OrderDate field to represent the Sales Year.

   We will create three queries to build a Report Table. Follow these steps:

   1. Open a new query in SQL View (do not select any table when prompted).

   2. Copy and paste the SQL string provided for each query.

   3. Save each query with the name indicated after the code block.

   This setup will help organize the sales summary data by employee and year for use in the form letter.

   • 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;

   Note: Be careful when naming your Make-Table Query and the Target Table it creates. Do not give them the same name — doing so can cause errors or unexpected behavior when the query is executed.

   For example, in the query named Yearwise_IncentiveQ I’ve added a “Q” at the end to distinguish it from the table it creates. You are free to use any naming convention you prefer, as long as the query name and the target table name are not identical.

   In the first query, we extract data from the Orders table and create a year-wise summary of the Freight values, renaming it as Sales. The second query is a Crosstab Query, which transposes the data so that the years appear as column headers.

   However, since Crosstab Queries cannot be used directly in Reports, we use a Make-Table Query to store the transposed results in a new table named Yearwise_Incentive. This allows us to use the data easily in reports or for further processing.

8. After you have created and saved the third query, double-click on it to run. This will generate the output table named Yearwise_Incentive. If you receive any warning messages during the process, simply click OK to proceed.

9. Design a Report using the Yearwise_Incentive Table as the record source, following the layout shown in the sample image (refer to your earlier example). The report width should be approximately 6.5 inches.

   • Do not use the Page Header/Footer sections.

   • Place all field heading labels in the Report Header section.

   • Arrange the detail fields accordingly in the Detail section to align under their respective labels.

   • Once completed, save the report with the name: Yearwise_Incntv.

   
   

   Click to Enlarge

10. We need to create one final query to join the Employees table with the Letter table (created earlier to store the memo reference, subject, and body text).

    To do this:

    1. Open a new query in Design View, but do not add any tables yet.

    2. Switch to SQL View.

    3. Copy and paste the following SQL string into the SQL window:

       SELECT Letter.*,
        Employees.EmployeeID,
        Employees.TitleOfCourtesy,
        Employees.FirstName,
        Employees.LastName,
        Employees.Title,
        Employees.Address,
        Employees.City,
        Employees.Region,
        Employees.PostalCode,
        Employees.Country
       FROM Employees, Letter;

    4. Save the query with the name: LetterQ.

11. After saving the query, open it in Design View and examine how the Employees and Letter tables are arranged on the design surface. Note that the Letter table contains only one record, while the Employees table has nine. Since we haven't defined a join between the two tables, Access performs a Cartesian product, repeating the single record from the Letter table for each record in the Employees table. When you open the query in Datasheet View, you'll see the Letter content duplicated for every employee.

    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.

12. Here’s why this happens:

    • In your query LetterQ No JOIN condition is specified between the Employees table and the Letter table.

    • Because of that, Access performs a Cartesian Product (also called a cross join):

      Each record in the Employees table is combined with every record in the Letter table.

    • Since Letter contains only one record, the result is that this single letter content is repeated for each employee.

    What This Means in Practice:

    When you use this query as the Record Source for your form letter report:

    • Each page (or section) of the report will show one employee’s information,
      along with the same shared letter content from the Letter table.

    • This is exactly what we want when preparing a bulk personalized form letter —
      Each employee gets their copy of the same letter, but with their name, address, and individual details.

    ---

    ✅ Next Steps: You can now use the LetterQ query as the Record Source for your final report — the actual Form Letter Report.

13. Design the Main letter using the LetterQ query as shown below.

    Take a close look at the image. The Report does not use the Report Header/Footer or Page Header/Footer sections. Instead, the memo’s header is designed within the FirstName Header section. To create this section, click the Sorting and Grouping button on the toolbar, or choose Sorting and Grouping from the View menu. In the dialog that appears, select FirstName in the Field/Expression column. Then, in the Group Properties section below, set Group Header and Group Footer to Yes. Once the FirstName Header section appears in the report design, position your mouse over the top edge of the Detail section (the horizontal bar). When the pointer changes to a double-headed arrow, click and drag downward to create enough space for designing the memo's header layout.

    

    Click to Enlarge

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

    Inserting the Statement

    • To insert the Yearwise_Incntv statement into the main report, place it between Para1 and Para2, and link it to the EmployeeID field that was added separately in the FirstName Header section.

      To do this:

      1. Move the main report slightly to the right side of the screen, next to the Database Window, so both are visible side by side.

      2. If the Yearwise_Incntv report is not visible, click its tab to bring it forward.

      3. Click and drag the Yearwise_Incntv report into the Detail section of the main report, positioning it between the Para1 and Para2 text boxes.

      4. Align and size the inserted report properly to fit the layout as shown in the reference image.

      5. If the main report’s width has expanded to the right during this process, resize it appropriately to maintain the original 6.5" width.

      Make sure the Subreport control is properly linked using EmployeeID as the master and child field so that the correct data appears for each employee.

    • Click on the Yearwise_incntv statement, display the Property sheet, and insert EmployeeID in the 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

14. Open the report in Print Preview and use the Page Navigation controls at the bottom to move through the pages. As you advance, verify that the Employee Name displayed in the address section at the top of each page changes appropriately. Also, ensure that the Employee Name shown in the statement inserted between the paragraphs matches the one in the address section. Once you've confirmed that the data is displaying correctly, close the report.

15. Open the Letter Form in the design view.

16. 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()
       Me.Refresh
       DoCmd.OpenReport "Letter", acViewPreview
    End Sub

17. Open the Letter Form in a normal view. Make some changes in the body text. Click on the Preview button. The changes that you have made in the text should also be reflected in the previewed letter.

Your Report display should match the sample image given at the top of this page.

With this article, we have laid the groundwork for merging data field values and built-in functions into the body text of a letter. In the next step, we will explore how to incorporate these elements and print the final document.

Downloads.


Download Mailmerge.zip




• Edit Data in a Zoom-in Control
• Custom Menus and Tool Bars-2
• Custom Menus and Tool Bars
• Calendar and Toolbars
• Animated Floating Calendar

Read More

MS-Access and Mail Merge

Introduction

Address Labels

When we think of address labels, form letters, and mail merge tasks, Microsoft Word is usually the first tool that comes to mind, thanks to its wide range of features designed for these purposes. However, Word requires a data source to supply the content for address labels, form letters, and similar documents. You can either create a table directly within a Word document for recording address data or connect to an external source, such as a Microsoft Access table or another database.

Instead of using Microsoft Word for creating address labels, form letters, and performing mail merge operations in Microsoft Access. Yes, you read that right, we’ll explore how to handle Mail merge directly within Access itself.

While Access may not offer the advanced paragraph formatting features of Word, it remains highly effective for generating form letters, particularly in cases such as agency agreement renewals, Bank Guarantee renewal reminders, or department-wise monthly reports on stationery or telephone expenses. These letters usually consist of one or two standard paragraphs combined with actual data, which Access can quickly assemble and print with minimal effort.

Designing the Address Labels.

We’ll begin with a simple task: designing address labels. MS Access includes a built-in Label Wizard that makes it easy to create address labels of various sizes and layouts. It offers a wide selection of predefined label sizes from different manufacturers, making it ideal for producing hundreds or even thousands of labels on continuous stationery in standard formats.

Ordinary people like me who would like to print on plain paper or cut and paste on envelopes can try the manual method.

A sample image of the output created from a manually designed Address Label is shown below, and we will go through the procedure needed to prepare them:

Click to Enlarge

The Design Task

1. Import the Employees table from the Northwind.mdb sample database if you haven’t already done so in the earlier sessions. For guidance on locating the sample database, refer to my previous post.

   Saving Data on Forms, not in a Table, for the exact location reference of the sample file.

2. Select the Employees Table.

   Select Report from the Insert Menu and select Design View.

3. Go to the View menu and remove the checkmark from Page Header / Footer and Report Header / Footer options to remove them from the report design, if they are visible.

4. Draw a Text Box approximately 3.25" wide at the top and left area of the Detail Section, leaving enough space for the border as shown in the design given below:

   

5. Display the Property Sheet of the TextBox from the View menu and set the Special Effect Property = Chiseled. If you don't like the underlined design, stay with the Flat property setting.

6. Write the expression as shown above in the Control Source Property. The partially visible field is [LastName]; don't forget the closing brackets.

7. Copy the same Text Box, paste it four times down, and write the expression as shown above.

8. Select all five Text Boxes together. Select Vertical Spacing -> Decrease from the Format Menu. Repeat the process till all the TextBoxes come close together.

9. Draw a Rectangle around the TextBoxes. If the Rectangle hides the TextBoxes, then select Transparent from the Fill/Back Color Toolbar Button. When the Label is cut from plain paper, the Border will give the label a proper shape, even if the cutting is not.

10. Now, we must go to the final settings on the Page Setup Control. Select Page Setup from the File Menu.

11. Select the Columns Tab.

    

12. Change the Number of Columns to 2.

13. Column Spacing change to 0.15"

14. In Column-Size control, insert a checkmark in the Same as Detail if it is not appearing there.

15. Column Layout: Across, then Down.

16. Click OK and save the Report with the Name: Address Labels.

17. Open the report in Print Preview; it should resemble the sample image shown at the top of this page.

Next, we will look into setting up and Printing Form Letters.

• Custom Menus and Tool Bars-2
• Custom Menus and Tool Bars
• Calendar and Toolbars
• Animated Floating Calendar
• Startup Screen Design

Read More