Textbox And Label Inner Margins

Let it be a Form or Report, a good design will always catch the eye of the User and the onlooker alike. We all design them, but if we give the same Report or Form to five different people to design, they all will do it differently based on their individual skill and tastes, unless they all use the same built in wizards.


The user may give importance mainly to the information contained in a Report and insist only on getting it in the right order and layout. But, how to present this to him/her is your responsibility alone and depends on how much time you have to work on it. You will be designing a Report only once and it is part of your Project, how you do it is your own choice.


Your Report may also travel to places through Faxes or E-mails and likely to have a wider audience to it. Where-ever it goes I expect some one to ask who designed it when compared with other Reports floating around. Luckily, MS-Access has all the right Tools you need for designing strikingly beautiful Reports or Forms. Little more of your time and imagination can do wonders with simple tools available in Access.


Here, I would like to introduce you to few Properties of Text Boxes and Labels on a Report and how simple design changes can transform it into a nice looking Report. The following is an image of a Tabular Report designed with the Report Wizard of MS-Access.



Wizards are very good in laying out all the objects quickly on Forms or Reports with default formatting of Font Type, Size, Style and saves a fair amount of design time. All you need to do is to modify them to your liking.


If you would like to try out this simple design step by step then you may Import the Shippers Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb MS-Access sample Database. The location address shown here is for MS-Access2003. If your Version of Access is different then look for change in \Office11\ part of the address. After importing click on the Table and select Report from Insert menu and select Auto Report: Tabular from the displayed options. The above Report will be created in no time. An image of the above Report in Print Preview is given below:


The modified version of the same Report in Print Preview below:




The transformation was easy with only few changes to the above design and I know what change you have noticed first. If I have turned on the borders of the Text Boxes and Labels alone then the Print Preview will look like the one below:



Make the following changes to the above design:

1. Delete the thick line under the Header Labels.


2. Point the Mouse on the vertical ruler to the left of the Header Label Shippers, so that it turns into an Arrow pointing to the right, and then Click and drag along the ruler downwards so that you could select all the Labels and Text Boxes in the Report Header, Page Header and Detail Sections
together.

Alternatively you can click on an empty area of the Report and drag the Mouse over all the controls to select them. Do not select the Page Footer Section Controls. We don't need them on this Report.



3. Display the Property Sheet (View- -> Properties) and change the following Values:




• Border Color = 9868950


• Special Effect = Flat


• Border Style = Solid


• Border Width = Hairline



You need to change only the Border Color Value, others will be there as default, if not then change them as given above.



4. Select all the Field Header Labels alone in the Page Header Section, as we did in Step-2 above. Select Format- - > Align - -> Left to arrange the labels close together horizontally without leaving gaps between them.


5. Display the Property Sheet of the selected Labels (View - -> Properties) and change the Top Property Value to 0 and Height Property Value to 0.4167"



6. Centralize the Text horizontally within the Labels by changing the Text Align Property Value to Center, while all the Labels are still in selected state.



7. Select all the Text Boxes in the Detail Section together and select Format- - > Align - -> Left to arrange the Text Boxes close together without leaving gaps between them.



8. Display the Property Sheet of the Text Boxes (if you have already closed it) and change the Top Property Value to 0 and the Height Property Value to .2917" so that the data lines are not too close and crowded when Previewed/Printed.



9. If there is gap below the Labels in the Page Header Section and below the Text Boxes in the Detail Section then close them by dragging up the Detail Section Header and the Page Footer Bars.
   
   
10. Delete all the Page Footer Section controls. Close the gap by dragging the Report Footer Bar up.



11. Now we must resize the Report Header Label with the Shippers heading and make it as wide as the width of all Field Header labels in the Page Header Section put together. You can either do it by resizing manually with your eyes as guide. Or display the Property Sheet of all the Header Labels one by one and take the Width Property Value of each label and add up to get the total of all and change the Width Property Value of the Shippers heading label.



12. Change the Height Property Value to 0.416 and the Text Align Property Value to Center.
    
    
13. Save your Report with a Name of your choice.

With the above modifications the Report will look like the image given below, when Print Previewed.

The Report looks good, but with few more cosmetic changes it will look even better.

1. The Field Header Labels' Text must be vertically centered.


2. The Shipper ID Numbers and other field values are too close to the Border Line and they should be positioned little away from the border.


3. Open the Report in Design View and select all the Field Header Labels together as we did earlier.


4. Display the Property Sheet and drag the right scroll bar of the Property Sheet down to the bottom. There you will find the Inner Margin Properties that you can use to position the Text within the Controls.


