Welcome to FontStuff – your source for Office tips and tricks. Here are some recent answers to a variety of questions:
Fixing the Access Date Function
“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.
If you are looking for more on using the date functions in Access, here is a good tutorial video from YouTube:
A Progressive Search Tool for Access
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.
Modifying Recorded Macros: A Universal Sort Macro for Excel
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.
Build a Back-End Link Checker for Your Access Database
It wasn’t until I started building databases for other people (way back in the days of my time working with mysql hosting and creating web apps like StampFetcher) 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.
Handling Errors in Your Access Database #2
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
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.