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

 
Get Access and Excel Group Training for Just 425.
  
Get Access and Excel 1-to-1 Training for Just 275.

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 martin@fontstuff.com.

Access Forms Masterclasses

  • Access Forms Masterclass #1: Custom Navigation Buttons New!
    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 New!
    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 New!
    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 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.
  • 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 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-2013 Martin Green martin@fontstuff.com All rights reserved