NB: These Properties are available only in MS-Access2000 and later versions.





5. Change the Top Margin Property Value of Header Labels to 0.1"


6. Select the Text Controls together on the Detail Section and change the Top Margin Property Value to 0.0701".


7. Select the Shipper ID Text Box in the Detail Section and change the Right Margin value to 0.1"


8. Select the Company Name Text Box and change the Left Margin Value to 0.0597" and set the same Left Margin Value for Phone Number also.


9. Save your Report and open it in Print Preview. It will look like the 3rd Image from Top of this page.

Even though it took lengthy steps to explain it, you can do it quickly in a few minutes, when you know what to do to get what you want.

---

MS-Access Live Data in Excel
Database Connection String Properties
Opening Excel Database Directly
Display Excel Value Directly on Form
Opening dBase Files Directly

Multiple Parameters For Query

Queries are an essential element in data processing and we use them a lot in various ways. While creating Queries the main issue we are faced with is how to filter data in a user-friendly manner and make things work effortless for the User. We use several methods to facilitate the user to pass values as criteria to the Queries.

1. Creating Parameter Queries and inserting Variables (like [Enter Sales Date]) in Query's Criteria row so that the Query will prompt for Parameter Values and the User can Key-in them directly and filter records. The Data Type for the Parameter Variable can be defined by selecting the Parameters… Option from the Query Menu when you are in Design view of the Query.



2. Creates Text Boxes or Combo Boxes on the Form and the User fills in values into them and runs the Report or Data views. The underlying Queries will have a reference to the Text Boxes or Combo Boxes on the Form in the Criteria Row, like Forms![MyForm]![myDateCombo], and based on the values in them the data filtering takes place for Reports or Views.



3. Another way of selecting records is based on a range of Values. For example filtering Sales records for a certain period and the criteria setting on the Query for the Sales Date will be something like: Between #01/01/2008# AND #03/31/2008# if the values are used in Constant form. But, these values also can be passed from Text Boxes from a Form too.


What I prefer to do in these cases, create a small table (let us call it as Parameter Table) with one record and two fields for StartDate and EndDate and create a Datasheet Form and place it as a Sub-Form on the Main Form so that the User can conveniently key in the date range values into the Table.


This table will be included in the main Query and the StartDate, EndDate fields are placed in the Criteria row with the expression Between [StartDate] AND [EndDate]. It is important to see that this Parameter table has only one record in it otherwise the records selected from the main table will be doubled, if the parameter table has two records. We can control this by setting the Allow Additions Property Value, of the Data Sheet Form, to No so that the user is prevented from adding more records by mistake.


When the User clicks a button for the Report or for other outputs based on this date-range we can run the Query after refreshing the Parameter Sub-Form to update the changed value in the table.



4. The above example asks for all the data between StartDate and Endate. But there are times that we need data of intermittent values like Employee Codes 1, 5, 7, 8 and we are forced to input the Code in the criteria row in one of three ways like the example image given below:

I would like to present here another method that I use to provide the Users to select Parameter Values for Reports by putting check-marks in the Parameter Table.


Assume that our Company has Branch Offices across the Country and the Management may ask for Reports on selected Branches. Since, Branch Names are constant values all we need to do is to select the required Branches by putting check marks by their side and the selected cases can be used as criteria for filtering Data.


To have a closer look at this method and for simplicity we will use the List of Months for our example and see how the selected Months are used in the Criteria of the Main Query. The Image of the List of Months presented to the user in a Datasheet Form (as Sub-Form on a Main Form) is given below:



We need two Queries, one to filter the selected months from the list and second the Main Query in which we will use the Values from the first Query as Parameter to filter Data for the Report. Our first Query must come out with the result values 3,6,9 & 12 as per the Month selection shown on the image above. The following SQL string is used for it:


Query Name: Month_ParamQ

SELECT Month_Parameter.MTH
FROM Month_Parameter
WHERE (((Month_Parameter.[SELECT])=True));

When the User puts check marks on the Parameter screen the selection may not immediately update in the underlying Month_Parameter Table. To update the change we have to Refresh the Month_Parameter Sub-Form before opening the Report that pulls data from the Main Query that uses the above Query as criteria. For that we have written a statement on the On_Click() Event Procedure of the Print Preview Command Button as below.

Private Sub cmdPreview_Click()
     Me.Month_Parameter.Form.Refresh
     DoCmd.OpenReport "myNewReport", acViewPreview
End Sub

Now, how the selected months filtered in the Month_ParamQ can be used in the Main Query as criteria? It is easy, look at the third method we have used as criteria in the first Image given above. I will repeat it here below:


