DAO Recordset becomming Invalid (Error 3420)

[Lendog]

Here's the basic problem: I have a section of code whose purpose is to move
the form to the record of my chosing. The field in the form looks to see if
an existing data has been changed, and if so attempts to find the record that
matched it and moves the bookmark to it. I've used this same code in earlier
versions of Access with no problems.

When the code runs the first time the databse is opened it makes it all the
way to the bookmark line before erroring out stating that my "recClone"
recordset is "3420: Object Invalid or No Longer Set"

When the code runs the second time, it gets this error on the 'findfirst' line

I've looked at it about a dozen different ways, and tried several things,
and I'm beginning to believe that I have a corrupted dao360.dll file. Here's
the code:

If Not Me.NewRecord Then

Dim recClone As DAO.Recordset
Set recClone = Me.RecordsetClone

recClone.FindFirst ("RFI = '" & Me.RFI & "'")
If Not recClone.NoMatch Then
Cancel = True
Me.Undo
Me.Bookmark = recClone.Bookmark
Else
If MsgBox("RFI not found. Change current record?", vbYesNo, _
"Change Current RFI") = vbNo Then Cancel = True
End If
recClone.Close
Set recClone = Nothing
End If

If I changed things around, this next piece of code runs successfully, but
only the first time, then it errors out at the 'Findfirst' line as the first
one does.

If Not Me.NewRecord Then

Dim intPosition As Integer
Dim recClone As DAO.Recordset
Set recClone = Me.RecordsetClone

