Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Dynamic Report

Introduction

Report designing in MS-Access is very easy as we already know. Once we set up a procedure to run the macros/programs to prepare the data for a standard Report, all we need to do is to design the Report and add it to the Report List. Every time the user runs the report it is ready to Preview or Print. The Source data may change, based on the report period setting, but there will not be any change in the structure of the Report or source data or need any change in the Report design itself as all the standard labels, like the main heading, Report generated for which period, prepared date, Page Numbers, etc., are already taken care of as part of the initial report design task.

We are going to discuss a Report that is not so easy to plan on the above rules or based on a fixed source data structure. The structure of the source data is not predictable or shall we say field names of the source data can change every time we run this Report. If the data structure changes every time, then you cannot put the field names permanently on the report design as we normally do. Adding to the difficulty the number of fields for the report also may change. Even more difficult situation is to create Report Summary figures at Group-level and Report Footer level Grand-totals.

When someone asks for something difficult we can easily say it is not possible and the user may swallow it too, provided he/she doesn't know computer programming at all. In the Software world when someone says no to something, it simply means that he doesn't know how to do it, at least for the time being. I fall into this category too. But, then after saying that it keeps me disturbed, and start asking myself, can I write it off just like that or find a way somehow to do it? It may take a while to have a plan, get things organized, try something along those lines of ideas, and finally come up with something that works. Doesn't matter how many steps you have taken to achieve that. The final result is that matters. You can always go back and review the steps and refine them. But, there are times that we have to stick to the No answer too.

Let us get into problem-solving instead of beating around the bush. I was trying to be a little philosophical. It is not as big as you are imagining now, after listening to my bragging.

Report Preparation

Before getting into the Report preparation part, first I will show you some sample lines of data and the Report Format that we need to deal with. The sample data is taken from two familiar Tables from the Northwind.mdb sample database; Employees and Orders, which we have already used in earlier examples.

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

sample data table view

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

The Report contents should be for a period of 12 months and the data selection criterion is set for the period Between 199607 (July 1996) and 199706 (June 1997) in the data selection Query of the Report. In the Details section, the report fields are inserted. Report Footer controls are set with the Summary formula taking month-wise Totals. Field Heading Controls are defined with month-Year labels. So far so good, we can open the report in Preview or Print, with no issues.

Report Data Selection Criteria

But, when the data selection criteria change for a different period the Report will not run. It will show an error on the first field that does not match with the fields already placed on the Report TextBoxes and refuses to open to Preview or Print.

An alternative method is to create a Report Table with Field Names like M1, M2 to M12 for holding January to December Data, and use an Append Query to add the report data into this table and design a Report using these permanent field Names. We must find a way to define the field header labels with some formula taking the values of the report period parameter. The user can create report data for the full 12 months or a lesser period for a particular year. Then we are putting conditions on the user asking him to select data for a particular year only, not to cross from one year to the other when entering criteria.

If he/she goes for a trial run cross-over a period, then the data for the earlier period comes at the right end of the Report and the later period in the beginning. The field headings are another issue to take care of.  Finally, we cannot call it a good report or let us put it this way, is not a user-friendly report.

Sample Data Preparation Queries

We found a remedy for this issue by introducing a small program on the Report VBA Module to take care of the final stage designing task based on the source data structure, every time we open it to preview or print.

  1. To get prepared for the Program, Import the Tables Employees, and Orders from the Northwind.mdb sample database, if you don't have them. If you don't know the location of the sample database visit the Page Saving Data on Forms not in the table for location references.

  2. Copy and paste the following SQL String into new Query's SQL Window and save it with the Names as 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 after the other design a Report is shown below using FreightV_CrossQ Query Data Source.

  4. In the Detail Section of the Report create 13 Text Box Controls; make the leftmost one wider for the Employee's Name, and others month-1 to 12. Change the Name Property of the TextBoxes as M00, M01 to M12 from left to right (write two-digit numbers in the Name Property as 01, 02, etc., along with the prefix M). Leave the Control Source property empty.

  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 Header Section creates 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 Window, display the Property Sheet of the Report and check whether the On Open property is marked with the [Event Procedure] Value. If it is not, then the code we have pasted is not correctly loaded into this event. Select [Event Procedure] from the drop-down list and open the Code module, cut the Code (except the top and bottom lines that we have pasted from the Web page) and paste the Code within the newly opened Empty Report_Open() . . . End Sub lines, remove the orphaned lines, and save the Report.

    Print Previewing the Report.

  12. Open the Report in Print Preview. Your Report must be loaded with the Values from the Source Query with correct heading Labels and the Report Footer Summary.

  13. Open the First Query in the design view and change the Criteria values in different ranges, taking care that the Range of values are not exceeding 12 months (it can be less than 12), and try out the Report.

