Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.



Microsoft Access (the Relational Database Management System), is the Jewell among the MS Office Suite of Applications, with the superior design tools and built-in Visual Basic Language for programming. These Pages are not intended for beginners, but for those who have the basic knowledge of Database designing with MS Access Tables, Queries, Forms, Reports, and Macros and have a general understanding of Visual Basic for Applications Language (the Programming Language of all MS Office Applications) Modules, Class Modules, Functions, and Event Procedures.

When an Application is developed under any Database Management System it should be user-friendly and visually pleasing too. An unattractive Database design spoils the appeal of the whole Project and it reflects the developer's lack of creativity as well as his inability to approach the data processing tasks effectively.

Here, I would like to present some Programs, that I have developed to use on the Form controls with some amusing twists in their use, in my own Projects and I am sure that you will find them interesting too.

Demo Programs are written for Access 2000 and will run in Access 97 versions too.

Attaching Access System Reference Library Files.

First of all, please ensure that the essential Access Reference Library Files are attached to your Project. Do the following to configure Microsoft Access:

Display the Visual Basic Editor Window. Select the Visual Basic Editor Option from the Tools Menu or Code from the View Menu.

On the Visual Basic Editor Window, select References' from the Tools menu. Put a check mark on the following Library Files in the Available References Dialogue Control:

  1. Visual Basic for Applications
  2. Microsoft Access 11.0 Object Library
  3. OLE Automation.
  4. Microsoft DAO 3.6 Object Library
  5. Microsoft ActiveX Data Objects 2.5 Library
  6. Microsoft Office 11.0 Object Library
  7. Microsoft Visual Basic for Applications Extensibility 5.3

The Version numbers of Access Library Files may change for different versions of Microsoft Office. The Library Files List is in alphabetical order. The selected items will appear at the top of the list.

Design Modification

On the MS Access user interface, the command button control is the most frequently used control besides text boxes, with appropriate labels on it indicating the task executed, when clicked. Besides this, we never gave much attention to this control.

Here, we are trying to make the command button control a more interesting and attractive control on the Form. We will introduce some animated actions on the Command Button to make it lively on the screen.

We can do this with an addition of a rectangle, filled with some dark color, behind the command button, and with a few lines of Visual Basic Code.

Animating Command Button

The animation design is very simple. There is a rectangular control, with almost the same dimension as the Command Button and with a black background color, that is kept hidden behind the Button. When the Mouse Moves over the Command Button, the Button moves slightly up and to the left, showing part of the rectangle control, like the shadow of the command button. When the Mouse is moved out, the Command Button goes back into its original state, hiding the rectangle control again. 

When this action is repeated in quick succession the Button gives a lively appearance by moving up and down, showing the shadow on and off. Check the images given above to show both states of the Command Button.  

Copy and paste the following VBA code into a new Global Module:
Public Function ButtonAnimate(ByVal strForm As String,ByVal mode As Integer, ByVal lblName As String)
'Command Button Animation
'Author : a.p.r. pillai
'Date : September 2006
Dim FRM As Form, l As Long, t As Long
On Error GoTo ButtonAnimate_Err
Set FRM = Forms(strForm)
l = FRM.Controls(lblName & "Box").Left
t = FRM.Controls(lblName & "Box").Top
If (mode = 1) And (FRM.Controls(lblName & "Box").Visible = False) Then
    FRM.Controls(lblName & "Box").Visible = True
    FRM.Controls(lblName).Left = l - (0.0208 * 1440)' 0.0208 inches
    FRM.Controls(lblName).Top = t - (0.0208 * 1440)' 0.0208 inches
    FRM.Controls(lblName).FontWeight = 700
ElseIf (mode = 0) And (FRM.Controls(lblName & "Box").Visible = True) Then
    FRM.Controls(lblName & "Box").Visible = False
    FRM.Controls(lblName).Left = l
    FRM.Controls(lblName).Top = t
    FRM.Controls(lblName).FontWeight = 400