IN(1,5,7,8)


Here, we are asking to compare the EmployeeID values with the numbers 1,5,7,8 and select records that match with any of these numbers as output.


Similarly, all we need to do here in the Main Query is to write this as a Sub_Query in the Criteria Row to use the Month Values from the Month_ParamQ. The above criteria statement when written in the form of a sub-query it will look like the following:



IN(SELECT MTH FROM MONTH_PARAMQ)


The User don't have to type the Parameter values for the Report, only put check marks on the required items, click a Button and the Report is ready.

---

Database Connection String Properties
Opening Excel Database Directly
Display Excel Value Directly on Form
Opening dBase Files Directly
Opening External Data Sources

No Data and Report Error

Report Source Query or Table can end up with no output records. In that case some of the controls with formula on the Report will show #Error on them. An image of a sample report is given below:



The #Error at the right top is a control that has a formula for showing the Period for which the Report is currently prepared for. The controls to the right of the word TOTAL are Sub-Totals and Totals of detail line values and all of them ended up with Errors because the underlying Query of the Report doesn't have any record to show for the current period selected by the User.


Even though this is not a serious issue if it became necessary to show this Report to some one as a NIL REPORT or printed and kept for future references leaving the controls with errors this way is not in good taste on the part of the Database Developer.


The modified version of the above Report is given below with a Comment, Summary Controls showing Zero Values and Report period printed correctly.



I have made few modifications to the Report Design to add a hidden label at the footer of the Report with the Caption: *** Congratulations *** Nothing Pending to show up when there is no output Records for the Report. The Visible Property of the label is set to No manually. On the Detail Section Under the Description Column shows *** NIL REPORT ***. The period for which the Report is prepared is also shown to the right, above the Detail Section headings.


Report Period (DateFrom and DateTo) are normally entered into a Parameter Table and joined with the Report Source Table in a Query to use them for criteria and for displaying on the Report.



I have created two Text Controls (with the name Frm and To respectively) at the Report Header Section to the right of the Control name STAFFNAME to load the DateFrom and DateTo Values from the Report Parameter Table with DLookup() Function:



=DLookUp("DateFrom","Report_Param")

Second Control have the expression to read DateTo from the Report_Param Table and both values are used in the expression (="Period : " & [frm] & " To " & [To]) to format the values to show the output as in the second image given above.


These are all cosmetic changes for the Report. The major change is to create a temporary table with a single blank record, with the same structure of the Source Table or Query that is attached to the Report. If your Report is using a Table as Report Source Data then make a copy of the structure of the Table and add a tmp_ prefix to the table name like tmp_myReport. If it is a Query then create a Make-Table Query using the Report Source Query and create a temporary table. Add a blank record in the temporary table. If your Report Table has a Text Field that is displaying value on the Report then type *** NIL REPORT *** in that field. Keep all other fields Empty.


The trick is, when the Report is open by the User we will check whether the original Report Source Table or Query have any record in it or not. If not swap the Temporary Table with the Report Source Table or Query. The hidden Label's Visible Property will be set to Yes to display the comment *** CONGRATULATIONS *** NOTHING PENDING. Since the temporary table has one blank record in it already, the Summary Controls will not end up with errors.


We need a few lines of VBA Code in the Report_Open() Event Procedure to check and swap the Report Source Table or Query.

Private Sub Report_Open(Cancel As Integer)
Dim i
i = DCount("*", "myReport")
If i = 0 Then
   Me.RecordSource = "tmp_MyReport"
   Me.lblMsg.Visible = True
End If

End Sub

Copy the above lines of Code in the Report's VBA Module and make changes to insert the correct Table/Query and tmp_myReport names.

---

Opening External Data Sources
PIE Chart Object and VBA
Column Chart and VBA
Working with Chart Object in VBA
Linking IBM AS400 Tables

Dynamic Report

Report designing in MS-Access is very easy as we already know. Once we setup 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 on the Report design itself as all the standard labels, like main heading, the period indicator, Report prepared date, Page Numbers etc. are already taken care as part of the designing 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.


When someone asks for something difficult we can easily say it is not possible and the user may swallow it too, provided if 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 in this category too. But, then after saying that it keeps me disturbed and starts 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 to do it. But, there are times that we have to stick to the No answer too.


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


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, Month-wise formatted data shown below:




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




The Report contents should be for a period of 12 months and data selection criterion is set for the period Between 199607 and 199706 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, no issues.