If the selected period exceeds 12 months the Report will open with the data that fits into the maximum number of fields, after showing a message indicating that the selected period exceeds 12 months.

If the selected period is less than 12 months, then the rightmost controls will be empty. In either case, the Report will open and we can view the contents.

Download Demo Database


Download Demo Database


Share:

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:

    MS-Access and Mail Merge-2

    Form Letters

    A Form Letter can be anything like an Invitation to the Members of a Club or a Notice to the Share Holders of the Company or a Circular to the Head of the Departments of the Organization or a forwarding memo of Departmental Telephone Expenses Statement and so on. In all these cases the body text of the letter will be standard, but the addressee information will change on every page of the document.

    When we plan for a Form Letter in Microsoft Access we must carefully organize five different elements of this task.

    1. Design a Table and add a single record to hold the main-letter contents like Memo Reference, Date, Subject, Body Text, and Letter Footer Information like Department Head who will sign the document, etc.

    2. Design a Form for the above Table for editing the Letter Body Text, and for changing the contents as and when it becomes necessary.

    3. Processing of data and designing a sub-report (like a statement on department-wise telephone expenses) if needed to insert into the body of the main letter. If this is not required then this step can be omitted.

    4. To select the required fields for the document contents, join the Address Book Table with the Letter table (created in step 1).

    5. Design the Main Form Letter.

      A sample image of the completed Form Letter, that we are going to prepare, is given below. Take a look at it so that you will have a general idea, of how we are progressing through the steps to create it:

    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.

    If you check the above letter the Addressee information is picked from the Employees Table and the statement appearing in the middle is prepared from the Orders Table. In both tables, EmployeeID is a common field and is used here to link the embedded statement with the EmployeeID of the Employees Table, so that the correct statement related to the Employee will appear in the letter. All other information; including Memo Reference, Date, Subject, letter body text, and others; is inserted from the Letter Table that I have mentioned above.

    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 Insert Menu and select Design View from the displayed list.

    4. Design the Form like the sample image given below.

      Write a Macro to close the form Letter. Select the Close Button, display the property sheet, change the Name Property to cmdClose, and set the macro to the On Click Event of the Close Button. Click on the Preview button, display the Property sheet, and change the Name Property to cmdPreview. We will write a small Sub-Routine later for the Preview button. Display the Property Sheet of the Subject TextBox and set the Scroll Bars Property= Vertical. Make the same change for Para1 and Para2 Text Boxes as well. Save the Form with the name Letter.

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

      Inserting a Statement

    6. We will make use of three columns of data, EmployeeID, OrderDate, and Freight, from Orders Table to prepare a statement to insert into the Form Letter as shown in the first image above. We will pretend the Freight Value is the Sales Figure of each employee and will extract the Year from the OrderDate Field taking it as Sales Year. We need three Queries to prepare a Report Table. Copy and paste the SQL String given below one by one into the SQL Window of new Queries and save it with the Query Names as indicated:

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

      NB: You must be careful how you specify the Target Table name for a Make-Table Query and the name of the Query itself. Both should not be the same, otherwise, you will run into trouble when you run the Query. I have added the letter Q at the end of the Query name Yearwise_IncentiveQ, you can use any name you prefer.

      With the first Query, we are selecting the data from Orders Table and creating a year-wise summary of Freight Value, and changing its name to Sales. The second one is a Crosstab Query which transposes the data, year-wise. Since we cannot directly use Crosstab Query for Report we are creating a table Yearwise_Incentive with a Make-table query.

    7. After creating and saving the third Query, double-click on it to run and create the output table Yearwise_Incentive (Click OK to warning messages).

    8. Design a Report using the Yearwise_Incentive Table as shown below. The size of the Report is approximately 6.5" wide. Do not use Page Header/Footer Sections of the Report. Put the Field Heading Labels on the Report Header Section. Save the Report with the name Yearwise_Incntv.

    9. We need one more Query to join both the Employees Table and the Letter Table that we created at the beginning. Copy and Paste the following SQL String into the SQL Window of a new Query and save it with the name LetterQ.

      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;

    10. After saving the Query open it in the design view and check how both tables are placed on the Query design surface. Remember, we have created only one record in the Letter Table but there are 9 records in the Employees table. In the Query design area, we have placed both queries without joining them together with a common field and placed fields from both tables on the columns. When you open the Query in the Datasheet view you can see the Letter Table's single record is repeated for each record in the Employees table.

      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.

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

      Look carefully at the image. The Report Header/Footer Sections, as well as Page Header/Footer Sections of the Report, are not used in the Report; instead, the header part of the Memo is designed within the FirstName Header Section. To create the FirstName Header/Footer Sections, Click on the Sorting and Grouping Toolbar Button or select the same option from the View Menu. On the Displayed Control select FirstName in the Field/Expression Column and in the Group Properties below, select Yes in the Group Header and Group Footer properties. When the FirstName Header Section appears, point the mouse over the Detail Section horizontal bar to change the mouse into a cross, click and drag the detail section down to get enough space to design our Letter header Section.

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

      Inserting the Statement

      • Insert the Yearwise_incntv statement that we designed earlier between Para1 and Para2 above and link the statement with the EmployeeID field that we have placed separately in the FirstName Header Section. To insert the Yearwise_incntv statement in the main report move the main report to the right of the Database Window and position it side-by-side (click on the Report Tab, if it is not visible) click and drag the statement and place it between Para1 and Para2 and properly place it as shown above. Re-size the report width, if it got expanded to the right.

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

    12. Open the Report in Print Preview, and advance the Pages by clicking on the Page Control at the bottom. Check whether the Employee's name appearing in the Address section at the top, changes on each page and the name appearing in the statement in the middle of the paragraphs is also the same as above. Close the Report.

    13. Open the Letter Form in the design view.

    14. 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
    15. 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 be reflected in the previewed letter also.

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

    With this article, we have set the stage for Merging of Data Field Values and Built-in Functions into the body text of the Letter and Printing it, which we will try next.

    Downloads



    Share:

    MS-Access and Mail Merge

    Introduction

    Address Labels

    When we talk about Address Labels, Form Letters, Mail Merge, and so on, the name that comes into our mind is MS Word, loaded with plenty of functions for the above tasks. It needs a database to provide source data for address labels, Form Letters, etc. We can prepare a Table in Word Document and use it for Addresses or attach one from Microsoft Access or other database sources.

    Here, we are not going to use MS Word for Address Labels, Form Letters, and Mail Merge operations, we will do it with MS Access. Yes, we will try Mail Merge also.

    We may not be able to do fancy paragraph formatting as we do in Word, but preparing Form Letters in Access is important in situations like reporting of Agency Agreement Renewals, Bank Guarantee Renewal Reminders or Department-wise Monthly Stationery Expenses or Telephone Expenses for review, etc., and needs only one or two standard paragraphs combined with the actual statement, which we can quickly put on the print within no time.

    Designing the Address Labels.

    We will start with the simple task, Designing the Address Labels. MS-Access has a built-in Label Wizard, which we can use for quick designing of Address Labels of various sizes and shapes. MS-Access has plenty of predefined Label Sizes of different Manufacturers to select from. This is good when hundreds or thousands of labels are needed on continuous stationery, in predefined sizes.

    But, 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 that needs to prepare them:


    The Design Task

    1. Import the Employees Table from the Northwind.mdb sample database, if you have not done it so far for our earlier sessions. Refer to my earlier post

      Saving Data on Forms, not in Table for the exact location reference of the sample file.
    2. Select the Employees Table and select

      Report from 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: /p

    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 the five Text Boxes together. Select Vertical Spacing - - > Decrease from 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 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 puts a checkmark in the Same as Detail, if it is not already 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 look like the sample given at the top of this page.

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

    Share:

    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