Crosstab Union Queries for Charts

Introduction.

For preparing Charts in Excel, the Source Data keyed directly into cells or source-data cells are linked to specific locations on the Worksheets where summary figures are available. The arrangement of chart values is planned and entered in the desired order depending on the type of Chart we require. Charts are created out of them as the final step for taking printouts or presentation purposes. 

Most of the time this is a one-time exercise. If modifications like adding another month's data, if it is a month-wise chart, then we have to modify the physical arrangement of the data to add another set and change the chart Data Series Range Addresses to make the new data appear on the Chart. 

If advance planning is made for all twelve months' data area for the Chart and if the actual data appears only up to March then the balance of 9 months Chart Area will be empty.

Preparing Data for Chart.

For preparing charts in MS-Access we need to work with Queries to shape the data into a form that can go directly into the Chart. In MS-Access, the preparation of data can be automated by running Action Queries wherever needed through macros and it is only a one-time setup. We can use the partially shaped Summary Data with other Queries, like Cross-tab and Union Type Queries to reformat them to make them suitable for Charts. 

This Type of Query takes new rows of data when added to the Summary Source Table and reformats them into new columns reflecting that change automatically on the Charts as well. Once the Chart is created with these types of Queries it works every time without any modifications and your Chart is ready with new values in no time.

We will look into the usage of Cross-Tab and Union Queries in action, shaping the summary data suitable for Bar Chart or Line Chart. We assume that the Profitability Summary sample data of Branch1 shown below is prepared with the use of Action Queries, like Make-Table, Append Queries, etc.

Table Name: Profitability

Location	PYear	PMth	Revenue	Expenses
Branch1	2008	1	$25,000	$5,000
Branch1	2008	2	$35,000	$7,500
Branch1	2008	3	$15,000	$4,000
Branch1	2008	4	$40,000	$15,000

The summary data above is reformatted for the Chart below with the help of Select, Cross-Tab, and Union Queries. The Chart is directly linked to the Union Query that combines data from Revenue, Expenses, and Income Values from their respective Cross-tab Queries.

Click to Enlarge

When the summary table above is added with fresh data of May, June, and others the change will automatically reflect on the chart without any changes to the source Queries of the Chart.

The first step that we have taken is to format the Year and Month values, create the last date of the month, and show that on the X-Axis of the Chart. For this, we have created a Select type Query with the name Summary_inQ on the source data table Profitability above. The Query's SQL String is given below.

1. Query Name: Summary_inQ

   SELECT Profitability.*,
    DateValue([pyear] & "-" & [pmth] & "-01") AS dt,
    DateAdd("m",1,[dt])-1 AS edate
   FROM Profitability;

   We have created expressions with two Date Functions on the above Query to calculate the last date of the month like 31/01/2008, 29/02/2008, and so on.

   DateValue([pyear] & "-" & [pmth] & "-01") AS dt

   This expression will create the Date 01-01-2008 from the Year 2008 and month value 1 for January and the expression is named as dt.

   DateAdd("m",1,[dt])-1 AS edate

   This expression calculates the last date of the month using the column dt as input value and the resultant column is named edate (for end date).

   Create the Query.

   Open a new Query in design view. Do not select any Table or Query from the displayed list. Display the SQL Window (select SQL View from View Menu), copy and paste the above SQL String into the Query's SQL edit window, and save the Query with the name Summary_inQ.

   I have this urge for using the letter Q or the word Cross or Union with the name of the Queries because these Query types can be used as the source for other Queries and easy to identify them when the list is displayed mixed with table names.

   The Cross-Tab Queries.

   Next, we will create three Cross-Tab Queries for the values Revenue, Expenses, and Income figures. The SQL Strings are given below. Copy and paste each one into the new Query's SQL window and save them with the name as shown.

2. Query Name: Revenue_Cross

   TRANSFORM Sum(Summary_inQ.Revenue) AS SumOfRevenue
   SELECT "1Revenue" AS [Desc]
   FROM Summary_inQ
   GROUP BY "1Revenue"
   PIVOT Summary_inQ.edate;

   If you check the SELECT clause on the second line above, I have prefixed the digit 1 with the word Revenue and the next two Queries that we are going to create will have 2 and 3 prefixed to the description Expenses and Income respectively. When the Cross-Tab Query Values are combined with the Union Query they will be sorted in the correct order of Revenue, Expenses, and Income and will appear in that order on the Chart, rather than Expenses on the top, Income in the middle, and Revenue at the bottom, when sorted by Union Query in Alphabetical Order.

