Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.



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

When developing an Application under any Database Management System it should be user-friendly and visually pleasing too. An un-attractive 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.

You can cook good food in the kitchen but if the final presentation on the table is not right then all the efforts put behind the scene doesn't get the appeal that it deserves. Like the saying goes, “face is the mirror of mind”, attractively designed Screens and Reports definitely dictates the personality of a User-friendly Application.

Here, I would like to present some of the simple Controls and Programs that I have developed for my own Projects and I am sure that you will find them interesting too.

Example Programs are written for MS-Access 2000 and will run under earlier versions too.

Attaching System Reference Library Files

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

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

On the Visual Basic Editor Window select References' from Tools menu. Put 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 Library Files may change for different version of Microsoft Office. The Library Files List is in alphabetical order. The selected items will appear on top of the list.

Design Modification

On MS-Access User Interface, Command Button Control is the most frequently used object (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 more interesting and attractive object 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 few lines of Visual Basic Code.

Animation Action

The Mouse movement over the Command Button will move the command button slightly up and to the left, showing part of the rectangle control, like a shadow of the command button. When the Mouse is moved out into the blank area of the Form the Command Button goes back into its original state, hiding the rectangle control again. When this action is repeated the Button gives a lively appearance by moving up and down, showing the shadow on and off. Check the images given above which shows both state 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 Metric System please convert the values or select U.S. in the Regional Settings on Control Panel or convert the given values into your Regional values.

Command Button Design:

  1. Open one of your 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 : Name of the rectangle control must be same as the command button suffixed with the word ‘Box’.

    Visible = False
        SpecialEffect = Shadowed
        BorderColor = 0
        BorderStyle = Solid
        BackStyle = Transparent
  6. Drag and place the rectangle control 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.

  7. 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
  8. 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 the rectangle frame, as if it is the shadow of the Command Button. When the Mouse is dragged over the blank area in the form footer the command button goes back to 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.

Adding More Command Buttons

Any number of command buttons can be added this way anywhere on the form by placing the code for 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 at the form footer, each button should have its own Call to the ButtonAnimate() Function with 1 and 0 values at the Command Button & Form Footer MouseMove Event Procedures respectively.

Download Demo Database

  1. Command Button Animation
  2. Double Action Command Button
  3. Colorfull 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 are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts