Planning for Errors | Beginning Access 2002 VBA (Programmer to Programmer)

Despite the fact that we've said that errors will occur, it is possible to plan for errors. Someone once said that it's impossible to make anything foolproof because fools are so ingenious. We all make mistakes. We all do things that were unplanned . How many times have programmers thought "I don't need to add error code for this - the user will never do it"; or perhaps you think that nothing could go wrong with your code, only to be proved wrong at a later date.

So, errors don't just mean mistakes, or things you didn't think of, but also things the user does that you as a developer haven't accounted for. Perhaps they are using the application in a way that you just didn't think they would. Users don't really care about your code - they only see the visible portion of the application. So part of planning for errors is also planning for the way the application will be used, and planning for change, because applications do change over time.

The Design

If you've just said 'Design, what design?' then you're starting out on the wrong foot , and you're probably not alone. Remember back in Chapter 2 how much we talked about design. The design is the most fundamental part of your application, and in some cases, can actually take longer to produce than the application itself. The importance of requirements gathering and design cannot be overstated, because it's during this that you should work out what the application is supposed to do, and how it can do it. That might seem an obvious point, but how many times have you created a small application only to realize once it was finished that you'd missed something out. Or you suddenly think of something extra to add that would really help.

One of the most common causes of errors is change. Every change you make has the potential for adding errors, not only in the new code, but also in the existing code. Therefore, if you can avoid change, you reduce the possibility of errors. One of the ways to reduce change is to plan ahead.

User Analysis

Another obvious point, but working out what the users want is pretty important. One simple way to do this is to just sit and watch them work; see what they do and how they do it. There's no point creating a wonderful application if they have to change completely the way they work. This isn't the most time effective approach, but it can be instructive. Surveys and questionnaires also work well as user requirements analysis tools.

Table Design

We've said this before, but plan ahead. Try to think of the fields that might be required, rather than the fields that are required. If you collect sales figures, for example, do you just collect the address of the purchaser? What if you want to analyze by region (East, Central, West, and so on) as well as by state? Make sure you add these future fields, even if you don't use them at the moment. It's a lot easier to combine fields together than it is to split them apart.

Queries

In previous versions of Access it was a good idea to create a query for every table, and then base your forms upon the query. This allowed you to change the table without having to change the forms, because you could just change the query. Any field changes could easily be coped with by aliasing the fields. Access 2002 has a really cool feature that can automatically rename all occurrences of an object. You can turn this on from the General tab on the Options dialog, accessible from the Tools menu:

Selecting the first option allows Access to keep track of all of the objects and where they are used (except in code modules). The second option, if ticked , will automatically change all uses of an object name . If you don't want these changes automatically done, then leave this blank. If tracking is on, Access will track the changes to objects, and if AutoCorrect is on, then Access will be able to find all occurrences of the object and change them accordingly . The third option allows a table called Name AutoCorrect Log to be created with details of all changes that Access makes.

Important

One thing to be aware of is that name tracking doesn't extend to modules. So any object names used within modules are not affected by the AutoCorrect tool.

Forms and Reports

The key to creating maintainable reports is to keep it simple. We strongly believe in the 80/20 rule, which states that 80% of the people use 20% of the functionality. Your aim is to deliver an application that gives 100/100. You might find that the whole application doesn't achieve this, but that's normal. Not everyone uses all of the application, so try to target your forms accordingly.

With forms it's easy to try to fit everything onto one screen, so that it's there for the user, but do they really need it? If they do, does the cluttered screen make it harder to navigate? You may also find that a complicated form can make it harder to understand how events are fired and in what order, and thus complicate your programming. Would a second screen, or a Tab control, make things easier?

Modules

Using modules sensibly can ease maintenance. The idea is to put all of your functions that are related into a single module. So put all of your string handling functions into one place. This makes them easier to find later, and naturally keeps related items together.

If you have global variables , then consider a separate module just for their declaration. After all, if they are used everywhere it doesn't matter which module they are declared in, but it means that you can always go directly to the declaration if you need to. This works well for public constants and enumerated types too.

It's a fairly obvious solution, but one that works well.

Data-Driven Design

This is another of those 'plan ahead' ideas. Think about information that is fairly static, but may one day change: sales tax, for example. This may be fixed at the moment, but it could change. One way to solve this is to use a constant, so that you only have to change it in one place. An even better way is to store information like this in a table. When the application starts you can read your configuration information and it's then available to the code. If any of the values do change, you've only got to edit a field. No messing about with code and recompiling, and so on, and you could even design a form to allow users to make changes to these settings, perhaps in an 'Admin' mode. One small change and the next time the users log into the application it's done.

Another great advantage of using data-driven design is that it's easier to accommodate historical data. Consider a sales system and the use of sales tax. If you have a table storing your sales, you might need to work out the total cost of an order, including the sales tax. If last year sales tax was 8% but it was changed to 9% this year, you will have to store the date that it changed, otherwise when you look at past orders you might use the wrong sales tax.

