DataWright Information Services

Consulting and Resources for Excel and Access




Recovering a form whose code no longer works

If a form with VBA code suddenly starts giving messages that the
event does not result in a recognised macro or function, and the
code no longer runs, you may have a corrupted code module. The first
step is to identify the culprit; then you need to recover it.

Try to identify the damaged / corrupted form

There are a couple of ways to do this but one useful option is to
import everything into a new, blank database. Create the new
database and then copy everything across from the original.

Importing in Access 2003 or earlier

File > Get External Data > Import, and browse to the original
database.

In the dialog select all objects on all tabs. Also go to the
options and import relationships and custom toolbars. Import
structure and data for tables, and import queries as queries.

Importing into Access 2007

External Data > Access, and browse to the original database. Make
sure you keep the option to import the objects.

Select the options as described above.

For all versions click OK to start the import process. If the
database is large this could take a while. If it completes without
errors you may be OK; compact and repair the database, and see if
everything works.

However, one of more objects may not be importable. Note which
ones they are; these are the corrupt objects and they will need to
be recovered or replaced.

Try either of these methods to recover the form:

Delete and re-create the code module

In Design view go to the form’s code. Copy the contents of the
module and paste into Notepad

In the form’s properties, go to the Other tab and change
HasModule to No. You will get a warning that code on the form will
be deleted. Click OK, then save and close the form.

Re-open the form in design view, change HasModule to Yes, view
the code, and paste all the code back from Notepad. All going well,
your form will start running correctly again.

Use the SaveAsText method

There is a feature in Access that converts forms and reports to
text files. It’s used in the creation of Access templates, but it
can also be used to archive a form or report, and I have used it to
successfully recover a corrupted form.

Go to the code editor (Alt+F11) and Debug > Compile. If the
project compiles, go to the next step; otherwise work through the
errors, correcting them and fixing broken or missing references,
until the code compiles.

Export the form to a text file

In the Immediate window (Ctrl+G to display) type SaveAsText and
press the spacebar. An Intellisense tool tip will appear, showing
you the syntax for the function. Press Enter to run the code.

As an example, this code will save a form called frmUtility to
the same directory as the current database, naming the file with a
.txt extension:

SaveAsText acForm," frmUtility ", CurrentProject.Path & " frmUtility.txt"

If you open this text file you will see a full description of the
form, with all of its controls and code.

Restore the form from the text file

To restore the damaged form you can use the LoadFromText method.

Type this in the Immediate window and press Enter:

LoadFromText acForm, “frmUtility”, CurrentProject.Path & “frmUtility.txt”

If the incoming form encounters another object of the same name,
it overwrites it.

Why this works

VBA code is compiled before it runs. You can’t see the compiled
version and sometimes this meta-code gets corrupted. In that case
you need a way to flush the compiled code out of the system, and
doing the round-trip via a text file is one way to do that.

Note: this should also work with reports. If you still can’t
recover the form/report at this stage, you will need to import it
from a recent backup.