Digital Clock on Main Switchboard

Introduction

You may find several pieces of gadgets like Clocks, Wrist Watches, and so on around you to check the Date and Time.  How about inserting a Digital Clock into your Project's Main Switchboard Form so that your User can check the Date and Time with a casual glance in the middle of their normal work without interrupting what they are doing?

Besides all that it is a matter of style to put an animated Digital Clock on the Main Switchboard.  It takes only a few lines of VBA Code and a Label control on the Form.

We can stop the Clock when you open other Forms over the Main Switchboard and re-start the Clock with the current time when the Main Switchboard becomes active again.

If you have not started using VBA in your Databases and don't know where to begin then this is the time to learn something interesting and simple.

Simple Clock Design.

Let us do it together.

1. Open one of your existing Databases.

2. If you have a Control Screen (Main Switchboard) in your database, then open it in Design View. You can open any Form in Design View to try this out.

3. Display the Toolbox (View - ->Toolbox) if it is not visible.

4. Click on the Label Tool (button face with the letter Aa on it).

5. Draw a Label where you would like the Digital Clock to appear on the Form.

   I have used a copy of the Main-Switchboard Form from Microsoft Access Sample Database - Northwind. An image of the Form in Design View, with a Label Control inserted, is given below:

   

6. Type at least one character (any character) in the Label control otherwise the label control will get deleted when you click somewhere else.

7. While the Label Control is still in the selected state display its Property Sheet (View - ->Properties).

8. Change the following Property Values as given below:

   • Name    =   lblClock
   • Width   =  1.5938"
   • Height   =  0.3125"
   • Border Style = Transparent
   • Font Size = 8
   • Font Weight = Bold
   • Text Align   =  Center

   Now, we need two lines of VBA Code to start running our Digital Clock. One line of code to start running the IntervalTimer of the Form through Form_Load() Event Procedure, immediately after the Switchboard Form is open.

9. Click on the top left corner of the Form, where the Horizontal and Vertical Rulers meet, to select the Form. Now you will see the Form-level Property Values in the Property Sheet you displayed earlier. If you have closed the Property Sheet follow Step-7 above to display the Property Sheet of the Form.

10. Find the On Load Property and click on it to select it.

11. Select [EventProcedure] from the drop-down list box.

12. Click on the build (. . .) button at the right edge of the Property Sheet to open up the VBA Module with an empty skeleton of the VBA Sub-Routine as given below:

    The Form_Load() Event and Code.

    Private Sub Form_Load()
    
    End Sub

13. Write (or copy) the following line of VBA Code in the middle of the above lines of Code:

    Me.TimerInterval = 1000 

    This line of code says that the program control should be passed to the Form's Timer Sub-Routine (we will write the code for that next) at every one-second interval. So whatever program we will write in the Timer Sub-Routine will be executed sixty times per minute or once every second. We will write a one-line Code in the Timer Sub-Routine to take the System Date and Time and update the Caption of the Label we have created at the beginning. So, we will be seeing a continuous-time change every second.

14. Select Timer from the drop-down control at the top of the VBA Module Window.

    The opening and closing lines of the Timer Sub-Routine will be inserted into the VBA Module.  You must write the line given in the middle by giving spaces and other punctuations correctly between double-quotes (date/time format string).

    Private Sub Form_Timer()
        Me.lblClock.Caption = Format(Now(), "dddd dd, mmm-yyyy hh:nn:ss")
    End Sub

    Alternatively, you may copy and paste all three lines of the VBA Code anywhere within the Form Module.

15. Close and Save the Form.

16. Open the Form in Normal View.

    

Your digital clock will show the Current Date and Time and the change of time is updated every second.

When you open other Forms or run different programs or macros the Main Switchboard may become inactive and we can turn off the clock temporarily till the Main Switchboard become active again. This will help other programs to run faster without interruption from the digital clock taking time to update the label on the Main Switchboard.

We will write two more lines of code for the On Deactivate() and On Activate() Event Procedures to turn Off (when the Main Switchboard is inactive) and to turn On (when the Main Switchboard is active again) respectively.

1. Open the Form in Design View.

2. Display the VBA Module of the Form (View - ->Code).

3. Copy and paste the following VBA Code into an empty area of the Module.

   Private Sub Form_Activate()
       Me.TimerInterval = 1000
   End Sub
   
   Private Sub Form_Deactivate()
       Me.TimerInterval = 0
   End Sub

   Trial Run of Form Activity.

4. Save the Form and Close it.

5. Open it in the normal view.

6. Open some other Forms over the Main Switchboard from your database.

7. Click on the Title Area of the second Form and drag away from the Main Switchboard Form so that you can see the Digital Clock on it.

   You can see that the clock is not getting updated.

8. Close the second Form.

Now the Main Switchboard Form becomes active and the Clock will start updating the Date/Time again.

Technorati Tags: Microsoft, animation

• Filter with Buildcriteria Function
• Save User-Specific Parameter Values
• Creating Using Form Custom Property
• MS-Access and Data Processing-2
• MS-Access and Data Processing

Read More

Label Animation Zoom-out Fade

Introduction.

Computer Programming is interesting because when you start doing something it will definitely lead to more than that and give you more ideas to try different things all the time. I have started with the Label Animation methods and thought of presenting one or two tricks, but we have gone through five different animation methods by now.

We are going to learn one more trick with the same Form and Labels, which we have used last week. All you need to do is to copy the new VBA Code given below into the Code Module of the Form.

A sample image of the Run of the Program is given below:

In this method, the Color of each letter of the Employee's name is slowly fading away in the distance. The size of each letter of the name is getting reduced at each step and the letters are displayed at a fixed time interval giving it an animated and three-dimensional effect.

Links to earlier Animation Styles.

If you have not tried out the earlier Label animation methods you may take a look at them by visiting the following pages:

1. Label Animation Style-1
2. Label Animation Style-2
3. Label Animation Variant
4. Label Animation Zoom-in Style
5. Label Animation in Colors

Let us try the new method.

The Design Task.

1. Make a copy of the Employees Form we have used last week with twenty small labels with the Name Property set with the Values lbl01 to lbl20.

   A sample image of the Form is given below for reference:

   

2. Open the Employees Form you have copied in Design View.

3. Display the Code Module of the Form (View - -> Code).

4. Copy and paste the following VBA Code into the Module, over-writing the existing Code:

   The Form Module Code.

   Option Compare Database
   Option Explicit
   
   Dim j, txtName, ctrl As Label, i
   
   
   Private Sub Form_Current()
   Dim t, xRGB As Long, fsize
   
   txtName = UCase(Me![first name] & " " & Me![Last name])
   fsize = 22
   For j = 1 To 20
      Set ctrl = Me("lbl" & Format(j, "00"))
      ctrl.FontSize = fsize
      ctrl.Caption = ""
      fsize = fsize - 1
   Next
      
   xRGB = RGB(10, 10, 10)
   i = xRGB
   For j = 1 To Len(txtName)
      Set ctrl = Me("lbl" & Format(j, "00"))
      xRGB = xRGB + i
      ctrl.ForeColor = xRGB
      ctrl.Caption = Mid(txtName, j, 1)
      
   t = Timer
   Do While Timer < t + 0.1
     DoEvents
   Loop
   
   Next
   
   End Sub

