"Object invalid or no longer set" error

[Damian]

Often, when searching for specific records within a form,
I will receive one of several messages, including "Unknown
function name" (this often appears first), "The expression
is typed incorrectly, or it is too complex... (etc)",
and "Object invalid or no longer set".

What I don't understand is why these error messages only
appear after the form has been running for a few minutes.
For a while it runs perfectly fine, but once I start
getting these error messages several fields in my subforms
will be replaced with #Name? instead of the correct
values. (The "Object invalid or no longer set" message has
to be closed for every field this happens to)

This doesn't seem to be a result of calling a flawed
function because it's usually triggered just by switching
to a new record. I don't believe I've ever made reference
to the CurrentDb function so I don't think it's a matter
of correctly declaring a database variable somewhere. The
links to the source fields are fine, as proven by the fact
that the form runs perfectly for a little while, even when
switching records.

Does anybody have any ideas why this is going on?

Damian

 

[Allen Browne]

There are many possible causes for this.

1. Make a backup of your database file while it is not in use.

2. Compact the database to get rid of any temporary objects:
Tools | Database Utilities | Compact.

3. Open the Immediate Window (Ctrl+G).
Choose References from the Tools menu.
Check that none are marked "MISSING".
Deselect any references you don't need.Typically you need the first 2
(VBA and Access, which cannot be deselected), and either DAO or ADO. More
info on references:
http://allenbrowne.com/ser-38.html

4. Still in the Immediate Window, choose Options from the Tools menu.
Compile from the Debug menu. Fix any problems, and repeat until there are no
errors. If there are any naming issues, this should help locate them.

5. If the problem is still present, close Access, and open a command prompt
(DOS box). Enter something like this to decompile your application. It's one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\My Documents\MyDatabase.mdb"
Then compact again.

6. If the problem is still present:
a) Create a new (blank) database;
b) Uncheck the "Name AutoCorrect" boxes under Tools | Options | General
c) Press Ctrl+G and choose the right references.
d) Import all objects from the faulty database: File | Get External |
Import.
e) Press Ctrl+G and choose Compile from the Debug menu.

Other suggestions
=============
7. The "#Name" problem might be resolved by downloading the latest service
pack for your version of Access from support.microsoft.com.

8. The "Object no longer set" can be caused by closing a system object that
should be left open. Example in:
http://allenbrowne.com/ser-37.html

9. Using Option Explicit at the top of every module, and qualifying your
expressions (e.g. Me.Surname rather than just Surname) helps to pinpoint bad
names.

10. If the problem tends to happen when you move to a new record:
- double-check anything in the form's Current and BeforeInsert events.
- double-check for anything that may not be handling the Null values of the
new record correctly.

11. If the problem occurs only after the form has been running for a while:
- double-check for any objects (such as recordsets) that you are not closing
and dereferencing (i.e. setting to Nothing).
- look for any possible concurrency issues, e.g. trying to open another
recordset/form while you have one open where the record is already dirty, or
trying to move record/filter/reorder/reassign recordsource/close while the
record is still dirty.