3420 Object Invalid or no longer set.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Louverril - 21 Sep 2007 16:10 GMT

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

Reply to this Message

Allen Browne - 21 Sep 2007 16:36 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I use this code to check for no records in the before update event to
>prevent
[quoted text clipped - 24 lines]
>
> Lou

Reply to this Message

Louverril - 21 Sep 2007 17:40 GMT

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

> 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
[quoted text clipped - 33 lines]
> >
> > Lou

Reply to this Message

David W. Fenton - 22 Sep 2007 05:18 GMT

> 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.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

Louverril - 22 Sep 2007 12:00 GMT

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

> > BeforeUpdate
> >
[quoted text clipped - 19 lines]
> will instead only see the line of code that invokes the class
> module.

Reply to this Message

Louverril - 24 Sep 2007 16:12 GMT

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

> > BeforeUpdate
> >
[quoted text clipped - 19 lines]
> will instead only see the line of code that invokes the class
> module.

Reply to this Message

Louverril - 24 Sep 2007 16:34 GMT

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.

> 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
[quoted text clipped - 27 lines]
> > will instead only see the line of code that invokes the class
> > module.

Reply to this Message

Louverril - 24 Sep 2007 17:42 GMT

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 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
[quoted text clipped - 35 lines]
> > > will instead only see the line of code that invokes the class
> > > module.

Reply to this Message

David W. Fenton - 26 Sep 2007 01:38 GMT

> 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.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

Louverril - 26 Sep 2007 10:18 GMT

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

Lou.

> > 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
[quoted text clipped - 6 lines]
>
> Glad you solved it.

Reply to this Message

David W. Fenton - 26 Sep 2007 01:37 GMT

> 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).

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

David W. Fenton - 26 Sep 2007 01:31 GMT

> 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.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message