databaseLessons

I am sure that I have found them all now.

Seems that when I moved from AccessDatabaseTips.com over to DatabaseLessons.com I moved some files and never added them to the index pages. Most were discovered a month or so ago. Found 2 more today.

Need to use a query to change UPPER CASE values in a field to Mixed Case? Try this.

The documentation tool that comes with Access 2000 (under Tools, Analyze, Documentor) is VERY slow compared to the Access 97 version. I do not understand why. I use this feature frequently to print out the field names of a table. It is so slow in MS Access 2000 that I designed my own. Here is its story!

While I was at it, the info on “dao” was linked to from dozens of articles, but it was never added to an index page. It is now on the “Modules” page.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

I hope your databases are being good to you!

Just a friendly reminder. You need to ALWAYS use Option Explicit. Look at the top of any module of code, whether under forms or modules. The first two lines should be …

Option Compare Database
Option Explicit

By default, Access only puts the 1st one. You have two ways of getting the 2nd line in there.

1. type it in manually, or,
2. under Tools, Options in the VBA editor, turn on “Require Variable Declaration”

What does it do? Forces the “compiler” to make sure that EVERY variable has been declared. Basically, helps stop typos from causing “bugs”.

Just trust me on this one, and do it!

I may have given this one before, but it is SO useful. I personally had to use it last week.

The computer you are at does not have the Linked Tables utility working and you need to know where the user’s data is stored. Try this command in the VBA Editor’s Immediate Window (Ctrl-G)

? CurrentDb.TableDefs(”Holders”).Connect

Where Holders is an example table name (change that to one from your database).

(NOTE: you may have to have DAO references on. see this page if you do not know what that means)

Today I was working on some code that I have been asked to enhance. I came across the following bit of code.

if a = b then
   do something that does NOT affect a or b
   do something else that does NOT affect a or b
   if a = b then
      do a lot of stuff
   endif
endif

Why are there 2 identical if statements, with no code inbetween that affects the comparison test?

This reminds me of the article I wrote, called Working with Other Developers. Please spend at least a little bit of time on the “look-and-feel” of your VBA code. It makes it much easier for someone else (maybe even you) to read and understand your logic.

p.s. another problem with the system I am working on is that it has a lot of orphaned code. That is, code that is no longer attached to an object, and will never run. Some of this code causes the Access compiler to choke and sputter, and I depend on the compiler to help me find errors. Hence, I had to spend time getting rid of the orphaned stuff.

I have done a major rewrite of this article, first published many moons ago. The new version has 4 screen shots (about 60 K in total), to help you visualize what I am suggesting. I think I explain myself better as well.

If you have dozens of reports that need various criteria fields, then between this new version and the original Part 1 of this series, you should have a workable answer.

Read the article by clicking here.

As always, write me with further ideas or questions about this article.

I have chosen to start doing my news updates via a WordPress blog. I will also use this to post small tips that I feel are too small for a full article.

Richard “Manxman” Killey