5. Save the Form with the Code and open it in the normal view.

6. Use the Record Navigation Button and move the record forward or back and watch how the Employee name is displayed on the header of the form.

1. Textbox and Label Inner Margins
2. Animating Label on Search Success
3. Label Animation Style-1
4. Label Animation Style-2
5. Label Animation Variant
6. Label Animation Zoom-in Style
7. Label Animation in Colors
8. Label Animation Zoom-out Fade

Read More

Label Animation in Colors

Introduction.

We have learned several label animation methods, through the last few posts dedicated to this topic, but we will try another trick, with the last design that you have created to try out the Zoom-in method.

In this method, we will play with colors and each letter of the name is applied with different colors generated randomly. Besides that, the name of the employee is plotted in a magical way by displaying odd-numbered letters first on odd-numbered labels i.e. lbl01, lbl03, lbl05, and even-numbered letters displayed on even-numbered labels i.e. lbl02, lbl04, lbl06, and so on. The letters will be plotted on the labels in a fixed time interval giving it an animated effect.

After this two-step action, the employee name display will be completed on the Header of the Form to view.

You can easily implement this method if you have tried out the earlier label animation method we have seen last week.

The Design Task.

1. Make a Copy of the Employees Form, which we have designed last week, and name it Employees_2 or any other name you prefer.

   The sample design of the Form, with twenty labels placed close together in the Header of the Form, with the Name Property Values set as lbl01 to lbl20, is given below:

   

2. Display the Code Module of the Form (View - -> Code), after opening the Form in Design View.

3. Copy and Paste the following VBA Code into the Form Module overwriting the existing Code.

   The Form Module Code.

   Option Compare Database
   Option Explicit
   
   Dim j, txtName, ctrl As Label
   
   Private Sub Form_Current()
   Dim t, m, R, G, B
   Randomize (Timer)
   
   txtName = Me![first name] & " " & Me![Last name]
   For j = 1 To 20
      Set ctrl = Me("lbl" & Format(j, "00"))
      ctrl.Caption = ""
   Next
   For j = 1 To Len(txtName) Step 2
      Set ctrl = Me("lbl" & Format(j, "00"))
      R = Int(Rnd(1) * 64)
      G = Int(Rnd(1) * 128)
      B = Int(Rnd(1) * 255)
   
      ctrl.ForeColor = RGB(R, G, B)
      ctrl.Caption = Mid(txtName, j, 1)
   
   t = Timer
   Do While Timer < t + 0.1
     DoEvents
   Loop
   Next
   
   For j = 2 To Len(txtName) Step 2
      Set ctrl = Me("lbl" & Format(j, "00"))
      R = Int(Rnd(1) * 255)
      G = Int(Rnd(1) * 128)
      B = Int(Rnd(1) * 64)
   
      ctrl.ForeColor = RGB(R, G, B)
   
      ctrl.Caption = Mid(txtName, j, 1)
   
   t = Timer
   Do While Timer < t + 0.1
     DoEvents
   Loop
   
   Next
   
   End Sub

4. Save the Form with the new VBA Code.

   The Demo Run.

5. Open the Form in the normal view.

6. Use the record navigation button to move the record forward or back and watch how the employee name is displayed in the header labels.

The sample screen in Normal View is given below:

Each character of the name will be displayed in different colors and in one-tenth of a millisecond time interval, giving it an animated effect. The color codes are generated randomly.

In this control program, we have used two delay loops, instead of using the Form's default Timer Interval Event Procedure.

You can modify the given value 0.1 in the program line Do While Timer < t + 0.1 to increase or decrease the animation speed.

For example, the value 0.5 will slow down the action and 0.05 will run faster.

1. Textbox and Label Inner Margins
2. Animating Label on Search Success
3. Label Animation Style-1
4. Label Animation Style-2
5. Label Animation Variant
6. Label Animation Zoom-in Style
7. Label Animation in Colors
8. Label Animation Zoom-out Fade

Read More

Label Animation Zoom-in Style

Introduction.

This week we will learn a different style of Label Animation technique. For those who would like to have a look at the earlier simple label animation methods, the page links are given below to visit those pages:

• Label animation Style-1
• Label Animation Style-2
• Label Animation Variant

In all the above methods we have used two labels, which moved towards each other from different directions and placed close together to form the displaying text to look like a 3D heading.

This week we will use a different approach and we need about twenty small labels placed close together horizontally. Each Label will display only one character of the Employee Name and all the employee names are less than twenty characters only.

Sample arrangement of Labels in the design view is given below:

Animation Style image.

The employee name will appear from left to right, character by character on each label, followed by the letters will Zoom-in and Zoom-out in sequence. The above screen is captured in the middle of that action.

The Design Task.

Let us get into the design task of this animation.

1. If you have not gone through the earlier examples, then Import the Employees Table from the Northwind sample database.

2. Click on the Employees Table and select Form from Insert Menu.

3. Create a Form as shown above and save it with the name Employees or any suitable name that you prefer.

4. Open the Form in Design View.

5. Select the Label Tool from Toolbox and draw a Label control in the header section of the Form.

6. Change the following property values of the Label as given below:

   • Name = lbl01
   • Width = 0.2528"
   • Height = 0.3549"
   • Top = 0.1563"
   • Left = 1.1146
   • Back Style = Transparent
   • Border Style = Transparent
   • Special Effect = Flat
   • Font Name = Verdana
   • Font Size = 14
   • Font Weight = Bold
   • ForeColor = 7500402

   Now, we must copy this label nineteen times and arrange them as shown in the first image, at the top of this page.

7. We must also change the Name Property Value of each label sequentially so that we can easily address each label in Programs to change their caption values to display the Employee's name.

8. Right-Click on the Label and select Copy from the displayed Shortcut Menu.

9. Select Paste from Edit Menu to create a copy of the Label.

10. Click and drag the new label and place it to the right of the first label. Don't worry about the misalignment of the labels, we will arrange them easily later.

11. Repeat the Paste action to create another eighteen labels.

    The Labels will appear automatically to the right of earlier labels.

12. Click on the second Label.

13. Display its Property Sheet (View- -> Properties).

14. Change the Name Property value to lbl02.

15. Repeat this method for other labels also and name them as lbl03, lbl04, and so on up to lbl20.

16. Click on the left side of the first label (lbl01) and drag the Mouse over all the twenty labels to select them all together.

17. Select Format - -> Align - -> Top to align all Labels horizontally.

18. Select Format - -> Align - -> Left to bring all the Labels close together.

    Now, that we have arranged the labels and their Name Property Values set to lbl01 to lbl20 all that is left to do is to copy the following Programs into the Form's Code Module.