3. Query Name: Expenses_Cross

   TRANSFORM Sum(Summary_inQ.Expenses) AS SumOfExpenses
   SELECT "2Expenses" AS [Desc]
   FROM Summary_inQ
   GROUP BY "2Expenses"
   PIVOT Summary_inQ.edate;

4. Query Name: Income_Cross

   TRANSFORM Sum([Revenue]-[Expenses]) AS Expr1
   SELECT "3Income" AS [Desc]
   FROM Summary_inQ
   GROUP BY "3Income"
   PIVOT Summary_inQ.edate;

   Now, we have to combine the data from all the three Cross-Tab Queries into a Union Query and Design the Chart using the Union Query as a Source. The Union Query SQL String is given below. Copy and Paste it into a new Query as we did for Cross-Tab Queries and save it with the name as suggested.

5. Query Name: Union_4Chart

SELECT * FROM [Revenue_Cross]
UNION SELECT * FROM [Expenses_Cross]
UNION SELECT * FROM [Income_Cross];

The Summary Data travels all the way from the Profitability Table to the Union Query to the Chart. It takes different shapes and that shape changes when the new month's data are added to the base table. The Chart responds to the change to the source Union Query and it shows the new values on the Chart automatically.

Designing the Chart.

Now, all you have to do is to design a Chart using the Union_4Chart Query. Instead of repeating the same procedure that I have already explained earlier you can go to the Next Page and continue there from Step 4 onwards. Change the Property Value Row Source = BarChartQ to Row Source = Union_4Chart and continue with creating the Chart and formatting it as explained there. Make changes to the Titles and other formatting options to make the finished Chart look like the sample given above.

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line-Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

Union Query

Introduction.

Union Queries are useful for combining data from more than one Table or from other Queries while maintaining the original data physically separate.

Assume that the data Tables from different branches (branch1, branch2 & branch3) are linked directly into our main system in separate files and our job is to combine the contents and prepare monthly Reports, Charts, etc. We can use Append Queries and combine the data into a separate Table and prepare what we want. Then that is a duplication of information. Instead, we can combine the data with the help of a Union Query and use that as the source for our further processing tasks. The source data files will remain physically separate, and branch locations can continue updating information on them.

We cannot place Tables or Queries on the Query Design surface, as we do with other types of Queries instead we have to write the SQL String manually on the SQL Window directly. The SQL Syntax is not that hard to memorize. You need only a little practice to write them. First, let us have a look at the sample Tables given below, their contents, the number of fields in both tables, Field Names, Values, and Field Types.

Sample Tables

Table-1 Image:

Click to Enlarge

Table-2 Image:

Click to Enlarge

Creating a Union Query

Let us look into an example. The contents of the above tables can be combined together with the help of a Union Query.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. Without adding tables or queries, click Close in the Show Table dialog box.
4. On the Query menu, point to SQL Specific, and then click Union.
5. Enter SQL SELECT statements to combine data from tables or queries.

Example:

TABLE [Branch1] UNION SELECT * FROM [Branch2];

OR

SELECT * FROM [Branchl] UNION SELECT * FROM [Branch2];

Copy and paste one of the above SQL strings into the SQL design window and save the Query with the name UNION1 or with any name you prefer. Open the Union Query in the normal datasheet view.

The output of the above query is given below:

Click to Enlarge

What Does the Union Query Do?

The above statement will combine data from both Branchl and Branch2 Tables and sort the data on the first field. Any duplicate records found in them will be suppressed from the output. If you have more than 2 Tables or Queries you can repeat the part UNION SELECT * FROM [xxxxxxxx] (where [xxxxxxxx] stands for table or query name) for each table or query and place a semi-colon (;) at the end.

We can interpret the action that goes into the above SQL statement and read it as "Take all the field values of all the records from the table Branchl, select all the field values from all the records from Branch2 and combine them together, remove duplicates out of the resultant recordset and sort the data in ascending order based on values in the first column."

