#ERROR Message When Previously Worked Fine

[Joe Williams]

I have a database that has forms that display a value from a couple custom
functions that I have written. They have worked fine, no issues at all.

All of a sudden, today the functions no longer work and display #ERROR when
the function is called from a query or in a text box on a form.

I have several different copies of the database and the functions no longer
work in any of these databases. All databases compile in the module VBA
window.

What would cause the functions to stop working all of a sudden? Where do I
start in debugging this problem?

Thanks

Joe

 

[fredg]

I have a database that has forms that display a value from a couple custom
functions that I have written. They have worked fine, no issues at all.

All of a sudden, today the functions no longer work and display #ERROR when
the function is called from a query or in a text box on a form.

I have several different copies of the database and the functions no longer
work in any of these databases. All databases compile in the module VBA
window.

What would cause the functions to stop working all of a sudden? Where do I
start in debugging this problem?

Thanks

Joe

Check your references first.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Allen Browne]

These can be hard to trace, because once Access finds one calculated control
that it cannot resolve, it gives up on the others too. That means that one
bad one can cause #Error in many that are inherently okay.

To debug this, create a copy of your database, and work in the copy.
Delete half the calculated controls on the form.
#Error disappears?
- Yes: put half of that half back onto the form
- No: remove half of the remaining ones.

Naturally, you do have to be aware of dependencies between the calculated
controls, but that basic approach will help you pin down which is the faulty
one.

Presumably you have already tested the function itself in the Immediate
window (Ctrl+G).

If the error suddenly turned up without any obvious cause, it might end up
being that Access is confused about the names of the objects. This can
happen if:
a) you have a control that has the same Name as a field, but is bound to an
expression, or
b) Name AutoCorrect is corrupting your database. Details:
http://members.iinet.net.au/~allenbrowne/bug-03.html