Martin Green Martin Green's Access Tips
|   Home   |   Excel   |   Access   |   Word   |   Outlook   |   VBA   |   eBooks   |   Handouts   |   Site Index   |   RSS   |
Access Tips

Download FREE Access Courseware

Access Video Tips Home

Visit My YouTube Channel


Martin Green's Access Tips

I've been teaching Microsoft Access since 1995. Even then Access 2, Microsoft's first commercially available version, was a great database program and it has got better with every release. Working as a database developer I have built many Access databases for my clients. I want to share my knowledge and experience with you so that you can create powerful, professional databases for yourself, your workgroup or your company. There are regular updates and new tips and tutorials so stop by occasionally or subscribe to my RSS Feed and stay up-to-date.

I also offer personal Access Training for individuals or groups, and if you haven't got the time to do it yourself I can do it all for you. To enquire about my Training or Consultancy services email me at

Access Forms Masterclasses

  • Access Forms Masterclass #1: Custom Navigation Buttons
    This tutorial shows you how to build a set of record navigation buttons, a simple but effective and useful addition to any Access form. It guides you step-by-step through the process of drawing command buttons on a form then writing the VBA code that powers them. See the Access Video Tips section below for a video version of this Masterclass. [1 August 2013]
  • Access Forms Masterclass #2: Custom Record Counter
    Following on from Masterclass #1 this tutorial shows you how to build a record Counter to replace the the built-in one on a form's navigation bar. In addition to fully illustrated step-by-step instructions there is a completed sample database and a printable PDF handout to download. See the Access Video Tips section below for a video version of this Masterclass. [5 August 2013]
  • Access Forms Masterclass #3: Go To Record - A Custom Record Locator
    A common requirement for database users is to quickly locate a specific record. Whilst Access provides a number of tools for sorting and filtering a form's recordset there isn't a built-in tool to help the user quickly find and display a specific record. If you want one, and you almost certainly will, you will have to build it yourself. I have built several different kinds of Go to Record tools to suit my users' requirements and the complexity of the data. This is one of my favourites and is really easy to build. See the Access Video Tips section below for a video version of this Masterclass. [16 August 2013]
  • Access Forms Masterclass #4 Pop-Up Search Tool
    I have added this custom search tool to many of the databases I have built. Access forms include simple built-in search tools but, whilst useful, they are limited to simple text searches on individual fields, but how many of your users know of the existence of the options on the right-click context menu? My search tool consists of a pop-up dialog box that offers the user the ability to search for a specific record, through a choice of fields and in several different ways. [10 September 2018]
  • Access Forms Masterclass #5 Create Dynamic Titles for Your Forms
    Most Access forms need some sort of title. The title can simply tell the user what the form is for but it can also provide a quick and easily visible reminder of which record is currently being displayed. In this Masterclass I lead you through the steps of creating a Dynamic Title for your form, one that changes automatically as you move from record to record, and automatically updates when certain data on the form is changed. A title should be both visually appealing and useful. My Masterclass chows you how to add a Dynamic Form Title that fulfills both those aims. It is very easy to create and in the process of building it you can learn a little VBA code. [13 September 2018]
  • Access Forms Masterclass #6 A Push-Button Filter for Your Access Forms
    Few things frustrate the database user more than being unable to find a specific record. This Masterclass shows you how to build a push-button console equipped with a set of buttons, each representing a letter of the alphabet. When the user clicks a button something happens, exactly what is up to you but I offer a couple of examples: a Filter tool, displaying only those records in which a chosen field starts with that letter, and a GoTo tool that takes the user to the first record that starts with that letter in a sorted recordset. It's a versatile tool with lots of potential applications. [5 October 2018]