You cannot use field types MEMO, OLE, or Hyperlink in the Source tables or Queries in the above example. But if you add the magic word ALL after the word UNION then all the fields (including Memo, OLE & Hyperlink) are taken in, no sorting is carried out, duplicates are not removed and all the records from all Source Tables or Queries will be given back to you in the order the Tables or Queries are appearing in your SQL Statement.

An Experiment.

Let us try another trick. We will take the first Table contents twice, instead of joining another table, and let us see the difference. Modify the SQL String as follows or create a new Query with the following statement:

TABLE Branch1 UNION SELECT *FROM Branch1;

Open the Query in Datasheet View. What happened? The second set of data vanished. Union Query automatically suppresses duplicates from its outputs unless we explicitly state otherwise. Modify the SQL String by adding our magic word ALL immediately after the word UNION and then open the Query in Datasheet view. Now the Table contents will appear twice in the output.

Points to Note.,

There are certain rules that will not be overlooked by the Union Query, like all Source Tables or Queries should have an equal number of fields, sorting Field Name(s) must be from the first Table or Query, etc., and there is no magic word to over-rule this.

If you mixed the order of the fields and different data types (Number, Text) in different positions the output will be given to you in the order of the fields you have provided and the Column headings will be taken from the First Table or Query only. If you made a mistake in placing the field order differently, in any of the source tables or queries, you will come to know about it only when you take the time to look through the output.  

If you look at the sample output given above, the Age and Country field values from Table Branch2 are placed under the Salary and Address fields.

We will make little change in the structure of the second Table Branch2 and see how the output behaves after that, to understand what can go wrong if we are not careful when handling this type of Queries.

1. Open Table Branch2 in the design view.
2. Click and drag the field Age and place it after the field FirstName.
3. Click and drag the Country field and place it next to the field Age.
4. Save the Table Structure.
5. Open the Union Query and inspect the output.

The shifted field values will appear exactly on the new location moving other values forward up to the point from where we have removed the Age and Country fields to the left, ignoring the data types of other member fields in the Union Query and you will not get any warnings.

In the above example, we have taken all the fields from the member tables by giving the * symbol after the SELECT clause. If you need only selected fields from the member tables or queries you must specify them in the correct order.

To select EmployeelD, FirstName, and Birth Date from both tables and to get the output sorted in the FirstName field we can write:

SELECT [EmployeelD],
 [FirstName],
 [BirthDate]FROM [Branch1]
UNION ALL SELECT [ID],
 [FirstName],
 [BirthDate] 
FROM [Branch2]
ORDER BY [FirstName];

The ORDER BY Clause will take Field Names only from the Source Object appearing in the first SELECT statement, otherwise, it will show an error when you attempt to view the output.

If you open Table Branch2 in Datasheet view, you can select and drag Age and Address Columns to any position for your viewing purposes. If you have opted to save the layout, after these changes you will always see the field values in the changed position in the datasheet view, irrespective of the physical position of these fields in the Table structure or Query Design. 

Remember, never depend on the datasheet view of the table or query to determine the field layouts for the Union Query. Always look for the field order in the table structure or Select Query Design View before adding them into the Union Query in the same order.

CAUTION.

When you are designing Union Queries having hundreds or thousands of records in the output, always make a point to check through the object level record set, at least once, to ensure that they are in the correct order. Once we are sure that the output is appearing correctly, then you can apply to sort, suppress duplicates or include conditions to filter data.

When applying conditions you must specify Criteria at each Table or Query level.

For example: if you want to select employees born on 31st December 1960 or earlier from both tables then you will write the SQL in the following manner:

