The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

How to Solve Tough Problems – Part 4 – Debugging to the Next Degree

Introduction



Take a look at this picture. You probably see an old couple. Take a look again. Do you now see a young couple?  This picture by Octavio Ocampo is called Forever Always. If you need help seeing the young couple, skip to the end of this Wizard for help.

When you look at something in a different way, you’ll sometimes see something that was there all the time, but because you were too narrowly focused you missed important information or details.

In this issue we’ll continue our problem-solving series with more debugging methods to help you see things that are happening, but not always obvious. These are some concrete tools and techniques to use when you need to solve a puzzle in your code that is not working as expected – Debugging to the Next Degree.

Add a Watch

This technique allows you to monitor a variable as your code runs. Click on the code where the variable exists, then click on the Debug Menu and choose Add Watch.



Choosing "Add Watch" will bring up this screen:



By default, the Procedure and Module will be where you were when you clicked the add watch button; however, you can expand the scope to include all procedures in the current module as well as procedures in all modules.

From here you typically will choose a variable to watch, but it could be any logical expression. Your last decision is to choose whether the code should Break (stop) when the value is true, or to break when the value changes. Note that you can change any of these decisions after the fact.

At this point, let your code fly and the watch expression will stop your code whenever the criteria you choose is true.

Debug Assert

Debug assert is a technique that allows you to stop the code if some condition is false. For instance, let say that you have a variable “blnFoo” that should be true when you reach a certain point in your code. Just before that line insert the following:

Debug.Assert blnFoo = True

Each time your code hits this line it will evaluate the blnFoo variable. If it’s true, it will continue and, if not, the code will stop and highlight the line for you. From there you break out your Sherlock Holmes hat and start looking for something that is causing the unexpected value.

Note that you can expand this functionality by calling function, either a built in function, or a function that you wrote yourself.‚Äč

Compile/Decompile

This is technique is both simple and should be among the early steps you take when things are just not making sense. You can find the step by step instructions in the February 2010 Wizard.
 

The Nuclear Option: Import Everything into a New Database

If none of the problem-solving tools work: analyzing the data to eliminate all the logic problems, running the compile/decompile routine, and have tried a compact and repair (see July 2018 Wizard and April 2010 Wizard), you may be in a spot where the problem can be neither seen nor directly solved.

Sometimes problems are caused, not by logic issues, data issues or bugs, but rather by corruption. The corruption can be in the tables, or sometimes buried in a form or some place in the VBA code. You have done everything you can, but the problem persists. If you are in this unhappy place, don’t despair. There is one more step to try that may solve your problem.

Start a brand new Access Database file. In this new file click on the External Data Tab, then click on Access:



On the next screen, Click on the Import tables, queries, forms…. option, choose the file you have been working with, then click OK. You will then see this screen:



On each of the tabs, choose “Select All” and then click OK. This will start a process that will bring in every object as well as code modules into the new database. Frequently at this stage, you will get a message stating that some object, typically a form, is corrupt and cannot be imported. In this case, you will have to rebuild that object. If that is the only message you get, you have identified your problem and can take things from there.

Make sure you check out both this month’s Trap as well as the Tip to get more gotchas and potential shortcuts along the way.
 

Conclusion

In most problem-solving situations, when you finally reach a solution and you understand what truly caused the problem, you have made progress toward becoming a better problem solver. If it was a tricky problem, you may want to document your solution, so that years downstream, if you encounter a similar problem, you won’t have to reinvent the wheel.
 

How to See the Young Couple

Focus on eyes of the old man and woman. The eye ridges are the hats of the young man, playing a guitar and the young woman listening. If you look closely you’ll see other details that you probably missed the first time around.

Tip of the Month: Rebuilding a Form

In this month’s Wizard, I reference rebuilding a form that is corrupt. These is a shortcut that sometimes works: In the original database, remove all code from the offending form and then copy and paste the form into your target database. In this database, you will have the form with no code.

Do a decompile/recompile on your database, then try to open the form. If it opens without incident, you then have the form and all its controls with no code.

From there it is a matter of rebuilding the code, which you can do by copying the code from the old form to the new form.  As you do this, I recommend you frequently hit the Debug/Compile routine in the code window to make sure you are not copying in some problematic code.

Although this can be a tedious process, it’s much easier than trying to recreate an existing from the ground up.






Trap of the Month: Beware of Missing Import Items

If you do a complete import of a database into a new file, you may assume that it imports everything you need. That is not the case. The import described here omits the following:
  • Import/Export Specs
  • Code References
The details and resolution to these are beyond the scope of this Wizard. However, if you google these terms, you will get more information that will help you import or include these items.


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886