Consultancy - I can help your
business Bookshelf - You need these
books! Mailbag - Readers' problems
solved Articles - Bits and pieces Links - Check out these sites About - Who is Martin Green anyway?
Are you looking for Microsoft Office
training for yourself, your colleagues or your staff? If you
like what you see here you can get personal training from me.
Find out more on my
Training Page.
Development
& Consultancy
Do you need someone to build you an
Access database or an application in Excel? Or perhaps you are
just looking for help and advice. I can help. Find out about my
development and consultancy services on my Consultancy Page.
Contact Me
I work from an office in my home so, for
reasons of personal privacy, you won't find a contact address or
telephone number here.
For business related enquiries
(training, development and consultancy) send me an email at
training@fontstuff.com
All messages concerning business
or training will receive a prompt reply with full contact
details.
Fixing the Access Date Function
[05/12/2018]
"Why Doesn't the Date Function Work in My Access Database?"
When I first came across this problem, many years ago as a novice Access
programmer, I was really stumped. In those days the Internet was new
with little help available so if you had a problem you pretty much had
to fix it yourself, not always an easy task especially when Access
presents you with some very misleading clues. You have built your
database with liberal use of the Date function in calculated fields on
forms, in SQL in queries and in VBA code. Everything works fine so you
roll it out with your client or colleagues and suddenly find that the
Date function throws up an error. Even more confusing is that it doesn't
necessarily happen on every machine. What's going on?
It turns out that the problem isn't the Date function
after all. That's just Access teasing you and trying to throw you off
the scent. The problem is a broken reference. You developed your
database in a particular version of Access and cleverly had it interact
with another application such as Excel or Outlook. You forgot to check
which version of Access the client was using or, worse, you checked but
they neglected to tell you that some users had different versions. The
references you set aren't recognized by the older versions and your code
fails. The first thing you notice is the fact that Access pretends it
doesn't know the Date function.
In my latest Mailbag item
Fixing the Access Date Function I
explain what has gone wrong and how to fix it. If you want to know more
about writing VBA code that interacts with other programs, I have
written a fact sheet that gives a comprehensive introduction to the
topic:
Microsoft Office VBA - Automation which you can download in
printable PDF format completely free of charge.
A Progressive Search Tool for Access
[28/11/2018]
Recently a question arrived in my mailbox that
interested me because, although it seemed quite a complicated request,
the solution was relatively simple. I have published various tutorials
showing how to build different kinds of search tools, but this request
was different. Usually you enter the text that you are looking for, then
maybe click a button, and Access presents a list of items for you to
peruse. In this case my writer wanted a list that they could gradually
refine. First they type a letter and they get a list of items starting
with that letter. Then they add another letter and the list shrinks to
display only those items starting with that pair of letters, and so
on...
The solution is to make use of the Change event
of the text box into which the user types. Instead of waiting for the
user to finish typing then maybe using the AfterUpdate event of
the text box as they leave it, or the Click event of a command
button, my solution makes use of the Change event to run a
procedure each time the content of the text box changes.
Training and Consultancy Services for Business
with Naturally Excel
[15/11/2018]
I'm delighted to announce that I have joined the team at
Naturally Excel
to add Training to their existing range of services.
Naturally Excel
is a small team of highly experienced and dedicated Excel professionals
offering a wide range of consultancy services to business clients. I
have been training Excel and Excel VBA since 1995, helping people get
the best out of this excellent program. Working with Naturally Excel we
are offering half-day training courses and workshops for Excel users to
learn about or hone their skills on a wide range of topics.
I introduced my popular half-day training courses in
Excel, Access and VBA in response to an increasing demand from
businesses who were dissatisfied with off-the-shelf courses offered by
training companies. The courses come at a fixed price regardless of the
number of participants and take place on the customers' own premises.
The convenient half-day timetable means colleagues don't have to be away
from their desks for a whole day, making it easier for many more people
to attend. The wide range of topics means you can spend a morning or
afternoon learning about exactly what you need in far more detail than
an out-of-the-box course can provide.
Modifying Recorded Macros: A Universal Sort Macro for
Excel
[2/11/2018]
This question turned up in my Inbox a few days ago. The
writer had recorded a macro that included sorting a range of data. When
run on the original data the macro worked fine but when run on different
data (in this case the number of rows varied) the macro didn't work
correctly. How could it be fixed?
Excel's Macro Recorder is a great tool for creating
simple macros and for helping you learn VBA programming. After more than
20 years as a VBA developer I still use it occasionally to remind me how
to perform a particular action, to show me how Excel performs a task
I've never had to automate before, or simply to save time writing the
code myself. But the real skill comes with modifying the recorded code
to work in different, often unpredictable circumstances.
At this point you might be wondering what is the point
of having a macro to sort data. After all, it only takes a couple of
clicks for the user to do it manually. But you might want to include a
sort into a larger, more complex macro, in which case it has to be able
to work with whatever circumstances prevail at the time.
New Access Forms Masterclass
#6 A Push-Button Filter for Your Access Forms
[5 October 2018]
When building a database the task I enjoy most is
creating the user interface, what used to be called the UI until
Microsoft coined the term UX or User Experience. Good and thoughtful
work here can not only help maintain the integrity of your data,
avoiding user errors and preventing junk from getting in, but it can
also go a long way to create user satisfaction. My aim is always to
ensure my users have a good day and enjoy working on my databases. Tools
that are awkward and frustrating to use just end up annoying and
frustrating their users. I want the tools I create to be intuitive and,
above all, useful. You can add as many bells-and-whistles as you want
but unless they make sense to the user they might as well not be there.
The topics I have included in my series of
Access Forms Masterclasses
are all drawn from my personal experience of over 20 years of building
Access databases for my business clients. Many of them are included in
all the databases I create. In my latest Masterclass I show you how to
build a simple Push-Button Filter. The
idea isn't my own, in fact it was one of the things that inspired me to
get "under the hood" and start being creative with Access 2 way back in
the mists of time. I wish I could remember where I read it or the
author's name but much thanks to them anyway. The original tutorial used
Access Macros but I have made use of VBA, taking advantage of the added
flexibility and creativity that this offers.
As usual, the Masterclass includes step-by-step
instructions, plenty of screenshots and code that you can copy and paste
into your own project and, most importantly, explanations of how
everything works.
Build a Back-End Link Checker for Your Access Database
[30 September 2018]
It wasn't until I started building databases for other
people (way back in the mists of time!) that I began to realize the
benefits of splitting a database. With a split database you have two
database files: the back-end file containing the tables, and the
front-end file containing everything else. A single copy of the back-end
file is located in a shared location and each user can have their own
(tailored if appropriate) copy of the front-end file. The benefits of
splitting a database are many but for the developer perhaps the most
useful is the ability to implement updates, improvements and bug-fixes
simply by supplying the client with a new copy of the front-end.
All the users have to do is reconnect the new front-end
with their existing back-end file... and that's where the problems
arise. Do the users know how to link the files? Can they be trusted to
do it correctly and, anyway, it it fair to ask them to perform this
task? Is is going to require a site visit from the developer to install
the new front-ends and link them? To solve this I built a tool that
seamlessly checks the links to the back-end file each time the front-end
is opened. If the link to the back end is broken all the user has to do
is specify where the back-end file is located. Even if it does require a
site visit from you, of if the client's IT guy has to do it, the process
only takes a moment. Once linked the job doesn't have to be repeated
unless a new front-end file is required or if the path from front-end to
back-end is changed. If the back-end can't be located the tool will let
the user know and offer to fix it.
I incorporate this tool in all my split databases and
now you can do the same thanks to my latest tutorial
Build a Back-End Link Checker for Your
Access Database. If you aren't familiar with splitting and
re-connecting databases I have included a section describing how to
split a database and how manual re-linking is performed. There is a
fully working sample database to download plus a print-friendly copy of
the tutorial in PDF format.
Handling Errors in Your Access Database
#2 Add an Error Log to Your Database
[25 September 2018]
Hopefully, should an error occur in your database, whether
anticipated or not, the user will note the details and report it so that
action can be taken to rectify the problem. Unfortunately, this seldom
happens. Usually there is a message like “the database keeps crashing”
or “I keep getting an error message”. More savvy users will note the
details or even take a screenshot of the error message, which is very
helpful, but diagnosing the problem will often require more information.
Additional information would be helpful, such as: Does this only
happen to a particular user? Has it happened before and if so how often?
Does it always happen on the same computer? What was the user doing at
the time? To answer some of these questions and to make the reporting of
errors easier, I always include an Error Log in my databases.
I have even discovered that users have failed to report an error,
later saying something like “Oh yes, it does that…” as if they expected
things to go wrong occasionally when, had I known about the problem, I
could have fixed it.
The solution is to create a system to record as much
information as possible when an error occurs. My Error Log does exactly
that, and includes additional reporting features such as the facility to
email you a copy of the error log. My latest tutorial
Add an Error Log to Your Database
tells you everything you need to know and shows you how to do it.
Handling Errors in Your Access Database
#1 Errors 101
[20 September 2018]
Nobody writes perfect code. The aim of every developer
is to write code that is "bulletproof" but it's so easy to leave
something out, make a mistake, or simply get it wrong. We try to
anticipate every unexpected, irrational or simply crazy thing the user
might do and we test our code rigorously before releasing it to the
world. But unfortunately, despite our best efforts, errors happen.
That's when Error Handling comes to the rescue. Good error handling can
stop an error from becoming a crisis or worse, a disaster! When your
code hits a bump in the road a well-written error handler can help it
safely come to a halt. I am frequently surprised, even horrified, to see
code written by professional developers (well, folks who take money for
it) that either lacks any sort of error handler or simply relies on a
cursory On Error Resume Next to deal with whatever might
happen. My latest tutorial Handling Errors
in Your Access Database #1 Errors 101 tells you what you need to
know about adding safe, secure error handling to your Access databases.
If you work with macros in Excel, Word or any of the multitude of
Microsoft applications that can be programmed with VBA then much of what
you will see in this tutorial will be relevant to these programs too.
Coming Soon: Handling Errors in Your
Access Database #2 Build an Error Log for Your Access Database.
New Resources Page
[16 April 2018]
Those of you who have been fortunate enough to attend
one of my training courses will know that I have scant regard for many
of the handbooks supplied by training companies. These weighty tomes
usually end up on the shelf and gather dust until they are eventually
thrown away unread. Instead I prefer to write my own documantation which
is well illustrated and easy to read. The examples in my handouts will
often be the same as you will have done in class but if you haven't been
to one of my classes, or would just like to run through some of the
exercises again, many are accompanied by sample files.
All my handouts are free for anyone to download. They
are all in PDF format and are fully printable with no restrictions. You
can find them on my new Training
Resources Page. As I write this the content is mostly Excel and VBA
but I will be adding more as they become available so stop by regularly
for the latest content.
FREE eBOOKS
[25 July 2017]
Until now my existing eBooks on
The Visual Basic Editor,
VBA Message and Input Boxes, and
Recording Excel Macros have been
available for purchase. From today all of these ebooks are totally FREE
for you to download. Each PDF is unrestricted so you can copy and paste
content and print out as many copies as you like. No strings! Just
follow the links above or go to my eBooks
page and follow the download links.
Why free? I belong to a generation that remembers life
before the Internet. So many people enjoy its benefits but never make
any contribution themselves. I decided it was time for me to give
something back.
If you would like to say thanks and make a small
donation to help support this site please click the
PayPal Donate button
below. You can donate any amount in any currency you choose. It's easy
and only takes a moment. Thank you.
Pip pip!
What's New?
New Mailbag Item Fixing the Access Date Function >>GO>>
New Mailbag Item A Progressive Search Tool for Access >>GO>>
New Mailbag Item Modifying Recorded Macros: A Universal Sort Macro for
Excel
>>GO>>
New Access Forms Masterclass A Push-Button Filter for Your Access Forms
>>GO>>
New Access Tutorial Build a Back-End Link Checker for Your Access Database >>GO>>
5 December
2018
Free eBooks
All my eBooks are now FREE!
Do you want to learn more about Access,
Excel and VBA? Are you a teacher looking for top quality
courseware for your students? My eBooks are the ideal solution
to your needs. They are packed with code snippets, illustrations
and step-by-step exercises. Written in the same style as my
popular on-line tutorials, my eBooks will help you develop your
skills and build useful, professional looking applications. Find
out more at my eBooks page.
Free
Courseware
Download FREE courseware handouts. These
documents are ideal for teachers, students and anyone wanting to
learn more about their Microsoft Office programs. Each handout
covers a specific topic and is illustrated with full-colour
screenshots. Many have accompanying sample files. The files are
not restricted in any way so you can print copies or read them
on-screen. Get my Free Courseware
here.
Support
this site!
Please make a small donation to help
support this site.