End If
Exit Function

Resume ButtonAnimate_Exit
End Function

@@@Note: All object specifications on this site are in U.S. Measurements.

Those who follow the Metric System, please convert the values or select the US in the Regional Settings of the Control Panel or convert the given values into your Regional values.

Command Button Animation Design:

  1. Open one of your Access Forms in Design View.
  2. Create a Command Button control on the Footer Section of the form.
  3. Display the Property Sheet (F4) of the command button and change the following Property values as given below:
        Name = cmdClose
        Caption = Close
        ControlTipText = Click
  4. Create a Rectangle Control, on the Footer of the Form, slightly smaller by height and width of the command button, so that when the command button is placed over the rectangle control it stays hidden.
  5. Change the following property values of the rectangle control:
        Name = cmdCloseBox

    Note: The name of the rectangle-control must be the same as the command button suffixed with the word ‘Box’.

    Visible = False
    SpecialEffect = Shadowed
    BorderColor = 0
    BorderStyle = Solid
    BackStyle = Transparent
  • Drag and place the rectangle object correctly underneath the command button control completely hidden. You can use Ctrl-Key with Arrow Keys in MS-Access 2000 or Arrow Key alone in later Versions to move the rectangle control precisely behind the Command Button.

    The Rectangle control will not be visible when correctly placed underneath the Command Button. If necessary, click the Send-to-Back Toolbar Button (or Select Send-to-Back from the Format Menu), if the Rectangle Control is overlapping the Command Button.

  • Copy and paste the following Code into the Form’s Visual Basic Module and save the Form:
    Private Sub cmdClose_MouseMove(Button as Integer, Shift as Integer, X as Single, Y as single)
    ButtonAnimate Me.Name, 1, "cmdClose"
    End Sub
    Private Sub FormFooter_MouseMove(Button as Integer, Shift As Integer, X As Single, Y As Single)
    ButtonAnimate Me.Name, 0, "cmdClose"
    End Sub
  • Open the Form in normal view and try moving the Mouse over the Command Button and over the blank area in the Form Footer in a continuous stroke.
  • When the Mouse moves over the command button the button moves slightly up and to the left, exposing part of the rectangle frame, which looks like the shadow of the Command Button. When the Mouse is moved out to the blank area in the Form Footer Section the command button goes back into its original position hiding the shadow. When this action is repeated the Command Button becomes a lively control among other static controls on the form.

    Add More Animated Command Buttons

    Any number of command buttons can be added this way anywhere on the form by placing the code for the MouseMove Event Procedure. When the ButtonAnimate Function is called the function Parameter value 1 moves the button up and 0 brings it back to its original position. If more buttons are added to the form footer, each button should have its own Call the ButtonAnimate Function with 1 and 0 values as a parameter at the Command Button & Form Footer MouseMove Event Procedures respectively.

    Download Demo CommandButtonAnimation.zip

    1. Command Button Animation
    2. Double Action Command Button
    3. Colorful Command Buttons
    4. Transparent Command Button
    5. Command Button Animation-2
    6. Creating Animated Command Button with VBA
    7. Command Button Color Change on Mouse Move



    1. Advantageously, the post is in reality the freshest topic on curing acne naturally. I concur with your conclusions and will thirstily look forward to your upcoming updates. Just saying thanks will not just be enough, for the phenomenal clarity in your writing. I will right away grab your rss feed to stay abreast of any updates.

    2. You truly a extremely smart individual! Nice post! GA continually also my biggest earning. Nevertheless, it’s not a much.

    3. Good blog, lots of useful information. happens all the time

    4. This is a great site, I love the theme you are using. I Stumbled it for you and bookmarked it on Digg. By the way, if you get a moment, check out my Warcraft Gold Guide

    5. I am tired of these money making schemes. All I do is publish my content to article sites to get web traffic.


    Comments subject to moderation before publishing.




    Post Feed


    Popular Posts

    Blog Archive

    Powered by Blogger.


    Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code