"Object invalid or no longer set"

  • Thread starter Thread starter Damian
  • Start date Start date
D

Damian

Often, when searching for specific records within a form,
I will receive one or more 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
 
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.
 
First of all, thank you for responding. I appreciate it.
I went through all of your suggestions but didn't have
much luck:
1. Make a backup of your database file while it is not in use.

-Done

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

-Tried it before, tried it again, no luck.
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
Click to expand...

-No missing references. Tried unselecting/reselecting
references (a suggestion I found elsewhere) but it didn't
make a difference.
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
Click to expand...
locate them.

-No errors came up.
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
Click to expand...
office\office\msaccess.exe" /decompile
"c:\My Documents\MyDatabase.mdb"
Then compact again.
Click to expand...

-Tried it. Didn't get rid of the problem.
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.
Click to expand...

-Tried it. Problem persisted in the new database.
Other suggestions
=============
7. The "#Name" problem might be resolved by downloading the latest service
pack for your version of Access from
Click to expand...
support.microsoft.com.

-No luck.
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
Click to expand...

-I don't believe I'm closing any system objects, unless I
am doing so without realizing it. What can I look for to
find out?
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.
Click to expand...

-Got interesting results. After using Option Explicit at
the top of the modules for each form/report, I found a lot
more errors when compiling. Strangely, correcting these
errors (which were all "Object not defined") caused their
associated procedures to stop working, even after I tried
to change them back. I reverted to my backup database at
this point.
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.
Click to expand...

-There are no Current or BeforeInsert events, and I'm
pretty sure the null values are being handled correctly. I
would also assume this is the case since for a few minutes
I am able to switch records without problems.
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.
Click to expand...

-I never declared any recordsets in my code (I'm not even
using DAO). What are the other types of objects I might
not be closing or dereferencing? Also, I don't think dirty
records are the issue because the problem occurs even when
browsing records and not making changes to any of the
fields.

Is there any more information I could give that would help
isolate the problem? I forgot to mention I'm using Access
2000. When clicking the "Help" button for any of the error
messages nothing happens. Also, while several users are
using the database I seem to be the only one to come
across this problem (although I don't know if it's just
because they haven't hit upon the circumstances which
cause it yet).

Once again, thank you so much for your time.

Damian
 
Darn! Hoped one of those things would work for you.

You have identified some weirdness around Option Explicit. This is a really
good trouble-shooter, so I would encourage you to ensure Name AutoCorrect is
off and your References are minimal. Compact after that. Then start adding
Option Explicit to one module at a time. Complile and fix any issues in that
module before proceeding to the next. The fact that removing Option Explict
again did not revert to the previous state hints at NameAutoCorrect problems
or corruption.

A particular example of "Object No Longer Set" involves running DAO
transactions on the default workspace. If you close the workspace afterwards
(which you should not, as you did not open it), you can get this error.

If you are using Access 2000, make sure you have the latest service pack
(SP3) from support.microsoft.com. While it won't solve your problem, it may
help prevent further corruption.
 
Allen,
I took your advice and re-instated the Option Explicit
into the modules and re-fixed the problems (the reason it
caused my code not to work last time was because I
incorrectly declared a variable as a string instead of a
boolean. It had been working up to that point because it
was being treated as a variant, I think).

As for running DAO transactions on a default workspace, I
don't think this is the case because I'm not using any DAO
at all. In fact, the only libraries I'm referencing are
the two default ones: Visual Basic for Applications and
Microsoft Access 9.0 Object Library.

One thing I noticed as I was testing out the database just
now is that it was working fine until I went back to the
main menu, returned to the central form and searched for a
record, which triggered the aforementioned error messages.
Usually the errors are triggered without having to go back
and forth but this time I was running the database as a
user would instead of in editing mode. This leads me to
believe that whatever causes the errors, it's a lot easier
to cause it when I'm in edit mode, and it may be related
to closing and re-opening the central form and its many
subforms. This would also explain why nobody else who uses
the database would have encountered these errors, as they
would probably only access the central form once per
session.I wish I knew what to do with this speculation but
I'm stumped.

In any case, I did manage to clean up my code a bit so it
hasn't been a total loss so far. Please let me know if you
have any other ideas, and once again thank you for your
time.

Damian
 
Good. You have some progress at pinning this down.

Do I understand that others are using this database at the same time that
you are switching forms into design mode and changing them? That's a really
good way to corrupt the database. If it is being used by multiple users,
consider splitting the database into front end/back end, and giving each
user their own copy of the front end (with the shared data tables in a
shared location and linked to each front end).

The other thing you mentioned was searching, so it may be worth tracing
whether there are concurrency issued with that.

If splitting is a new idea, there is a wizard: Tools | Database Utilities |
Splitter.
More info:
http://allenbrowne.com/ser-01.html
 
I guess I should have been more specific when describing
the situation: the database is in fact split up into a
front end and a back end. I just meant that nobody has
experienced the same issues I am currently experiencing
while they were using (their copy of) the front end.
I would like to hear more about checking concurrency
issues when running a search, though. How would I check
something like that?

Thanks,
Damian
 
Okay, great. You are already split, so you are working on a local copy of
the front end that no-one else is using.

Any time you do anything that would require Access to save the record, code
the save explicitly. We have found that this avoids all manner of weird
things from unhelpful messages through to lost entries. Examples:
- before you close a form (this one can lose the entry);
- before you set a form's Filter or OrderBy;
- before you try to move to another record;
- before you reassign the form's RecordSource;
- before you open another form or report that will access the same data;
- before you open a recordset or execute an action query on the same data;
- before a requery.

Even, then you can still hit concurrency issues e.g. if you have a form open
and it has a text box bound to a memo field, and you attempt to modify the
memo in another form even though the first one is not dirty.

To achieve the save, we just use:
If Me.Dirty Then
Me.Dirty = False
End If
since that works even if the form does not have focus, and generates a
trappable error if the save fails for any reason.

Not sure this will sort out your issues, but it's good practice anyway.
 
Allen,
Before I start fiddling with the Dirty property, there is
something I would like to know about how it works.
Basically, the main form I'm working on in this case has a
large number of subforms (accessible through their
respective tabs), many of which are linked to editable
data (others list read-only data). Will I have to save the
current record explicitly for every subform or will the
Me.Dirty = False property work for every open subform
automatically?

Damian
 
Each bound form has its own Dirty property.

However, the user cannot move the focus from the subform to the main form
without triggering a save in the subform. This means that if the user has
clicked a button on the main form, the main form has focus, and so you do
not need to worry about the subforms being dirty UNLESS you have dirtied
them programmatically.

So, that's another case where it would be a good idea to immediately clean
up after yourself, i.e. if you write to a bound control in a subform from
the main form's module, explictly save the subform's record straight away so
as to minimize the potential the concurrency issues.
 
Back
Top