Object-Oriented Techniques

We're not really going to talk much about these techniques since they are covered in the next chapter, but it's worth pointing out that object orientation isn't something to be scared of. You've already done some object oriented programming in this book, even though you might not have been aware of it.

All that needs to be said here is that object-oriented techniques can pay dividends to you as a developer. You will find that you can reuse your code more easily, and you'll find it easier to work with, thus reducing maintenance. The idea of an object that is completely self-contained means that all other programs see of the object is what you want them to see. You are free to change the inside in any way you feel fit, as long as the outside view stays the same.

If you think forward you might be able to create objects that will be usable in future projects. This not only saves the time taken to create future applications, but also reduces the maintenance because pre-built objects should be error free.

Option Explicit

This has already been mentioned a couple of times so far, but it's worth mentioning again. You should ensure that this is the default for all modules in the VBE. It's available on the Options dialog from the Tools menu, and is shown as the second option below, Require Variable Declaration .

This option means that the OPTION EXPLICIT statement is placed in any new modules you create. With that statement safely installed, you cannot use a variable unless it has previously been declared, and thus allows VBA to pick up things such as typing mistakes, and incorrectly used variables. For example, consider the following:

 Public Function Circumference(dblRadius As Double) As Double   Circumfrence = 2 * 3.1414926 * dblRadius End Function

I'm sure you've all made similar mistakes - did you spot this one? The function name has been typed incorrectly when returning the function value. Running this without OPTION EXPLICIT would not cause an error, but the function would always return as the answer; however, with OPTION EXPLICIT at the top of our module, we would get a Variable Not Defined compile error, which would highlight the problem straight away. A simple example, but imagine a longer function, with many variables. You can see how it would be harder to track down. With Option Explicit set at the top of the module, VBA would warn you about undeclared variables.

Note that selecting the Require Variable Declaration option in the above dialog will not affect existing modules, only new ones, so if you have already created some modules, you'll need to go back and add the OPTION EXPLICIT statement. In general it is the first executable statement in a module, and resides in the General Declaration section of the module.

Syntax Checking, Statement Completion, and Code Tips

VBA also supplies a handy set of options for syntax checking, statement completion, and tips. These are the ones shown in the code settings dialog above.

Auto Syntax Check - turning this on (which is the default) causes VB to automatically verify correct syntax after you enter a line of code. This can be mighty frustrating sometimes, when you know that you haven't completed the line yet, but wanted to select text elsewhere, perhaps to copy and paste something. However, I generally keep it on, because in the main it's useful to know about a syntax error as soon as you have it.
Auto List Member - this is also sometimes referred to as "IntelliSense autocompletion", which just rolls off the tongue! But it certainly is a useful feature. Turning it on causes VB to displays a list that contains information that would logically complete the statement you are writing. You can then use the arrow keys to select the one you want, and press Tab to complete the statement. This not only makes you a faster typist, but it means you avoid silly typos. You can also force this to happen by pressing Ctrl + Spacebar on the keyboard.
Auto Quick Info - when enabled this displays information about functions and their parameters as you type them - it comes in quite handy as a quick reference.
Auto Data Tips - this displays the value of the variable over which your cursor is placed, and is used in Break mode (more on this later).

Comments

This always causes contention among programmers. We tend to put comments in as we go along, partly because we have bad memories. There are some people who put in no comments whatsoever ("If it was hard to write, it should be hard to read"), and there are others who go overboard, commenting every line.

Effective comments are those that are used judiciously. You have to find your own happy medium, but here are some tips:

You could put a comment at the top of each procedure describing what it does, what arguments it takes, and so on. For example:

 ' ' Purpose:     To calculate the circumference of a circle ' Arguments:   dblRadius   The circle radius ' Returns:     The circumference ' Author:      David Sussman ' Date:        15 December 1998 ' Modification History: ' Who      When        Why ' DMS      15 Dec 98   Corrected spelling of return value Public Function Circumference(dblRadius As Double)   Circumference = 2 * 3.1414926 * dblRadius End Function

This gives a very clear picture of what the procedure is supposed to do, and what changes have been made to it. The procedure shown above only has one line of code, but for larger procedures you can see this would be useful.

Comment variables, preferably on the same line, describing what they will be used for. For example:
```
 Dim objRecSales As Recordset       ' recordset to hold sales figures 
```
Place comments on their own line, above the block of code to which they refer. For example: use comments for blocks of code, and describe functionality, rather than line by line.
Do not comment the obvious. If the statement is self-describing and then you comment it why you are doing it? For example, the OpenRecordset command is obvious - it opens a recordset, but you may want to comment why you need the recordset in the first place.
Keep your comments up to date. If you change the code, comment it. This is especially true in projects with multiple authors.
Debug code, not the comments. Don't always rely on the comments, as they may not be accurate.

The most important thing is to be consistent. You may feel the code header shown earlier is a bit too unwieldy, but that's fine. Pick something that you feel happy with, and use it - everywhere.