3420 Object Invalid or no longer set.

[Louverril]

I use this code to check for no records in the before update event to prevent
the forms from trying to validate a blank record.

If Me.RecordsetClone.RecordCount = 0 Then 'don't try to validate empty record
GoTo CCExit
End If

I have three forms lets call them MainMainForm, it's subform MainForm and
MainForm's subform SubForm. Three levels. The code above is in the MainForm
and SubForm Before Upfdate events.

Mainform is loaded with an initally query limiting the initial display to
records meeting a criteria.

The problem is occasionally when clicking between MainForm and Subform
perhaps applying a fiter -I can not pin it down. I get this error :
3420 Object Invalid or no longer set.

I'm pretty sure it's due to the code above - what's wrong with it?! Are the
two recordsets getting mixed up? How can I get around this?

Please help

Lou

 

[Allen Browne]

One cause of this bug is closing the default workspace. Access doesn't
complain, and silently opens it again, but the Database used by the
RecordsetClone does not get automatically opened again, so the object is
"invalid or no longer set" until you close and reopen the form.

There are other mistakes you can make that will trigger this bug as well,
but I guess I don't understand what you are trying to do here. The form's
BeforeUpdate event doesn't fire unless there is a record to be saved.

 

[Louverril]

Allen thanks here is some more information.

See code below. I put a breakpoint on the lostfocus and beforeupdate events.

I spent 25 minutes simply selecting a couple of letters in the CO_Desc
field, right clicking and either applying a filter for the selected letters
(option from the right click menu) OR using the right click option to clear
filters.

The code ran variously as follows:

Lostfocus
Beforeupdate

Before update
Lostfocus

Neither ran.

Sometimes I got the 3420 error – only after some runs of the before update
– not everytime it ran.

When I commented out the recordset code – as earlier I could not get the
error.

So it definitely something to do witht e recordset stuff.

BeforeUpdate

If Me.RecordsetClone.RecordCount = 0 Then 'don't try to validate empty record
GoTo CCExit
End If

Private Sub CO_Desc_LostFocus()
'Make Title case
On Error GoTo CCError
CCTitleCase Me.CO_Desc
Exit Sub
CCError:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub

Public Function CCTitleCase(Ctl As TextBox)
On Error GoTo CCError
If Len(Ctl.Text & vbNullString) <> 0 Then
Ctl.Text = StrConv(Ctl.Text, vbProperCase)
End If
Exit Function
CCError:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Function

 

[David W. Fenton]

BeforeUpdate

If Me.RecordsetClone.RecordCount = 0 Then 'don't try to validate
empty record
GoTo CCExit
End If

I don't understand why this code would ever run as true for the
condition you're testing. If the form's BeforeUpdate is firing, then
there's a record. If there's no record, there is nothing that could
be updated.

Put another way, a form whose recordsetclone has 0 records in it has
no editable controls, so there's no way to trigger a BeforeUpdate
event, as there is no current record.

So, surely the problem must be somewhere else.

Are you by chance using a class module in this BeforeUpdate event?
If so, be sure to turn of BREAK IN CLASS MODULE in the VBE options.
Otherwise, you won't see the actual error in the class module, but
will instead only see the line of code that invokes the class
module.

 

[Louverril]

Just been timed out - again - giving a long reply to this. In short after a
quick test you may be right the BeforeUpdate doesn't fire becuase of the
lostfocus when its a blank record - but it did solve a problem at one point.

Will test out properly and report back.

Thanks
Lou

 

[Louverril]

mainI have a combo box on mainform which needs to do a requery to change the
data in maniform and subform. When this requery occurs the beforeupdate event
on mainform is triggered even if the mainform record has not been touched -
i.e. should be no record

???
Lou

 

[Louverril]

There is a field cause CO_Position which is set wiuth a default value of 200
- via teh property window. That value appears in this fireld when hte form is
first loaed. If you click in the form (and get teh error message about all
required fields not being enterred) and then press Esc teh 200 disppears and
you can use the combo box with no errors.

It's as if teh form load is half adding a record but I can't see how.

 

[Louverril]

Solved - I was setting a value for the subform in the main form on the select
combo and other buttons and this sort of half added a record. I used the
DefaultValue property to set this value to various defaults and the record
stayed aas "no reocrd"

 

[David W. Fenton]

I have a combo box on mainform which needs to do a requery to
change the data in maniform and subform. When this requery occurs
the beforeupdate event on mainform is triggered even if the
mainform record has not been touched - i.e. should be no record

A requery will save any unsaved edits that are pending before the
requery, so if any other changes have been made to the recordsource,
you'd see the form's BeforeUpdate event fire.

But I just tested with an unbound combo box that requeries the form
in its AfterUpdate event. It does *not* cause the BeforeUpdate event
to fire. I also put in code to check Me.Dirty before the requery,
and if the record has not been edited (i.e., Me.Dirty is False),
then there's no triggering of the form's BeforeUpdate event.

This is exactly as expected.

Is your combo box unbound? If it's not, that would explain why it's
triggering the event.

 

[David W. Fenton]

There is a field cause CO_Position which is set wiuth a default
value of 200 - via teh property window. That value appears in this
fireld when hte form is first loaed. If you click in the form (and
get teh error message about all required fields not being
enterred) and then press Esc teh 200 disppears and you can use the
combo box with no errors.

Default values do not dirty the form.

Do you have the record selector displayed? If so, you'd see that
going to a new record does not dirty the form (when the form is
dirty, the record selector shows the pencil icon, which means the
record has been edited).

However, if you *click* the record selector, it will save the record
*if* it is already dirty (it won't save a new record that hasn't
been edited at all).

 

[David W. Fenton]

Solved - I was setting a value for the subform in the main form on
the select combo and other buttons and this sort of half added a
record. I used the DefaultValue property to set this value to
various defaults and the record stayed aas "no reocrd"

There had to be something dirtying the record for the BeforeUpdate
event to fire, as I said from the beginning. It's just that it's
sometimes hard to figure out what it is that's doing it!

Glad you solved it.

 

[Louverril]

Thanks very much for your help David. You gave me a place to start looking
for the problem!

Lou.