recClone.FindFirst ("RFI = '" & Me.RFI & "'")
intPosition = recClone.AbsolutePosition
If Not recClone.NoMatch Then
Cancel = True
Me.Undo
Me.Recordset.MoveFirst
Do While Not Me.Recordset.AbsolutePosition = intPosition
Me.Recordset.MoveNext
Loop
Else
If MsgBox("RFI not found. Change current record?", vbYesNo, "Change
Current RFI") = vbNo Then Cancel = True
End If
recClone.Close
Set recClone = Nothing
End If

Has anyone run into this?

 

[Dirk Goldgar]

Here's the basic problem: I have a section of code whose purpose is to
move
the form to the record of my chosing. The field in the form looks to see
if
an existing data has been changed, and if so attempts to find the record
that
matched it and moves the bookmark to it. I've used this same code in
earlier
versions of Access with no problems.

When the code runs the first time the databse is opened it makes it all
the
way to the bookmark line before erroring out stating that my "recClone"
recordset is "3420: Object Invalid or No Longer Set"

When the code runs the second time, it gets this error on the 'findfirst'
line

I've looked at it about a dozen different ways, and tried several things,
and I'm beginning to believe that I have a corrupted dao360.dll file.
Here's
the code:

If Not Me.NewRecord Then

Dim recClone As DAO.Recordset
Set recClone = Me.RecordsetClone

recClone.FindFirst ("RFI = '" & Me.RFI & "'")
If Not recClone.NoMatch Then
Cancel = True
Me.Undo
Me.Bookmark = recClone.Bookmark
Else
If MsgBox("RFI not found. Change current record?", vbYesNo, _
"Change Current RFI") = vbNo Then Cancel = True
End If
recClone.Close
Set recClone = Nothing
End If

If I changed things around, this next piece of code runs successfully, but
only the first time, then it errors out at the 'Findfirst' line as the
first
one does.

If Not Me.NewRecord Then

Dim intPosition As Integer
Dim recClone As DAO.Recordset
Set recClone = Me.RecordsetClone

recClone.FindFirst ("RFI = '" & Me.RFI & "'")
intPosition = recClone.AbsolutePosition
If Not recClone.NoMatch Then
Cancel = True
Me.Undo
Me.Recordset.MoveFirst
Do While Not Me.Recordset.AbsolutePosition = intPosition
Me.Recordset.MoveNext
Loop
Else
If MsgBox("RFI not found. Change current record?", vbYesNo,
"Change
Current RFI") = vbNo Then Cancel = True
End If
recClone.Close
Set recClone = Nothing
End If

Has anyone run into this?

I'm not sure, but I suspect the line:

recClone.Close

You don't want to close the cloned recordset, since you didn't open it and
you intend to use it again. Does removing that line fix the problem?

 

[Lendog]

Thanks Dirk, but that was not the problem. The subroutine does intentional
open and close the cloned recordset each time it is run. I've used this same
code before with no problems, and the same error moves to a different line
after the subroutine is executed the first time. If I completely exit Access
and run it again, the cycle starts over. It seems like once the
me.recordsetclone is executed the first time, it will not run properly
afterwards, and the me.Undo statement is when the clone becomes invalid the
first time. Very frustrating.

 

[Dirk Goldgar]

Thanks Dirk, but that was not the problem. The subroutine does
intentional
open and close the cloned recordset each time it is run.

While I wouldn't do that, and think that you must be forcing unnecessary
extra processing behind the scenes, I've tested with a form of my own and it
doesn't give me an error. What version of Access are you using? Did you
try it without the recClone.Close just to see if makes a difference?

I've used this same
code before with no problems, and the same error moves to a different line
after the subroutine is executed the first time. If I completely exit
Access
and run it again, the cycle starts over. It seems like once the
me.recordsetclone is executed the first time, it will not run properly
afterwards, and the me.Undo statement is when the clone becomes invalid
the
first time. Very frustrating.

Are you sure you aren't raising an error on the FindFirst? Is RFI a text
field, as your code implies?

Are you having any other odd problems in other forms or processes? If it's
just this form behaving badly, you may have a corrupt form or corrupt VB
project. Decompiling might help.

 

[Lendog]

To answer your first question, I'm using 2007.

I realize that my approach may border on unorthodox, it aloows the use of a
cloned recordset which will generate a compatable bookmark. While I could
leave the recordset open in the background, I would have to deal with making
sure that it is current each time it is used. Honestly, I have not
investigated this approach (or others) because its always worked and never
created a preformance problem - until now.

I am leaning toward an ill behaving form. I can remember one other occasion
where I ended up copying all the stuff from one form to another because of
some unexplainable behavior and that fixed the problem - maybe I'll attempt
that next.

As far as the error goes, it does not raise on the 'findfirst' line the
first time it is executed, but does after subsequent executions. I can watch
the recordset in the watch window as I step through the execution, and
observe it as it becomes invalid after the first run of the m.Undo statement.
On subsequent runs, the me.recordsetclone does NOT pass a valid recordset,
so recClone is never valid.

I'll go one furhter - I reconstructed the code such that I do not use the
recordsetclone method, but instead create a recordset based on the forms
recordsource. Keep in mind that I tried snapshot and dynaset both with and
without readonly, the me.Undo killed it each time. I added code to close the
dead recordset and reopened it again, searched the same search, and then used
absolute position to set the current record in the form as the bookmark trick
generates an error (might have something to do with using a recordset that is
not a clone). Talk about Frankenstien code, but it worked.

I did not fix the problem, just worked around it. I hate that. Hate it.

You mentioned something of decompiling. I'm not familiar with that. I
thought that access compiled as it ran.

 

[Dirk Goldgar]

To answer your first question, I'm using 2007.

I just tried the same thing in A2007 -- I was using A2003 before -- and it
worked fine for me. So the fault isn't inherent in the approach.

I realize that my approach may border on unorthodox,

Only to the extent that you're using a bound control for navigation. That's
a tricky business, but it works fine if you handle it properly.

it aloows the use of a
cloned recordset which will generate a compatable bookmark.

Using RecordsetClone to navigate is pretty standard practice. The combo box
wizard to "find a record on my form" generates code that does that.

While I could
leave the recordset open in the background, I would have to deal with
making
sure that it is current each time it is used.

Actually, you wouldn't. You're using the FindFirst method, which always
searches from the beginning of the recordset. There's no need to make it
current. In my opinion, by closing it and reopening it each time, you're
doing unnecessary work. However, I've determined that this is not the
source of your problem.

As far as the error goes, it does not raise on the 'findfirst' line the
first time it is executed, but does after subsequent executions. I can
watch
the recordset in the watch window as I step through the execution, and
observe it as it becomes invalid after the first run of the m.Undo
statement.
On subsequent runs, the me.recordsetclone does NOT pass a valid recordset,
so recClone is never valid.

This is quite odd.

I'll go one furhter - I reconstructed the code such that I do not use the
recordsetclone method, but instead create a recordset based on the forms
recordsource. Keep in mind that I tried snapshot and dynaset both with
and
without readonly, the me.Undo killed it each time. I added code to close
the
dead recordset and reopened it again, searched the same search, and then
used
absolute position to set the current record in the form as the bookmark
trick
generates an error (might have something to do with using a recordset that
is
not a clone).

Right. Bookmarks are not compatible except for the same recordset or
between a recordset and its clone.

You mentioned something of decompiling. I'm not familiar with that. I
thought that access compiled as it ran.

Access compiles uncompiled code the first time it is run, and then after
that uses the compiled code until the source code is changed. So sometimes
the compiled object code gets corrupted. Access has a /decompile
command-line switch that forces the compiled object code to be discarded.

Here's how to decompile:

1. With the database closed -- no users in it at all -- and (ideally) Access
not running, make a backup copy.

2. On the task bar, click Start -> Run...

3. Enter this (adapted to match your database path and name) in the Run
dialog box and click OK:

msaccess.exe /decompile "C:\My Documents\YourDBName.accdb"

You may need to include the full path to msaccess.exe, but I don't find that
to be the case when I try it. Depending on your Access version, you may not
get any sign that anything in particular happened. Your database will
probably open in Access.

4. Compact and Repair your database.

5. Open the database again, press Alt+F11 to switch to the VB Editor, and
click Debug -> Compile (your project). If any errors appear, fix them and
recompile.

6. Close the VB Editor. Compact & Repair again.

See if the problem has disappeared.

 

[Lendog]

Dirk,

First of all, I want to thank you for the time you've given me on this...
onwards..

I just tried the same thing in A2007 -- I was using A2003 before -- and it
worked fine for me. So the fault isn't inherent in the approach.


Only to the extent that you're using a bound control for navigation. That's
a tricky business, but it works fine if you handle it properly.


Using RecordsetClone to navigate is pretty standard practice. The combo box
wizard to "find a record on my form" generates code that does that.


Actually, you wouldn't. You're using the FindFirst method, which always
searches from the beginning of the recordset. There's no need to make it
current. In my opinion, by closing it and reopening it each time, you're
doing unnecessary work. However, I've determined that this is not the
source of your problem.

This would be correct if the original recordset did not change after it was
cloned, which is very possible with my particular application. Without
refreshing the clone, the findfirst would not find any added records.

This is quite odd.


Right. Bookmarks are not compatible except for the same recordset or
between a recordset and its clone.


Access compiles uncompiled code the first time it is run, and then after
that uses the compiled code until the source code is changed. So sometimes
the compiled object code gets corrupted. Access has a /decompile
command-line switch that forces the compiled object code to be discarded.

Here's how to decompile:

1. With the database closed -- no users in it at all -- and (ideally) Access
not running, make a backup copy.

2. On the task bar, click Start -> Run...

3. Enter this (adapted to match your database path and name) in the Run
dialog box and click OK:

msaccess.exe /decompile "C:\My Documents\YourDBName.accdb"

You may need to include the full path to msaccess.exe, but I don't find that
to be the case when I try it. Depending on your Access version, you may not
get any sign that anything in particular happened. Your database will
probably open in Access.

4. Compact and Repair your database.

5. Open the database again, press Alt+F11 to switch to the VB Editor, and
click Debug -> Compile (your project). If any errors appear, fix them and
recompile.

6. Close the VB Editor. Compact & Repair again.

See if the problem has disappeared.

--

I will try this before moving things to a new form. I will post the results
of this attempt later once complete - properlt after this weekend.

 

[Dirk Goldgar]

Dirk,

First of all, I want to thank you for the time you've given me on this...

You're welcome.

This would be correct if the original recordset did not change after it
was
cloned, which is very possible with my particular application. Without
refreshing the clone, the findfirst would not find any added records.

This may reflect a misunderstanding. If you add records through the form's
recordset (as in normal form operations), the recordsetclone will reflect
them without having to be closed and reopened. Maybe there's something else
you're doing on your form that invalidates this in some way, but you
wouldn't normally have to close the recordsetclone.

I will try this before moving things to a new form. I will post the
results
of this attempt later once complete - properlt after this weekend.

Thanks. If the decompile doesn't work, then you can also try exporting the
form to text and back again, using the only-barely-documented
Application.SaveAsText and Application.LoadFromText methods. Here's an
example (using the Immediate Window):

Application.SaveAsText acForm, "YourForm", "YourForm.txt"
DoCmd.Rename "YourForm_OLD", acForm, "YourForm"
Application.LoadFromText acForm, "YourForm", "YourForm.txt"

If you're typing that in, don't be concerned that you don't get intellisense
for SaveAsText and LoadFromText until you finish entering the whole method
name. They're hidden methods.

 

[Stuart McCall]

You're welcome.


This may reflect a misunderstanding. If you add records through the
form's recordset (as in normal form operations), the recordsetclone will
reflect them without having to be closed and reopened. Maybe there's
something else you're doing on your form that invalidates this in some
way, but you wouldn't normally have to close the recordsetclone.


Thanks. If the decompile doesn't work, then you can also try exporting
the form to text and back again, using the only-barely-documented
Application.SaveAsText and Application.LoadFromText methods. Here's an
example (using the Immediate Window):

Application.SaveAsText acForm, "YourForm", "YourForm.txt"
DoCmd.Rename "YourForm_OLD", acForm, "YourForm"
Application.LoadFromText acForm, "YourForm", "YourForm.txt"

If you're typing that in, don't be concerned that you don't get
intellisense for SaveAsText and LoadFromText until you finish entering the
whole method name. They're hidden methods.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk

FYI if you right-click anywhere in the object browser's 'members' pane and
put a check on 'Show Hidden Members', you'll get full intellisense for the
command.

 

[Lendog]

Well, Dirk, I tried both approaches, with no success. I suspect something is
corrupted, but I just don't know what. I get the same series of unusaul
erros.

Perhaps I'll try an experiment; I'll try this code in a completely different
database.

 

[Lendog]

Thanks for the tip Stuart. I wish I had know that sooner.