19. Select Code from View Menu.

20. Copy and Paste the following VBA Code into the Module (overwriting the existing VBA Code, if any).

    The Form Module Code.

    Option Compare Database
    Option Explicit
    
    Private Const twips As Long = 1440
    Dim i, j, txtName, ctrl As Label
    
    Private Sub Form_Current()
    
    txtName = UCase(Me![first name] & " " & Me![Last name])
    i = 0
    Me.TimerInterval = 50
    
    End Sub
    
    Private Sub Form_Timer()
    Dim m, L1, L2
    i = i + 1
    If i > Len(txtName) Then
       For j = Len(txtName) + 1 To 20
        Set ctrl = Me("lbl" & Format(j, "00"))
        ctrl.Caption = ""
        Next
       Me.TimerInterval = 0
       i = 0
       animate
    Else
       Set ctrl = Me("lbl" & Format(i, "00"))
       ctrl.Caption = Mid(txtName, i, 1)
       ctrl.ForeColor = &H727272
    End If
    DoEvents
    
    End Sub
    
    Public Function animate()
    Dim k As Integer, t
    For k = 1 To Len(txtName)
      Set ctrl = Me("lbl" & Format(k, "00"))
      ctrl.ForeColor = 0
      ctrl.FontSize = 24
      DoEvents
      If k = 10 Then Exit For
      t = Timer
      Do While Timer < t + 0.09
        DoEvents
      Loop
      ctrl.FontSize = 14
    
    Next
    
    End Function

    The Trial Run.

21. Save the Form with the Code.

22. Open it in the normal view.

23. Use the Record Navigation Buttons to advance the record one by one forward/back to display the employee name animated.

Hope you like this method better and implement it in your Projects.

1. Textbox and Label Inner Margins
2. Animating Label on Search Success
3. Label Animation Style-1
4. Label Animation Style-2
5. Label Animation Variant
6. Label Animation Zoom-in Style
7. Label Animation in Colors
8. Label Animation Zoom-out Fade

Read More

Label Animation Variant

Introduction.

I have several Label Animation Styles in store for you and we have already seen two of them through the last two Articles:

1. Label Animation Style-1
2. Label Animation Style-2

Here, we will examine two variants of the animation method we tried last week. If you have gone through the procedures for creating the sample labels and programs, then we can try the same animation method with slightly different settings for the labels.

The change is required only on the position of the second label lbl2. The arrangement of labels of the last animation method is given below:

Both labels are placed apart and moved towards each other before they are positioned on the final destination to form the 3D Heading Style.

If you think it is better if the distance of both labels is reduced and place them a little bit closer so that the animation style has a better appeal then here it is for you to try it. 

The modified version of the above design is given below:

You can implement this variant of the earlier animation style by changing the Properties of the lbl2 label as given below. 

The Design Change.

1. Make a copy of the Employees Form with the earlier animation method and change the Form name to something like Employee2_1 or any other name you prefer.

2. Open the Employee2_1 in Design View.