But, when the data selection criteria changes for a different period the Report will not run. It will show error on the first field that do not match with the fields already placed on the Report and refuse 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 Report using this permanent field Names. We must find a way to define the field header labels with some formula taking the values from the report period parameter. The user can create report data for full 12 months or 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 with cross over period then the data for earlier period comes at the right end of the Report and later period at the beginning. The field headings are another issue to take care. Finally we cannot call it a good report or let us put mildly it is not a user-friendly report.


We found a remedy for this issue by introducing a small program on the Report VB 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 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;




3. After creating the above Queries one after the other design a Report as shown below using FreightV_CrossQ Query as Data Source.





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



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



6. In Page Header Section create 13 Label Controls and name them as 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 Save from File menu and save the Report with the name FreightVal_Rpt.



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



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



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



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

---

Highlighting Reports
Saving Data on Forms Not in Table
Edit Data in a Zoom-in Control
Custom Menus and Tool Bars-2
Custom Menus and Tool Bars

MS-Access and Mail Merge-3

Continued from previous Post MS-Access and Mail Merge-2.

MAIL MERGE IN MS-ACCESS

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


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


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


Usage of Field Values to merge with the Text:

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


• Use of at least one space between field names is recommended, if they are placed next to each other, but can be used without space also.


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


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


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

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


Output : A Statement of Year-wise special Sales Incentive, credited to Dr. Andrew Fuller’s personal A/c., is given below for information. His request for transfer to his home town Tacoma&9801 has been approved.


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

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

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



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



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



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

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

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

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

Don’ts:

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


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


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


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

1. Copy and paste the SQL String given below into the SQL window of Yearwise_FreightQ1 Query that we have created earlier overwriting the old SQL String and save the Query. This change is to create a new column for 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;




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



3. Open the letter editing Form (Letter) in design view, create a Combo Box to display the Report Source Data Field Names, which the Users can reference and type them correctly into 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 at the left side of the body text area). Display the Property Sheet of the Combo Box and change the following property values:



• Name = cboFields


• Row Source Type = Field List


• Row Source = LetterQ


• Column Count = 1


• Column Width = 1”


• List Rows = 8


• List Width = 1”





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




5. Save the Form with the above change.



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



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





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



9. Save the Report after the changes.



10. 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 top-most 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).Str
If 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

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

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


Open the Text editing screen: ‘Letter’ and try out examples explained above by inserting Field Names, Built-in Functions and Functions that uses 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 controlled environment, User friendly and will be a powerful Tool in your Projects.


Any suggestions for improvement of the program are welcome.

---

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

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, for changing 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, as how we are progressing through the steps to create it:

We need two sample tables, Employees and Orders from the Northwind.mdb Database. (Refer my earlier post Saving Data on Forms not in Table for the exact location reference of the sample file). Import the above Tables from 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 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 - Memo Reference, Date, Subject, letter body text and others - are 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 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 Text Box 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 given below with the sample data filled-in:





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 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 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 creates year-wise summary of Freight Value and changing its name to Sales. 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 Employees Table and the Letter Table that we have 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 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. On 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 Datasheet view you can see the Letter Table’s single record is repeating 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 on 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 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 the few more steps:




• Insert Yearwise_incntv statement that we have designed earlier between Para1 and Para2 above and link the statement with the EmployeeID field that we have placed separately at the FirstName Header Section. To insert the Yearwise_incntv statement into 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.




12. Open the Report in Print Preview, advance the Pages by clicking on the Page Control at the bottom. Check whether the Employee’s name appearing at 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 same as above. Close the Report.



13. Open the Letter Form in 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 normal view. Make some changes in the body text. Click on the Preview button. The changes that you have made in the text should reflect in the previewed letter also.

Your Report display should match with 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 print the letter, which we will try next.

---

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

MS-Access and Mail Merge

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 in MS-Access. Yes, we will try Mail Merge too.

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.

We will start with the simple task, 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. This is good when hundreds or thousands of labels are needed on continuous stationery of predefined sizes.

But, ordinary people like me who would like to print on plain paper, 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:

1. Import the Employees Table from Northwind.mdb sample database, if you have not done so far for our earlier sessions. Refer 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 View menu and remove the check mark 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 border as shown in the design given below:





5. Display the Property Sheet of the Text Box from 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 and 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 Text Boxes comes close together.


9. Draw a Rectangle around the Text Boxes. If the Rectangle hides the Text Boxes then select Transparent from the Fill/Back Color Tool Bar 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 for the final settings on Page Setup Control. Select Page Setup from File Menu.


11. Select the Columns Tab.





12. Change Number of Columns to 2.


13. Column Spacing change to 0.15”


14. In Column Size control put check mark in 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: AddressLabels.


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.

---

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