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 later versions too.

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.

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

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 and over 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

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

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



Your email address:

Delivered by FeedBurner


Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


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

Featured Post

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...


Blog Archive

Recent Posts