For preparing Charts in Excel, the Source Data keyed directly into cells or source-data cells are linked into specific locations on the Worksheets where summary figures are available. The arrangement of chart values are 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 print outs 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 balance 9 months Chart Area will be empty.


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, preparation of data can be automated by running Action Queries wherever needed through macros and it is only a one time set up. We can use the partially shaped Summary Data with other Queries, like Cross-tab and Union Type Queries to reformat them to make it suitable for Charts. This Type of Queries 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 with the Union Query that combines data from Revenue, Expenses and Income Values from their respective Cross-tab Queries.




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 to create the last date of the month and to 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 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 as edate (for end date).


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 source for other Queries and easy to identify them when the list is displayed mixed with table names.


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 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 descritpion 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 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 new month’s data are added to the base table. The Chart responds to the change on the source Union Query and shows new values on the Chart automatically.


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.

---

Keyboard Shortcuts

Find or Filter Data on Form

Who is Online

Automated Email Alerts.shtml

Configure Outlook for Lotus Notes

Labels: msaccessQuery

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 duplication of information. Instead we can combine the data with the help of a Union Query and use that as source for our further processing tasks. The source data files will remain physically separate, branch locations can continue updating information on them.


We cannot place Tables or Queries on the Query Design surface, as we do with other type 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 and with little practice you can write them. First have a look at the sample Tables given below, their contents, number of fields in both tables, Field Names, Values, and Field Types etc.


Table-1 Image:





Table-2 Image:




Let us look into an example. The contents of the above tables we will combine 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 string into the SQL design window and save the Query with the name UNION1 or with any name you prefer. Open the Union Query in normal datasheet view.


The output of the above query is given below:




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


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.


There are certain rules that will not be overlooked by the Union Query, like all Source Tables or Queries should have 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 table or query, 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 the Table Branch2 are placed under the Salary and Address fields.


We will make little change on 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 the Table Branch2 in 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 on 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 error when you attempt to view the output.


If you open the 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 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 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.


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 recordset at least once to ensure that they are in order. Once we are sure that the output is appearing correctly then you can apply sorting, 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.shtml
Configure Outlook for Lotus Notes
MS-Access and Email

Labels: msaccessQuery

Query Problem

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


Let us look into an example. NorthWind Traders Customer-wise Order-wise Sales Figures are kept in a Table. The 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 Total Sales Value of all Customers and they need only the Top 10 Customer’s with 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.


We need only the Orders Table now. Customers Table is being used as 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 messages will popup 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 to 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 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 using the earlier one as 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.


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.

---

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

Labels: msaccessQuery

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 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 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, 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 for a List Box as shown at the left side of the Form above.

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




Out of eleven items the first seven files are on 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 for 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 at 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



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



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




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

You can export this Form into your other Projects and use it there after linking the Microsoft Office Library File to your Project.


NB: At the beginning of the Code I have defined two Constants m_left = 500and 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.

---

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

Labels: msaccess animation

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 better than what we did earlier, better and attractive to the User as well as to the outside world.


Data processing has its own importance and I am not overlooking it here. Every Application that we develop is different and a challenge in 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, to the developer and to the User as well. I think that is one of the reasons why Windows or Microsoft Office Applications comes out with more features and better looking Programs all the time.


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:




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.


I will be preparing sample databases on most of the Articles that I have published so far and will upload them into the site shortly, so that you can download and try them out instantly.


We have moved recently into our own site: www.msaccesstips.com and I am in the process of updating the site and dealing with the initial teething problems. Once that is over I will concentrate on the above task.

1. Open the Form OptionCheck that we have created in the earlier example in 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.




• Number of elements in the OptionArray() Variable 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 the Case -2 and next two statements underneath it. Here it is inserted for demonstration purpose only.





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 = 0
k = 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 same as the Options Menu that we have used for our earlier example. The statement .labels(X).Text = MaxArray5obj(X) changed to .Checkboxes(X).Text = obj(X). Added few more lines of code to check, whether the User



• put check marks in more than one item, if so, forces to select one item only.


• clicked OK Button without selecting any of the Options. If so, asks to select one of the options before clicking OK Button and suggests 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 every thing went through well you will see the Office Assistant with Check Boxes as shown in the image at the top.



10. Try clicking the OK Button with check marks in more than one item. Try clicking OK Button without putting check mark in any of the options.

---

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

Labels: msaccess animation