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.