3. Click on lbl2 (the label with the White-colored label and display its Property Sheet (View - -> Properties).

4. Change the Property Values as shown below. The only change that you need to make is the left Property value only. But, the full Property Values are reproduced here with the change in the Left Property:

   • Width = 2.9924
   • Height = 0.3549
   • Top = 0.125
   • Left = 3.6354
   • Back Style = Transparent
   • Border Style = Transparent
   • Font Name = Verdana
   • Font Size = 18
   • Text Align = Center
   • Font Weight = Bold
   • ForeColor = #FFFFFF

   Once you make the Left Property Value change the label will move into place as shown in the second image above.

5. Display the Code Module of the Form (View - -> Code) while the Employee2_1 Form is still in the design view.

6. Copy and paste the following modified Code into the Form Module over-writing the existing VBA Code.

   The Form Module VBA Code.

   Option Compare Database
   Option Explicit
   'Global declarations
   Private Const twips As Long = 1440
   Dim i, j
   
   Private Sub Form_Current()
   Dim txtName As String
   Me.lbl1.Left = 2.5194 * twips
   Me.lbl1.Top = 0.1569 * twips
   Me.lbl1.Width = 2.9924 * twips
   Me.lbl1.Height = 0.3549 * twips
   
   Me.lbl2.Left = 3.6354 * twips
   Me.lbl2.Top = 0.125 * twips
   Me.lbl2.Width = 2.9924 * twips
   Me.lbl2.Height = 0.3549 * twips
   
   txtName = UCase(Me![first name] & " " & Me![Last name])
   Me.lbl1.Caption = txtName
   Me.lbl2.Caption = txtName
   i = 0
   Me.TimerInterval = 25
   
   End Sub
   
   Private Sub Form_Timer()
   Dim m, L1, L2
   i = i + 1
   m = i Mod 2
   Select Case m
       Case 1
           L1 = Me.lbl1.Left
           L1 = L1 + (0.084 * twips)
           Me.lbl1.Left = L1
       Case 0
           L2 = Me.lbl2.Left
           L2 = L2 - (0.084 * twips)
           Me.lbl2.Left = L2
   End Select
   DoEvents
   If i > 12 Then
      Me.TimerInterval = 0
      i = 0
   End If
           
   End Sub

7. Save the Form and open it in Normal View.

8. Move the Employee Records forward using the record navigation buttons and watch the refined animation of employee names.

I hope you like the overall impact of the change in the earlier animation method.

The Design Changes.

We will look into another variant of the same animation method with the following design change:

In this method, we place lbl2 named label below lbl1 and slowly move them towards each other and place them in such a way that they form into a 3D header.

1. Make a Copy of the Employee2_1 Form and save it with the name Employee2_2.

2. Open the Form in Design View.

3. Click on the label with the White-colored text (with the Name Property Value lbl2) to select it.

4. Display the Property Sheet (View- -> Properties) and change the following Property Values as shown below:

   • Width = 2.9924
   • Height = 0.3549
   • Top = 0.5313
   • Left = 2.5729
   • Back Style = Transparent
   • Border Style = Transparent
   • Font Name = Verdana
   • Font Size = 18
   • Text Align = Center
   • Font Weight = Bold
   • ForeColor = #FFFFFF

5. Display the Code Module of the Form (View- ->Code).

6. Copy and Paste the following Code into the Form VBA Module over-writing the existing Code.

   The Form Module Code.

   Option Compare Database
   Option Explicit
   
   Private Const twips As Long = 1440
   Dim i, j
   
   
   Private Sub Form_Current()
   Dim txtName As String
   Me.lbl1.Left = 2.5521 * twips
   Me.lbl1.Top = 0.1569 * twips
   Me.lbl1.Width = 2.9924 * twips
   Me.lbl1.Height = 0.3549 * twips
   
   Me.lbl2.Left = 2.5729 * twips
   Me.lbl2.Top = 0.5313 * twips
   Me.lbl2.Width = 2.9924 * twips
   Me.lbl2.Height = 0.3549 * twips
   
   txtName = Me![first name] & " " & Me![Last name]
   Me.lbl1.Caption = txtName
   Me.lbl2.Caption = txtName
   i = 0
   Me.TimerInterval = 50
   
   End Sub
   
   Private Sub Form_Timer()
   Dim m, L1, L2
   i = i + 1
   m = i Mod 2
   Select Case m
       Case 0
           L1 = Me.lbl1.Top
           L1 = L1 + (0.084 * twips)
           Me.lbl1.Top = L1
       Case 1
           L2 = Me.lbl2.Top
           L2 = L2 - (0.084 * twips)
           Me.lbl2.Top = L2
   End Select
   DoEvents
   If i > 4 Then
      Me.TimerInterval = 0
      i = 0
   End If
           
   End Sub
   
   

7. Save the Form and open it in normal View.

8. Try moving the Employee records forward and watch the new method of the same style of animation.

Next week we will learn a different and interesting style of the label animation method.

1. Textbox and Label Inner Margins
2. Animating Label on Search Success
3. Label Animation Style-1
4. Label Animation Style-2
5. Label Animation Variant
6. Label Animation Zoom-in Style
7. Label Animation in Colors
8. Label Animation Zoom-out Fade

Read More

Label Animation Style-2

Introduction

Last week we learned a simple label animation method to display Employee Names, and made them appear character by character from the right side of the label to full view. The animated label gives the Form a lively look and makes it more interesting for the  User to work with the Screen.

This week we will learn a different label animation method with the same set of labels. In the earlier method, we used two identical labels to give the employee name a 3D effect.  We will stick with the same design, but the labels will be put horizontally apart in the design as shown below:

In the Current-Event Procedure of the Form the Labels will be moved in the opposite direction and finally assembled into a 3D heading style as shown below:

This happens every time an employee record becomes current.  The labels will restart from their original design position and slowly move in the opposite direction and stay in place to form the 3D Style Employee Name.

If you have gone through the earlier label animation design task, then it is easy to implement this one very easily.  All you have to do is to set the following Property Settings of lbl1 and lbl2 and copy the VBA routines into the Employee Form Module.

The Label Animation Design.

1. Open your database where you have tried the earlier example.

2. Make a copy of the earlier Employee Form we have tried the Label Animation and name it Employee2.

3. Open the Employee2 Form in Design View.

4. Click on the top label on the header of the Form and drag and move it to the right so that we will be able to click and select the labels individually and set their properties.

5. Select the Label with the name lbl1.

6. Display its Property Sheet (View - -> Properties) and set the following Property Values:

   • Name = lbl1
   • Width = 2.9924"
   • Height = 0.3549"
   • Top = 0.1569"
   • Left = 2.5194"
   • Back Style = Transparent
   • Border Style = Transparent
   • Font Name = Verdana
   • Font Size = 18
   • Text Align = Centre
   • Font Weight = Bold
   • ForeColor = 0

7. Select the Label with the name lbl2.

8. Display the Property Sheet and change the following Property Values:

   • Name = lbl2
   • Width = 2.9924"
   • Height = 0.3549"
   • Top = 0.125"
   • Left = 5.5313"
   • Back Style = Transparent
   • Border Style = Transparent
   • Font Name = Verdana
   • Font Size = 18
   • Text Align = Centre
   • Font Weight = Bold
   • ForeColor = 16777215

9. Display the Code Module of the Form (View- -> Code).

10. Copy and Paste the following VBA Code overwriting the existing Code:

    The Form Module VBA Code.

    Option Compare Database
    Option Explicit
    
    Private Const twips As Long = 1440
    Dim i, j
    
    Private Sub Form_Current()
    Dim txtName As String
    Me.lbl1.Left = 2.5194 * twips: Me.lbl1.Top = 0.1569 * twips: Me.lbl1.Width = 2.9924 * twips: Me.lbl1.Height = 0.3549 * twips
    Me.lbl2.Left = 5.5313 * twips: Me.lbl2.Top = 0.125 * twips: Me.lbl2.Width = 2.9924 * twips: Me.lbl2.Height = 0.3549 * twips
    txtName = Me![first name] & " " & Me![Last name]
    Me.lbl1.Caption = txtName
    Me.lbl2.Caption = txtName
    i = 0
    Me.TimerInterval = 5
    
    End Sub
    
    Private Sub Form_Timer()
    Dim m, L1, L2
    i = i + 1
    m = i Mod 2
    Select Case m
        Case 0
            L1 = Me.lbl1.Left
            L1 = L1 + (0.084 * twips)
            Me.lbl1.Left = L1
        Case 1
            L2 = Me.lbl2.Left
            L2 = L2 - (0.084 * twips)
            Me.lbl2.Left = L2
    End Select
    DoEvents
    If i > 35 Then
       Me.TimerInterval = 0
       i = 0
    End If
            
    End Sub

11. Save the Employee2 Form.

12. Open the Employee2 Form in normal view

13. Click on the Record Navigation control to advance each record forward one by one.

    For each record change, you will find the Employee Name Labels move towards each other and assemble into place to form a 3D heading.

1. Textbox and Label Inner Margins
2. Animating Label on Search Success
3. Label Animation Style-1
4. Label Animation Style-2
5. Label Animation Variant
6. Label Animation Zoom-in Style
7. Label Animation in Colors
8. Label Animation Zoom-out Fade

Read More

Label Animation Style-1

Introduction

A Form with lively Objects like Animated Command Buttons, Animated GIFs, Moving Text, etc., makes it more interesting for the User to work with MS-Access Applications.  MS-Access doesn't have any of those things built-in or ready-made to use quickly. Our imagination and ingenuity is the only tool available to create something with the existing, strictly for business-like features in MS-Access.

After working with MS-Access for some time I got bored with the repeated use of the same objects without any changes for designing the Applications.  I wanted to make something more eye-catching to kill the boredom. As the saying goes "Necessity is the father of inventions", I have developed some interesting features like Command Button Animation, Animated Floating Calendar, usage of Office Assistant in MsgBoxes, Animating Label on search success, Reminder Ticker, and others.

Today we will learn a simple Label Animation Technique with an example in the Employees Form from the Northwind.mdb sample database.

1. Import the following objects from the Northwind sample database.:

   • Table: Employees
   • Form: Employees

   If you open the Employees Form in Normal View you can see that the Employee's; Name; Firstname and Lastname joined together; is displayed in the Header of the Form in a Text Box. When you make some other record current on Form the employee's full name changes on the Form Header Textbox as well.

   We will add some animated effect to this and make the employee name appear slowly from the right edge of the label to the left, character by character, and move into place.

2. Open the Employees Form in Design View.

3. Delete the existing TextBox that displays the Employee's Name.

4. Create a Label on the Header of the Form.

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

   • Name = lbl1
   • Width = 3.5"
   • Height =0.32"
   • Back Style = Transparent
   • Border Style = Transparent
   • ForeColor = 16777215
   • Font Name = Times New Roman
   • Font Size = 18
   • Font Weight = Bold
   • Text Align = Right

6. Display the Code Module of the Form (View - ->Code).

7. Press Ctrl+A to highlight the existing VBA Routines and press the Del key to delete them.

8. Copy the following VBA Code and paste it into the Code Module of the Form.

   Label Animation Code.

   Option Compare Database
   Option Explicit
   
   Dim txt1 As String, txtlen As Integer
   Dim j As Integer, txt2 As String
   
   Private Sub Form_Timer()
   j = j + 1
   If j <= txtlen Then
     txt2 = Left(txt1, j)
     Me.lbl1.Caption = txt2
     'Me.lbl2.Caption = txt2
     Else
     Me.TimerInterval = 0
   End If
   
   End Sub
   
   
   Private Sub Form_current()
          
   txt1 = UCase(Me![FirstName] & " " & Me![LastName])
   txtlen = Len(txt1)
   j = 0
   Me.TimerInterval = 50
   
   End Sub

9. Save and Close the Employees Form.

10. Open the Employees Form in the normal view.

When you open the Form you will find the Employee Name is moving into place slowly from right to left, character by character, in the header label.


11. Click on the Navigation button to move the records forward one by one.

12. The names of the employees will be displayed in the same style by moving from the right edge of the label to the left.

Fancy Work to the Label.

We will add little fancy work to the Employee Name with a three-dimensional backlit effect by copying the Label and placing it over the existing one. See the finished design of the image is given below:

1. Open the Employee Form in Design View.

2. Select the header label.

3. Click on the Copy, Paste ToolBar buttons to make a copy of the label (or select Copy, Paste Options from the View Menu).

4. Click on the new Label to select it, if it is not in the selected state.

5. Display the Property Sheet of the Label (View - ->Properties).

6. Change the following Property Values as shown below:

• Name = lbl2
• ForeColor = 128

7. Move the label up and over the first label and place it slightly down from the top edge of the first label and to the left, about the thickness of a hair.  The sample image design view is given below:



I have already included the line of code necessary to run this trick.  All you have to do is to enable that line in the VBA code, do the following:

8. While the Form is still in design view display the VBA Module (View - ->Code)

9. You will find the following line of code in the Sub Form_Timer() Event Procedure in a different color (most probably in green color):

'Me.lbl2.Caption = txt2

10. Find the ' (single quote) character at the beginning of this line and delete it.

11. Save and Close the Form.

12. Open the Form in the Normal View.

    Now you will find the Employee Names appearing in animated characters, with the 3D effect, as shown in the second image above.

1. Textbox and Label Inner Margins
2. Animating Label on Search Success
3. Label Animation Style-1
4. Label Animation Style-2
5. Label Animation Variant
6. Label Animation Zoom-in Style
7. Label Animation in Colors
8. Label Animation Zoom-out Fade

Read More

Run Slide Show when Form is Idle

Introduction

This week, we will create a Slide Show of images on a Form that runs when the Form remains idle for a certain period of time.  It works something like the following:

• When the Main Switchboard Form remains idle for about one minute the Slide Show runs and each image will change at a fixed interval of about 6 seconds. The Idle Time and Interval Time can be changed according to your specific needs.

• The idle time of the Form is calculated based on the inactivity (i.e. No interaction with the User) of the Main Switchboard Form and the active control on the Form remains active for more than one minute.

• If you click on a Command Button or List box or any other control other than the active control on the Main Switchboard Form then the Form comes out of its idle state and the Slide Show stops.

• Checking for the idle state of the Form starts again from that point onwards with the new active control. If the same control remains active for more than one minute then the slide show starts again.

• If you open another Form over the Main Switchboard Form then the slide show sleeps until the Main Switchboard Form becomes active again.

• You need a few Bitmap Images of Landscapes or Photos and you can use as many images as you like for the Slide Show. All the images should have the same name with a Sequence Number at the end like 1.bmp, 2.bmp, 3.bmp, and so on. You can use jpg or gif and other images but this will display a progress bar for a brief moment while loading the image into the Image Control. All images must be of the same type.

Sample Main Switchboard Form

A sample Main Switchboard Form with the active Slide Show is given below:

Click to Enlarge

The Design Task

1. To try out this interesting trick; first, organize a few Bitmap Images into a folder as explained above.

2. Make a copy of your existing Main Switchboard Form (Control Screen).

3. Open the Form in Design View.

4. Make enough room on the Form to create an Image Control on the Form.

5. Display the Toolbox (View - - >Toolbox), if it is not visible.

6. Select the Control Wizards button on the Toolbox to make it active.

7. Select the Image control Tool from the Toolbox and draw an Image Control on the Form where you want the SlideShow to appear.

8. The Common Dialog Control will display. Browse to the Image location and select the image with Serial Number 1 (Image1.bmp).

   The selected image will appear in Image Control.

9. While the Image control is still in the selected state; display the Property Sheet (View - -> Properties) and change the following Property Values as indicated below:

   • Name = ImageFrame

   • Size Mode = Zoom

   NB: We need part of the Picture Property Value (the location address of the Images) to make changes in the Program that we are going to introduce into the Code Module of the Form. So, note down the image location address on paper from the Picture Property or copy and paste it into a Notepad document for later use.

10. Display the Code Module of the Form (View - -> Code).

11. Press Ctrl+A to highlight the existing VBA Code in the Module, if present, and press Delete to remove them.

12. Copy and Paste the following Code into the Form Module.
    
    

    Switchboard Form Module VBA Code (Revised Code)

    Option Compare Database
    
    Dim actForm As String, actControl As String, idletime As Integer
    Dim oldForm As String, oldControl As String, imageNo As Integer
    Dim s_path As String
    
    Private Sub Form_Activate()
        Me.TimerInterval = 1000
    End Sub
    
    Private Sub Form_Deactivate()
        Me.TimerInterval = 0
        Me.ImageFrame.Visible = False
    End Sub
    
    Private Sub Form_Load()
    DoCmd.Restore
    idletime = 0
    imageNo = 0
    s_path = CurrentProject.Path
    Me.ImageFrame.Visible = False
    Me.TimerInterval = 1000
    End Sub
    
    Private Sub Form_Timer()
    Dim txt As String
    
    txt = "Slide-Show Starts In: "
    
    actForm = Screen.ActiveForm.Name
    actControl = Screen.ActiveForm.ActiveControl.Name
    
    If actForm = oldForm And actControl = oldControl And actForm = "SlideShow" Then
        idletime = idletime + 1
        
        If idletime > 15 And idletime < 31 Then
            
          Me.Lblup.Visible = True
          Me.Lblup.caption = txt & (30 - idletime) & " Seconds."
            
        Else
            Me.Lblup.Visible = False
        End If
    Else
        oldForm = actForm
        oldControl = actControl
        idletime = 0
        Me.ImageFrame.Visible = False
        Me.Employees.Visible = False
        DoEvents
    End If
    
    If idletime > 30 Then
        Me.Lblup.Visible = False
        DoEvents
       If idletime Mod 5 = 1 Then
            Me.ImageFrame.Visible = True
            Me.Employees.Visible = True
            imageNo = imageNo + 1
            imageNo = IIf(imageNo < 1 Or imageNo > 9, 1, imageNo)
            Me.ImageFrame.Picture = s_path & "\" & imageNo & ".bmp"
            
            DoEvents
       End If
    End If
    
    End Sub
    
    

    Make Important Changes to References

13. Change the "SlideShow" reference in the VBA code line with your own Main Switchboard Form Name.

14. Change Number 9, in the second line with bold letters, to match with the number of Images you have saved for the Slide Show as explained above.

15. If your pictures are not Bitmap Images then change the file extension ".bmp" to indicate your image type.

16. Save and Close the Form.

17. Open the Main Switchboard Form in Normal View.

18. Wait for one minute to begin the Slide Show.

Interrupting the Slide Show.

When the Slide Show is running, if you click on a Control other than the active control on the Form the Slide Show will stop and the image will disappear.

If you don't click on any other control on the Form and the time elapsed is more than 30 seconds, since your last click on a Control, the Slide Show will run again.

If you want to increase or decrease the idle time of the Form then change the value in the VBA line If idle time > 30 Then to an appropriate value you prefer.

Each Image stays displayed for about 6 seconds. If you want to increase or decrease the display time, then change the Value in the VBA line

If idle time Mod 5 = 1 Then to the required value you prefer the time delay between two slides for viewing.

Download the Demo Database from the Link given below:

Download SlideShow.zip

NOTE: Unzip the Database with Photos into a Temporary Folder and open the Database from there. Read the Notes given in the Form for details on how it runs and how to disable it etc.

• Detail and Summary from the Same Report
• Hiding Report Lines Conditionally-3
• Hiding Report Lines Conditionally-2
• Hiding Report Lines Conditionally
• Network and Report Page Setup-3

Read More

Office Assistant And Msgbox Menus-3

After Clickable Menu Options - Access 2003.

After going through the earlier Articles on this subject I hope that the Readers are now familiar with programming the Balloon Object of Microsoft Office Assistant. You have seen that you can use this feature with a few lines of customizable VBA Code to display MsgBox text formatted with Color, underline, and with your favorite images on them. We can display Menus on them to obtain responses from Users, besides the buttons that we normally use like OK, Cancel, Yes, No, etc.

Since this article is the third part of this series I suggest that new Readers may go through the earlier Documents on this subject to learn interesting and simple ways to use this feature in MS-Access before continuing with this Article. Links to those Articles are given below:

• Color and Picture in MsgBox -Access 2003.
• Office Assistant and MsgBox Menus - Access 2003.
• Office Assistant and MsgBox Menus-2 - Access 2003.

Last week we learned how to display Clickable Menu Options in Message Box with the use of Office Assistant. The Image of that example is given below.

We have displayed the Menu Options in the Message Box with the Labels Property of the Balloon Object of Office Assistant.

Check Box Menu Options

Here, we will learn how to display Menu Options with Checkboxes and how responses from the User can be obtained, examined, and execute actions that are programmed for each choice made. The example code and the sample image of MsgBox that displays the Check-Box Menu is given below:

Public Function ChoicesCheckBox()
Dim i As Long, msg As String
Dim bln As Balloon, j As Integer
Dim selected As Integer, checked As Integer

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Select Data Output Option"
    .Checkboxes(1).text = "Print Preview."
    .Checkboxes(2).text = "Export to Excel."
    .Checkboxes(3).text = "Datasheet View."
    .Button = msoButtonSetOkCancel
    .text = "Select one of " & .Checkboxes.Count & " Choices?"
    i = .Show

    selected = 0
    If i = msoBalloonButtonOK Then
        'Validate Selection
        For j = 1 To 3
            If .Checkboxes(j).checked = True Then
                selected = selected + 1
                checked = j
            End If
        Next

       'If User selected more than one item
        'then re-run this program and force the
        'User to select only one item as suggested
        'in the message text.

        If selected = 0 or selected > 1 Then
           Call ChoicesCheckBox
        Else
            Select Case checked
                Case 1
                    Debug.Print .Checkboxes(checked).text
                Case 2
                    Debug.Print .Checkboxes(checked).text
                Case 3
                   Debug.Print .Checkboxes(checked).text
            End Select
        End If
    End If
End With

End Function

Like the Labels Property Array, the dimension of CheckBoxes also can be up to a maximum of five elements only.

In our earlier example, we have not used the OK or Cancel buttons along with the Labels based Menu because the Balloon Button (msoBalloonTypeButtons) based options were clickable and accepted the clicked item as a valid response and dismisses the Office Assistant on this action. The clicked item's index number is returned as the response value and it was easy to check this value and execute the action accordingly.

But in the case of Checkboxes, this is a little more complex because of the following reasons

1. The CheckBoxes can be either in checked or in the unchecked state, which needs to be validated.
2. The User may put check marks on one or more Options at the same time. If this cannot be allowed, then there must be a validation check and force the User to make the selection of one item only.
3. If the User has the option of selecting more than one item then the program must be written to execute more than one action based on the combination of selections made.
4. In either case, we have to inspect the checked or unchecked state of each element of the CheckBox Array to determine the validity of Menu selection.

Validating the Checked/Unchecked Items

In the example code given above the User can select only one item at a time.

• In the validation check stage of the code, first, we are checking whether the User has clicked the OK Button or not.
• If she did then in the next step we take a count of all check-marked items, in the Variable selected.
• If the value in the Variable selected is zero (The User clicked the OK Button without selecting any option from the list) or selected more than one item then the Program is called again from within the ChoicesCheckBox() Function itself. This will refresh the Menu, removes the check marks, and display it again. This will force the user to make only one selection as suggested in the message or she can click Cancel Button.
• In the next step, the action is programmed based on the selection made by the User.
• If the User is allowed to put check marks on more than one item (depending on the purpose of the MessageBox-based Menu) then the validation check and the execution of actions can be different and the code must be written accordingly.

The methods which I have introduced to you and explained in these three Articles are good to learn the basics of this feature and easy to understand the usage of different Properties of Balloon Object of Office Assistant.

But, you will appreciate the fact that duplicating and customizing these Codes everywhere in your Programs for different needs is not advisable. This will increase the size of your database, no flexibility in usage of Code and it is not good programming practice either.

You may go through the Articles (links are given below) published earlier on this Subject that it shows, how to define Public Functions like MsgOK(), MsgYN(), MsgOKCL(), and others with the use of Office Assistant. It simplifies the usage of this feature, without duplicating the code, and can use them freely anywhere in your Programs like MS-Access MsgBox() Function.

The above Function Names themselves suggest what kind of Buttons will appear in the Message Box when they are called with the minimum Parameter Value of Message Text alone or Message Text and Title Values.

1. Message Box using Office-Assistant
2. Message Box with Options Menu
3. Office Assistant with CheckBox Menu

A comparison of the above User Defined Function usage with the MsgBox is given below for reference. The underscore character in the text indicates the continuation of lines and should not be used when all values are placed on the same line.

MS-Access MsgBox() usage	Office-Assistant-based User Defined Function usage
MsgBox "Welcome to Tips and Tricks"	MsgOK "Welcome to Tips and Tricks"
X = MsgBox("Shut Down Application", vbQuestion+vbDefaultButton2+vbYesNo, _"cmdClose_Click()")	X = MsgYN("Shut Down Application", _ "cmdClose_Click()")
X = MsgBox( "Click OK to Proceed or Cancel?", _ vbOKCancel+vbDefaultButton2+vbQuestion, _ "MonthEndProcess()")	X = MsgOKCL("Click OK to Proceed or Cancel?", _ "MonthEndProcess()")

• Network and Report Page Setup-3
• Network and Report Page Setup-2
• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success

Read More

Office Assistant And Msgbox Menus-2

Continued from Last Week. - Access 2003.

This is the continuation of last Week's Article: Office Assistant and MsgBox Menus. Readers may go through that Page first, if you have not already done so, before continuing.

Last week we learned, how to use the Office Assistant for MsgBox in a simple way and saw how to create a Menu and present it to the User, with the Labels Property of the Balloon Object.

Labels().Text Property

We have seen that we can display the Menu from Labels().Text Property in three different ways by setting the Values of Balloon Type Property:

1. Balloon Type = msoBalloonTypeButtons

   The User can click on any of these buttons to select one of the options presented.

2. Balloon Type = msoBalloonTypeBullets
3. Balloon Type = msoBalloonTypeNumbers

The second and third Balloon Type Values display the Labels().Text in different styles, but they cannot be selected by the User. So, we can use these options for different situations in Programs.

Let us look a little closer at the overall behavior of the Balloon Object when you display MsgBox with Office Assistant, to understand it better.

If you go back and try those earlier examples, you can see that while a MsgBox with the Office Assistant's Balloon Object is displayed you cannot click or work with any other object in the database unless you dismiss the Balloon by responding to the actions you are suggested to do, like clicking on the OK or Cancel Button and so on. This is true in the case of the normal MsgBox() Function of MS-Access too. But, Office Assistant has some, solutions to this rigid behavior of Message Boxes.

When the Balloon is displayed, you cannot open a Form or Report to check something on it, before clicking the Yes or No Button on the MsgBox because the Mode Property of the Balloon Object is set with the default value msoModeModal.

The Mode Property of Message Balloon

I have not introduced the Mode Property to you in the earlier examples to avoid overcrowding of usage rules. Once you are through with the basics it will be easier to understand other things associated with it better. You have already seen that you can create Message Boxes using Balloon Object of Office Assistant with a few lines of Code and display them in style without using these properties.

The Mode property can be set with three different values to control the behavior of the Balloon Object.

Mode = msoModeModal (default).

This value setting forces the user to dismiss the Balloon by responding to the suggested action before doing anything else, like normal MS-Access MsgBox. You have to click on one of the Buttons (if more than one is showing like OK and Cancel) on the MsgBox to dismiss the Balloon before you are allowed to do anything else.

Mode = msoModeModeless.

This value setting allows the User to access other database objects, while the Balloon is active. But, this forces the use of another Property CallBack, to run a separate Sub-Routine to do the testing of a selection of choices or do different things based on the choice and finally to Close the Balloon Object.

Mode = msoModeAutoDown.

This value setting dismisses the Balloon (MsgBox) automatically if you click somewhere else ignoring the Balloon.

The first and last Mode Property value setting and their usages are very clear. But, the second one (msoModeModeless) needs the CallBack Property Value set to the name of a valid Sub-Routine otherwise the Balloon will not work. You cannot load the CallBack Property with an empty string either because that will trigger an Error.

So let us see how we can re-write the earlier Program to understand the usage of Mode Property sets to the value msoModeModeless and Callback Property with the name of a Sub-Routine to handle the selection of options displayed in the Menu.

The Mode and CallBack Property.

The Modified Code with Mode and CallBack Property Settings and the sample code for the required Sub-Routine MyProcess() is given below:

Public Sub Choices()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print. "
    .labels(3).text = "Pivot Chart. "
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of  " & .labels.Count & " Choices? " 
    .mode = msoModeModeless
    .Callback = "myProcess"
    .Show
End With

End Sub

Sub MyProcess(bln As Balloon, lbtn As Long, lPriv As Long)
Assistant.Animation = msoAnimationPrinting
Select Case lbtn
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select
 bln.Close
End Sub

The Call Back Property is set with the Sub-Routine name myProcess. When the user clicks on one of the Options from the displayed MsgBox the MyProcess() Sub-Routine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Sub-Routine when called:

1. The Balloon Object bln.
2. A Long Integer type value lbtn (BalloonTypeButton) identifying the Option clicked.
3. A Long Integer type value lPriv (Private) uniquely identifies the Balloon that is called the Sub-Routine, if there is more than one Balloon active in memory at the same time.

NB: There is no such thing as a collection of Balloon Objects. But, you can create an Array of Variables with Balloon Object, define different Property Settings for each of them, and the Show() method is run with their respective index numbers in Programs when you need them to appear.

The lbtn Variable will have the value of the User's choice. This is tested in the Sub-Routine and runs the Docmd.OpenReport action within the Select Case End Select Statements.

The Assistant.Animation = msoAnimationPrinting line is placed in the Sub-Routine, rather than in the main Program as part of the Balloon Object Property setting, to animate the printing action only after the User makes a selection from the displayed Menu otherwise, the printing animation will run before the selection of choices.

The bln.close statement dismisses the MsgBox.

Next week we will learn how to use the CheckBoxes().Text Property Values of the Balloon Object to display a Menu with CheckBoxes in MsgBox.

• Network and Report Page Setup-2
• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color

Read More

Office Assistant and MsgBox Menus

Introduction - Access 2003.

In last Week's Article: Color and Picture in Message Box we have seen some methods to use the Office Assistant quickly, to display a Message Box with formatted text. We are not going to discuss further the formatting part, but I have a general feeling that Readers would like to know how this simple method can be used to obtain responses from Users, out of several options presented to them, and do different things based on their selection of choices.

I have already covered this topic by creating Functions like MsgOK(), MsgYN(), MsgOKCL(), etc., with the use of Office Assistant. These can be called with only the Message Text Value alone or Message Text and Title Values as Parameters, from anywhere within the Application like the built-in Function MsgBox(). I made an attempt to simplify the usage of Office Assistant through the above-mentioned Functions and others, which otherwise need several property values to be passed to the Office Assistant's Balloon Object for displaying.

But, in the simplification process, the method used in those Functions is not fully understood by several readers.

The Links to those earlier posts are given below for reference:

1. Message Box with Office Assistant -Access 2003
2. Message Box with Options Menu - Access 2003
3. Office Assistant with CheckBox Menu - Access 2003.

In the example code presented in last week's Post: Color and Picture in Message Box, we came across several Properties of the Balloon Object of Office Assistant that can be set with values before the Message Box is displayed.

We are going to work with these properties directly so that it is easy to understand their usage, rather than passing values for them through the Parameter List in the function definition.

Message Balloon Properties

Following are some of these properties:

• Animation
• Icon
• Heading
• Text
• Balloon Type
• Button

Microsoft Office Object Library

NB: If you have not already attached the Microsoft Office Object Library to your Database, try out the examples given here, then do that by following the procedure given below.

• Press Alt+F11 to display the VBA Code Window (or Tools - - >Macro- ->Visual Basic Editor).
• Select References from Tools Menu.
• Find Microsoft Office Object Library in the Available List and put a checkmark to select it.
• Click OK to close the Dialog Box.

Message Balloon Animation Constants

The value for Animation alone has about thirty-five different choices which are defined as Constants in the Microsoft Office Object Library. The constant values for Balloon Properties Animation, Button, Icon, and Balloon Type are given below for reference:

Animation	Icon
msoAnimationAppear msoAnimationBeginSpeaking msoAnimationCharacterSuccessMajor msoAnimationCheckingSomething msoAnimationDisappear msoAnimationEmptyTrash msoAnimationGestureDown msoAnimationGestureLeft msoAnimationGestureRight msoAnimationGestureUp msoAnimationGetArtsy msoAnimationGetAttentionMajor msoAnimationGetAttentionMinor msoAnimationGetTechy msoAnimationGetWizardy msoAnimationGoodbye msoAnimationGreeting msoAnimationIdle msoAnimationListensToComputer msoAnimationLookDown msoAnimationLookDownLeft msoAnimationLookDownRight msoAnimationLookLeft msoAnimationLookRight msoAnimationLookUp msoAnimationLookUpLeft msoAnimationLookUpRight msoAnimationPrinting msoAnimationRestPose msoAnimationSaving msoAnimationSearching msoAnimationSendingMail msoAnimationThinking msoAnimationWorkingAtSomething msoAnimationWritingNotingSomething	msoIconAlert msoIconAlertCritical msoIconAlertInfo msoIconAlertQuery msoIconAlertWarning msoIconNone msoIconTip
Button	BalloonType
msoButtonSetAbortRetryIgnore msoButtonSetBackClose msoButtonSetBackNextClose msoButtonSetBackNextSnooze msoButtonSetCancel msoButtonSetNextClose msoButtonSetNone msoButtonSetOK msoButtonSetOkCancel msoButtonSetRetryCancel msoButtonSetSearchClose msoButtonSetTipsOptionsClose msoButtonSetYesAllNoCancel msoButtonSetYesNo msoButtonSetYesNoCancel	msoBalloonTypeButtons msoBalloonTypeBullets msoBalloonTypeNumbers

Animation and Icon properties are always set with one of the above values based on what we are trying to convey to the User. By default, the OK button will appear. If any other Button or Buttons Group is required, then the Button Property must be set to one of the above values. The Balloon Type is used with Labels Property only. We will look into the Labels property in the latter part of this Article.

I am sure when you go through these simple, straightforward examples you will be better informed about the usage of Office Assistant, and its methods and will start using them in your Applications.

I will reproduce the Code here with simple changes, which we have seen in last week's article, and go through it before we make changes in them for our new examples.

Public Sub MyMsgBox()
Dim strMsg As String
Dim strTitle As String

strTitle = "Assistant Test"
strMsg = "Wecome to MS-Access Tips and Tricks"

With Assistant.NewBalloon
    .Icon = msoIconAlertInfo
    .Animation = msoAnimationGetAttentionMajor
    .Heading = strTitle
    .text = strMsg
    .Show
End With

End Sub

Use the Above Code for a Demo Run

Press Alt+F11 to display the VBA Editing Window. Select the Module option from Insert Menu to create a new Standard VBA Module. Copy and Paste the above Code into the Module. Click somewhere in the middle of the Code and press F5 to run it.

In the above example, the Show() method displays the Message Box after setting the other Property Values. The Text property value shows the body text of the Message Box and Heading is the Title Text in Bold letters. The Animation property can be set with one of the 35 different options given above. The Icon Property value we have changed to Information type here.

If we want to obtain the responses of the User to do different things then we need to bring the Button Property into the Code. Let us say if the User needs to proceed with the report preparation process then she must click the OK Button otherwise Cancel Button. To evaluate the response received from the User and to take action accordingly we must write code for that further down in the Routine.

Modified VBA Code

Let us see how we can do this with changes to the above Code. The modified program is given below:

Public Sub MyMsgBox ()
Dim strMsg As String
Dim strTitle As String
Dim R As Long

Title = "Assistant Test"
msgTxt = "Proceess Weekly Reports...?"

With Assistant.NewBalloon
.Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMajor 
 .Button = msoButtonSetOkCancel
    .Heading = strTitle
    .text = strMsg 
 R = .Show
End With

If R = -1 Then
'User Clicked OK Button
    DoCmd.RunMacro "ReportProcess"
End If
End Sub

Compare the changes made in the new Code with the earlier one to find the difference.

If the user clicked the OK Button (-1 is returned in Variable R, Cancel=-2) then the Report Process Macro is run otherwise the Program ends doing nothing.

We can give different choices to the User in the form of a Menu with the use of Labels Property and perform the actions based on the User's responsibility. See the example code given below:

MsgBox Based Menu Choices.

Public Sub Choices()
Dim R As Long
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print."
    .labels(3).text = "Pivot Chart."
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of " & .labels.Count & " Choices?"
    R = .Show
End With

Select Case R
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select

End Sub 

 Note: You may copy and paste the code into the VBA Module and modify it with the appropriate changes before attempting to Run it.

The dimension of the Labels() Property Value can be up to a maximum of 5 only. The Balloon Type Value msoBalloon Type Buttons allow the User to click on one of the Options to select it and the Index value of the item clicked is returned in Variable R. We have set the Value Button = msoButtonSetNone to remove the OK Button from appearing so that the User will click only on one of the Options displayed.

Balloon Type Bullets and Numbers.

There are two more Balloon Type Property values available: msoBalloonTypeBullets and msoBalloonTypeNumbers. These are not selectable, like msoBalloon Type Buttons, and are used only for displaying information.

Compare the following Code with the earlier one to see the difference in changed Property Values.

Public Sub InfoDisplay()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Reminder"
    .Icon = msoIconAlertInfo
    .labels(1).text = "MIS Reports."
    .labels(2).text = "Trial Balance."
    .labels(3).text = "Balance Sheet."
    .BalloonType = msoBalloonTypeBullets
    .text = "Monthly Reports for User Departments"
    .Button = msoButtonSetOK
    .Show
End With

Sample Images with Balloon Type Property Changes are given below:

We will continue this discussion next week to see more ways to use the Office Assistant with Labels and CheckBoxes.

• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color
• MS-Access and Reference Library

Read More