SELECT * from [Branch1]
WHERE ([BirthDate] <=#12/31/1960#)
UNION ALL SELECT * from [Branch2] 
WHERE ([BirthDate] <=#12/31/1960#);

This will take records from each member table or query that satisfies the criteria. Remove the WHERE clause from any one position and check the output again.

• Find or Filter Data on Form
• Who is Online
• Automated Email Alerts/
• Configure Outlook for Lotus Notes
• MS-Access and Email

Read More

Percentage on Total Query

Calculating Line Value Percentage on Total.

How do we calculate the percentage of individual value on each row based on the Total Value of all rows?

Let us look into an example. Northwind Traders Customer-wise Order-wise Sales Figures are kept in a Table. Management would like to know the Customer-wise Sales Summary Figures. The Management has given further instructions that they would like to know the Percentage of each Customer's Sales Values on the Total Sales Value of all Customers and they need only the Top 10 Customers' with the highest Sales Values.

We will prepare a Total Query and we are through with the Problem. Import the Tables Orders and Customers from the NorthWind.mdb sample Database. If you are not sure about the location of the sample database on your machine visit the Page Saving Data on Forms not in Table for location references.

The Query SQL

We need only the Orders Table now. Customers Table is being used as the Source Table for a Combo box in the Orders Table to enter CustomerID into the Table. If we don't bring in the Customer Table also then an error message will pop up when we use the Orders Table in our Query.

1. Open a new Query in your Project. Copy and paste the following SQL String into the Query's SQL editing window:

   SELECT TOP 10 Orders.CustomerID,
      Sum(Orders.Freight) AS Sales,
      DSum("freight","orders") AS Total,
      Sum([freight]/DSum("freight","orders")) AS Percentage
   FROM Orders
   GROUP BY Orders.CustomerID
   ORDER BY Sum(Orders.Freight) DESC;

2. Change the Query View into the normal design view (View -> Design View) to have a better look at the Column-wise structure.

3. We have grouped the Values on CustomerIDs, summing up the Customer-wise Freight Values, and changed the Column Name: Sales, for example-purposes.

4. The third column finds the Freight Value Total of all the records in the Orders Table with the DSum() Function. Notice that we have used the word Expression on the Total Row instead of Sum.

5. We have to Repeat the Sum() and DSum() Functions in the next Column formula -Percentage: Sum([Freight])/DSum("Freight", "Orders") ' to calculate the Percentage of Sales on Total Sales Value in the Table. Even though we have calculated the Customer-wise Freight Values and renamed the field as Sales and Total Freight Values as Total in the second and third columns respectively, these two names we cannot use in the Percentage calculating expression in the form Sales/Total. The Total Query will not allow this. If you find the percentage calculating formula too complicated then you save this Query first without the percentage column. Open another Query that uses the earlier one as a source and write the formula Percentage: Sales/Total for the new column and here you will not have any problem.

6. To format the calculated column with Percentage, click anywhere on the Column, display the Property Sheet (View -> Properties.) and select Percentage in the Format Property and enter 2 in the Decimal Places Property.

   Listing the Top 10 highest Percentage Records.

7. To limit the output of the Query to 10 Records with the highest Sales values we have to change the Top Values Property of the Query. Click on an empty area somewhere near the Table above.

8. Display the Property Sheet (View -> Properties), if you have closed it.

9. Change the Property Top Values to the desired value. Currently, it will be 10, because we have mentioned SELECT TOP 10 in the SQL String that you have pasted into the SQL Window.

10. Open the Query in Normal Datasheet View and check the Result. We have already set the Sorting order to Descending under the Sales Column so that the Highest Sales Value and Percentage will be at the top Row.

Earlier Post Link References:

• Reminder PopUps
• MS-Access Graph Charts
• MS-Access Graph Charts-2
• Report Page Border
• Highlighting Reports

• Who is Online
• Automated Email Alerts/
• Configure Outlook for Lotus Notes
• MS-Access and Email
• Dynamic Report

Read More

Selection of Office Assistant

Introduction - Access2003.

Since we have tried many options with Office Assistant earlier, we will do some fun stuff this time. Those who entered straight into this page may like to look into the following Topics. The links to the earlier Articles are given below:

1. Message Box with Office Assistant
2. Message Box with Menu Options using the Office Assistant
3. Message Box with Check Box type Menu with the use of Office Assistant
4. Command Button Animation (Visit this page to Link the Microsoft Office Library File to your Project).

We all know that we can select the Office Assistant of our choice from the Help Menu of MS-Access or other MS-Office Applications as well. Instead, we will design a Form in MS-Access to organize all the Office Assistant Characters available on our PC on the Form, display them and select the one that we like, and set it as the default Office Assistant, as we please.

A sample image of the Program Run is given below:

We need a small Table with the Office Assistant's File names in a List Box as shown on the left side of the Form above.

Designing a Table and Form.

1. Create a Table with the Field Names as shown below, save it with the name Assistant and enter the Data from the List.
   

   Click to Enlarge

   Out of eleven items, the first seven files are in the same location and others are found elsewhere. Before entering the above list into your Table it is a better idea if you run a search for all the files with the File Extension.ACS on your machine and use those files in the list.

   To search for the files, select Start -> Search -> For Files or Folders -> All Files and Folders and enter the search term *.ACS on the Search Control and Click Search. When the search is completed, note down the correct path of all the files displayed and then enter them into your Table with the full Path Name.

2. Design a small Form and set the Properties of the Form and controls as given below to match the design.
   
3. Create a List Box on the Detail Section of the Form. Click on the List Box (if it is not selected), Display the Property Sheet (View -> Properties) and change the Property Values as shown below:
   • Name : AsstList
   • Row Source Type: Table/Query
   • Row Source: SELECT Assistant.id, Assistant.asstChar FROM Assistant;
   • Column Count: 2
   • Column Heads: No
   • Column Widths: 0";0.875"
   • Bound Column: 1
   • Multi-Select: None
   • Left : 0.125"
   • Top : 0.3125"
   • Width : 1.2083"
   • Height: 1.4375"
   • Font Weight: Bold
4. Create a Label on the top of the List Box and change the Caption to Office Assistant.
5. Draw a Rectangle Control on the right side of the List Box and change the Properties as given below.
   • Left: 1.4271"
   • Top: 0.3125"
   • Width: 1.7188"
   • Height: 1.4375"
   • Back Style: Transparent
   • Special Effect: Sunken
6. Create two Command Buttons below the List Box as shown on the Design. Display the Property Sheet of the Left Command Button. Change the Properties as given below:
   • Name: cmdSetAsst
   • Caption: Set as Default
7. Change the properties of the right-side Command Button.
   • Name: cmdCancel
   • Caption: Cancel
   
   

   Form Property Values.

8. Now, to change the Properties of the Form, click on the left top corner of the Form (at the intersection where both the vertical and horizontal Scales meet). Display the Property Sheet, click on the All tab of the Property Sheet, if that is not the current one, and change the Property Values as given below.
   • Caption: Office Assistant
   • Default View: Single Form
   • Scroll Bars: Neither
   • Record Selectors: No
   • Navigation Buttons: No
   • Dividing Lines: No
   • Auto Resize: Yes
   • Auto Center: Yes
   • Pop Up: Yes
   • Modal: No
   • Border Style: Dialog
   • Control Box: Yes
   • Min Max Buttons: None
   • Close Button: Yes
   • What's This Button: No
   • Width: 3.3333"
   • Allow Design Changes: Design View Only
9. Click on the Detail Section of the Form. Display the Property Sheet, if you have already closed it, and change the Property:
   • Width = 2.4271"
10. Select Save from File Menu and save the Form with the name Assistant and stay in Design View, don't close the Form.
    
    

    The VBA Code.

11. Display the VBA Module of the Form, View -> Code. Copy and Paste the following code into the VBA Module, save and close the Form:

    Dim defaultAssistant As String
    Dim strAsst() As String
    
    Private Const m_left As Integer = 500
    Private Const m_top As Integer = 225
    
    Private Sub AsstList_Click()
    Dim vID As Byte, strFileName, FRM As Form, l As Long
    Dim ctrl As Control
    
    On Error GoTo AsstList_Click_Exit:
    
    vID = [AsstList]
    With Assistant
        .On = True
        .fileName = strAsst(vID)
        .Animation = msoAnimationGetAttentionMajor
        .AssistWithHelp = True
        .GuessHelp = True
        .FeatureTips = False
        .Left = m_left
        .Top = m_top
        .Visible = True
    End With
    
    AsstList_Click_Exit:
    Err.Clear
    End Sub
    
    Private Sub cmdCancel_Click()
    On Error GoTo cmdCancel_Click_Exit
    With Assistant
        .On = True
        .fileName = defaultAssistant
        .Animation = msoAnimationBeginSpeaking
        .AssistWithHelp = True
        .GuessHelp = True
        .FeatureTips = False
        .Left = m_left
        .Top = m_top
        .Visible = True
    End With
    DoCmd.Close acForm, Me.Name
    
    cmdCancel_Click_Exit:
    Err.Clear
    End Sub
    
    Private Sub cmdSetAsst_Click()
    DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub Form_Load()
    Dim db As Database, rst As Recordset
    Dim acsFiles As Integer, i As Integer
    
    On Error GoTo Form_Load_Exit
    defaultAssistant = Assistant.fileName
    
    acsFiles = DCount("* ", "Assistant")
    If acsFiles = 0 Then
       MsgBox "Assistants File Not found. "
       Exit Sub
    End If
    ReDim strAsst(1 To acsFiles) As String
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Assistant", dbOpenDynaset)
    i = 0
    Do While Not rst.EOF
    i = i + 1
    strAsst(i) = rst![Path]
    rst.MoveNext
    Loop
    rst.Close
    
    With Assistant
      If .On = False Then
        .On = True
      End If
        .fileName = defaultAssistant
        .Animation = msoAnimationBeginSpeaking
        .AssistWithHelp = True
        .GuessHelp = True
        .FeatureTips = False
        .Left = m_left
        .Top = m_top
      If .Visible = False Then
        .Visible = True
      End If
    End With
    Form_Load_Exit:
    Err.Clear
    End Sub

    
    

    Selecting the Office Assistant.

12. Open the Form in Normal View. Click on the items in the List Box one by one giving a little time gap for the Assistant Character to appear. When your favorite image appears, click on the Set as Default Command Button to set the current Character as the default Office Assistant. If you change your mind then click Cancel Button to retain the existing one.

You can export this Form to your other Projects and use it to link the Microsoft Office Library File.

NB: At the beginning of the Code, I have defined two Constants m_left = 500 and m_top = 225 to position the Image on the Form when it appears based on my machine's Screen Resolution 1024 x 768. You may change this value to adjust it to your machine's Screen Resolution.

Download Demo Database

• Automated Email Alerts/
• Configure Outlook for Lotus Notes
• MS-Access and Email
• Dynamic Report
• MS-Access & Mailmerge-3

Read More

Office Assistant with Check Box Menu

Introduction - Access2003.

We have seen the usage of Office Assistant for Message Boxes and Message Box with Options Menu in the earlier Article.

Like I said before when we design Applications there must be something different in it, something better than what we did earlier, better and attractive to the User, as well as to the curious onlooker too.

Data processing has its own importance and I am not overlooking it here. Every application that we develop is different and has challenges on its own merit and we have to devise new methods or techniques to deal with the issues in them.

But, repeating the same type of Application design and using the same kind of controls like the Command Buttons or Message Boxes over and over again is a boring experience, for the developer and for the User as well. I think that is one of the reasons why Windows or Microsoft Office Applications come out with more features and better-looking Programs all the time.

Check-box Type Menu.

We will try the Check Box type Menu Options in Message Box, with Office Assistant. We will use the same sample Form that we have created for running the earlier program. An image of the run of the Program is given below:

Click to Enlarge

If you have not tried the earlier example, please read the Article on MsgBox with Options Menu and go through the preparations that I have mentioned there, in order to run the programs given here without errors.

1. Open the Form OptionCheck that we have created in the earlier example in the design view.
2. Create a second Command Button on the Form.
3. Click on the Command Button to select it and display the Property Sheet (View -> Properties). Change the following property values as given below.
   • Name = cmdChk
   • Caption = Get Checked
4. While the Form is still in design view, display the VBA Module of the Form (View -> Code).
5. Copy and paste the following code into the VBA Module of the Form and save the Form.

   Private Sub cmdchk_Click()
   Dim OptionArray(1 To 5) As String
   Dim i As Integer, msg As String, title As String
   
   OptionArray(1) = "Display Report Source Data"
   OptionArray(2) = "Print Preview"
   OptionArray(3) = "Print"
   
   msg = "Please Select an Option"title = "Report Options"
   
   i = MsgGetChk(msg, title, OptionArray())
   
   If i > 0 Then
       Me![optTxt] = i & " : " & OptionArray(i)
   End If
   
   Select Case i
       Case -2
          msg = "Cancelled"
          Me![optTxt] = i & " : Cancelled"
       Case 1
           'DoCmd.OpenForm "myForm", acNormal
           msg = "Display Report Source Data"
       Case 2
           'DoCmd.OpenReport "myReport", acViewPreview
           msg = "Print Preview"
       Case 3
           'DoCmd.OpenReport "myReport", acViewNormal
           msg = "Print"
   End Select
   
           MsgOK "Selected: " & msg
   End Sub

   This is a copy of the same program that we have used for Baloon Type Options which is already there in the Form Module. I have made changes in two places in the Code to use it for this example.

   • The number of elements in the OptionArray() Variable is reduced to three after removing the Cancel item because the OK and Cancel buttons will be included in the main program.
   • Included testing for the Cancel condition in the Select Case . . . End Select statements and terminates the program if the user clicks on the Cancel Button. When you implement this method into your Project you can remove Case -2 and the next two statements underneath it. Here it is inserted for demonstration purposes only.

   The Main VBA Program

6. Copy and paste the following main program into a Global VBA Module in your Project and save it.

   Public Function MsgGetChk(ByVal strText As String, ByVal strTitle As String, ByRef obj) As Integer
   Dim X As Integer, i, c As Integer, k As Integer
   Dim Bal As Balloon, vSelect As Integer
   
   On Error GoTo MsgGetChk_Err
   
   Set Bal = Assistant.NewBalloon
   i = 0k = UBound(obj)
   k = IIf(k > 5, 5, k)
   
   For X = 1 To k
      If Len(obj(X)) > 0 Then
        i = i + 1
      End If
   Next
   
   ForceEntry:
   
   With Bal
      .Animation = msoAnimationWritingNotingSomething
      .Icon = msoIconAlert
      .Heading = strTitle
      .Text = strText
      .BalloonType = msoBalloonTypeButtons
      For X = 1 To i
        .Checkboxes(X).Text = obj(X)
      Next
      .Button = msoButtonSetOkCancel
     vSelect = .Show
   
     If vSelect = -2 Then
           MsgGetChk = vSelect
           Exit Function
     End If
   
     c = 0
     For X = 1 To i
       If .Checkboxes(X).Checked Then
          MsgGetChk = X 
   ' get the item checked by the user
          c = c + 1 
   'more than one item checked
          If c > 1 Then
             Exit For
          End If
        End If
     Next
   
     If c > 1 Then
        strText = "Select only one item. "
        GoTo ForceEntry
     End If
   
     If c = 0 Then
        strText = "Select one of the Options or Click Cancel! "
        GoTo ForceEntry
     End If
   
   End With
   
   Assistant.Visible = False
   
   MsgGetChk_Exit:
   Exit Function
   
   MsgGetChk_Err:
   MsgBox Err.Description, , "MsgGetChk"
   Resume MsgGetChk_Exit
   End Function

   This main program is almost the same as the Options Menu that we have used in our earlier example. The statement labels(X).Text = MaxArray5obj(X) changed to .Checkboxes(X).Text = obj(X). Added a few more lines of code to check, whether the User

   • Put checkmarks in more than one item, if so, forced to select one item only.
   • Click the OK Button without selecting any of the Options. If so, asks to select one of the options before clicking the OK Button and suggest clicking Cancel Button if there is a change of mind.
7. If the office Assistant is not visible, turn it on, Help -- > Show the Office Assistant, or press Alt+H followed by Alt+O.
8. Right-Click on the Office Assistant and select Choose Assistant. Click on the Next or Back Button to select the Office Cat Character.

   The program works with any Office Assistant Image but the initial Animation Type: msoAnimationWritingNotingSomething set in the Program works fine with the Office Cat and I love the Office Cat.

9. Open the OptionCheck Form in normal View. Click on the Get Checked Command Button. If everything went through, well you will see the Office Assistant with CheckBoxes as shown in the image at the top.
10. Try clicking the OK Button with checkmarks in more than one item. Try clicking the OK Button, without putting a checkmark in any of the options.

Download Demo Database

Download OfficeAssistant.zip

• Configure Outlook for Lotus Notes
• MS-Access and Email
• Dynamic Report
• MS-Access & Mailmerge-3
• MS-Access & Mailmerge-2

Read More