Access Database Management

  • Add an Audit Trail to your Access Database
    It's important to keep track of your valuable business data and especially in a multi-user environment it can be important to know who did what and when. The task might seem daunting but when I set out to build an Audit Trail tool it turned out to be much easier than I expected. This simple tool works from any data form so you only need to build it once in your database. [23 April 2013]
  • Handling Errors in Your Access Database #1 Errors 101
    Nobody writes perfect code. We do our best, but we can never anticipate every circumstance or prepare for every crazy thing a user might do. Sometimes we just make a mistake or leave something out. Thatís where Error Handling comes to the rescue. This tutorial explains how to safeguard your VBA code so that an error doesn't become a crisis. [20 September 2018]
  • Handling Errors in Your Access Database #2 Add an Error Log to Your Database
    When an error occurs you can't always rely on the user to report the necessary information to help you diagnose the problem. A savvy user might send you a screenshot of your error message which will give you a clue to what has happened but for accurate diagnosis of the error you often need more information. My Error Log seamlessly records errors gathering and storing all the available information and includes useful features such as the facility to email a copy of the Error Log to a nominated person. [25 September 2018]
  • Build a Back-End Link Checker for Your Access Database
    There are many good reasons to split a database. For the developer one of the most significant is the ease of supplying updates. You can give the user a new copy of the front-end file, complete with its additions, improvements and any bug-fixes, whilst the back-end with its data remains unchanged. All the user has to do is reconnect the links from the front end to the back-end, and that's where the problem arises. Do the users know how to do this? Can they be trusted to do it properly and is it fair to ask them to do it anyway? Is is going to mean a site visit from you? To address this problem I have devised a tool that seamlessly checks the back-end links each time the front-end is opened. If the links are broken all that is required from the user it to specify the location of the back-end file. [30 September 2018]

Access Form Design

  • Build Better Access Forms: Mastering the Combo Box NotInList Event
    When a field has a limited list of possible values a combo box is usually the best way to help the user make their choice. This tutorial shows you several ways to enhance your Access forms by harnessing the power of the combo box NotInList event.
  • A Combo Box Date Chooser
    People need help entering dates, and I often add a calendar to my Access forms to make things easy for the user. But sometimes the ActiveX calendar isn't appropriate so I designed an "intelligent" date chooser using combo boxes. The VBA code that powers the form verifies the date and won't let you enter a bad one (like September 31). This tutorial is in two parts. The first part shows you how to build the form. The second part adds the VBA code and explains how everything works.
  • Coloured tabs for Your Access Forms
    Access forms are where the database builder can show off their artistic talent, but some components can't be customised to match your colour scheme. An obvious example is the command button. Another is the tab control, so when I was asked if the tabs could be given custom colours my first answer was "No". But as usual, some ingenuity and a little VBA can solve the problem.
  • Making Sense of List Boxes
    Unlike the Combo Box the List Box can allow the user to make multiple selections from the list, but to make use of this feature you need to use VBA code. Fortunately it's quite simple and this tutorial shows you various ways to do it.
  • Cascading Lists
    Form design is all about making life easier for the user. One way to achieve this is to help them make decisions. This tutorial shows you how to make the contents of a drop-down list dependent upon the choice the user makes in a different one.
  • A Pop-up Calendar for your Access Forms
    Solve your date input headaches by providing the user with a pop-up calendar. This tutorial shows how to use the Microsoft Calendar Control on your Access forms and power it with some simple VBA code.

Access Queries

  • Customizing Parameter Queries
    The question I get asked most of all in Access is "Can I have a drop-down list in my parameter query?" With a little form design and a touch of VBA code you can have just exactly what you need. [Updated 16 August 2018]
  • Access Query and Filter Criteria
    New users are often baffled by the wealth of different ways you can ask a query to look for data. This tutorial illustrates many useful criteria expressions to get you started.
  • Using Parameter Queries
    You don't have to create a whole set of queries for each eventuality. Instead you can use parameters in your query. These make the query ask the user for some input, such as... "Which invoices do you want to see?" [Updated 21 August 2018]
  • Parameter Queries: Handling Null Responses
    Control what happens if the user leaves the parameter dialog blank.
  • Calculating in Access Queries
    One of the principal rules of databases is that you don't need to store calculated data. Instead, you should get Access to do the calculations when you need to see them. This tutorial explains the basics.
  • Working with Dates in Access Queries
    Sooner or later everyone needs to do some date calculations and, like Excel, Access has a number of useful date-related functions. Using these tools can greatly increase the scope of your date queries.
  • Calculating Totals in Access Queries
    The Totals tool is a powerful but often-overlooked feature of the ordinary select query is its ability to calculate totals.
  • Working Out a Person's Age
    When storing personal data, it is normal practice to record a person's date of birth rather than their age. This piece of data is constant, whilst their age changes every year. Here's an easy way to get Access to calculate it for you.

