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
Access Forms Masterclasses
Access Forms Masterclass #1: Custom Navigation
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
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 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]
Access Form Design
Build Better Access Forms: Mastering the Combo Box
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
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.
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
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.
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.
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?"
- 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
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
Access and SQL #2: Putting VBA and SQL
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
Access and SQL #4: Building Queries "On the
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 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
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
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()
- Build Custom Functions for Your Access
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...