Access and SQL

  • Access and SQL #1: Setting the SQL Scene
    The first in a series of tutorials about using SQL to help you build a better database. This first tutorial takes an overview of where and how SQL fits in to database management and offers a taste of what is to come in later tutorials.
  • Access and SQL #2: Putting VBA and SQL Together
    VBA and SQL are different languages. This tutorial explains how to get your VBA code to "speak" SQL. It illustrates good coding techniques and shows you how to avoid errors and debug your code when things don't work.
  • Access and SQL #3: Some Practical Examples
    A hands-on session where you can test the power of SQL by building and modifying tables, and adding and editing records, all with a line of VBA/SQL code.
  • Access and SQL #4: Building Queries "On the Fly"
    When you build a database you can't anticipate your users' every need, especially when it comes to queries. Even if you have the skill to create your own queries it can be tedious to build a new query every time you want to ask your database a different question. This tutorial shows you how to build "all purpose" queries that write their own SQL.
  • Access and SQL #5: More Instant Queries
    This tutorial takes expands on the theme of "all purpose" queries and shows you how to build SQL statements from multi-select list boxes, how to offer And/Or criteria options, and how to let the user choose how the data is sorted. There are also tips on filling lists programmatically and clearing list box selections.
  • Access and SQL #6: Dynamic Reports
    Reports are often seen as inflexible, with the decisions about which data is to be displayed being made at design time. Access users often think that if they want to see a different set of data they need to build a new report. Not true! Add some VBA and a sprinkling of SQL and you can have truly dynamic reports which change their content at the click of a button.

Access Video Tutorials from my Access Video Tips Section...

  • Access Forms Masterclass #1: Custom Navigation Buttons
    In this video I show you how to add a set of custom navigation buttons to an Access form. The video is recorded in real time from start to finish showing you just how easy it is to complete this task including writing the VBA code necessary to power the buttons. [7 August 2013]
  • Access Forms Masterclass #2: Custom Record Counter
    If you built the Custom Navigation Buttons in Masterclass #1 you might have also chosen to remove the built-in navigation bar. In this video I show you how to add a label to an access form, format it to your liking, and write the VBA code necessary to turn it into a fully functioned record counter. [9 August 2013]
  • Access Forms Masterclass #3: Go To Record - A Custom record Locator
    A common requirement for database users is to quickly locate a specific record. Whilst Access provides a number of tools for sorting and filtering a form's recordset there isn't a built-in tool to help the user quickly find and display a specific record. If you want one, and you almost certainly will, you will have to build it yourself. This is one of my favourites and is really easy to build. [16 August 2013]

More Access tutorials from my VBA section...

  • Selecting Random Records from an Access Table
    This tutorial uses both VBA and SQL to select records at random from a table and place them into a new table with the aid of VBA's Rnd() function.
  • Build Custom Functions for Your Access Applications
    If you ever thought that Access was a little short on calculating power, this tutorial is for you. You will learn how to write a Custom function in Access and find out how to make use of it in forms and queries.
  • More Custom Functions for Access and Excel
    If you're short of ideas for Custom Functions take a look at these. This tutorial shows you how to build and use RandomNumbers, FindSaturday, EOMonth and RemoveSpaces functions that can be used in both Excel and Access. You will also see examples of If Statements and Case Statements, and some built-in functions that you might not have discovered yet.

Access Questions from my Mailbag section...


  ©2000-2018 Martin Green All rights reserved