RecordsetClone not working Access 2007

[So Call Me Crazy]

I have code to handle the deletion of a record that appears on a form.
Deleted the current recordset on the form and then either moved to the next
record, or the previous, depending.... Worked great in 2003. Now getting an
error of type mismatch when setting the variable rst with RecordsetClone!
Huh?!? Here's the code:

Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

Set rst = Nothing
On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If rst.EOF Then
.Recordset.MovePrevious
Else
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modRefresh"
GoTo DelCurrentRec_Exit

End Function

Thanks for your help!

 

[Douglas J. Steele]

I don't have Access 2007 installed on this machine, so I can't test, but see
whether changing your declaration to

Dim rst As DAO.Recordset

makes a difference. If not, try

Dim rst As ADODB.Recordset

The problem is that Recordset is the name of an object in both models, so
unless you disambiguate, you can't control which one you're getting. It's
possible that in your Access 2003 database, you'd removed the reference to
ADO so that you didn't have this problem.

 

[So Call Me Crazy]

That was it! Awesome! Thank you!

I don't have Access 2007 installed on this machine, so I can't test, but see
whether changing your declaration to

Dim rst As DAO.Recordset

makes a difference. If not, try

Dim rst As ADODB.Recordset

The problem is that Recordset is the name of an object in both models, so
unless you disambiguate, you can't control which one you're getting. It's
possible that in your Access 2003 database, you'd removed the reference to
ADO so that you didn't have this problem.

 

[David W. Fenton]

=?Utf-8?B?U28gQ2FsbCBNZSBDcmF6eQ==?=

With frmSomeForm
Set rst = .RecordsetClone

I simply do not understand why anyone would bother to initialize a
recordset object variable for an object that already exists and can
be easily referred to directly. When working with a recordsetclone,
I always do this:

With frm.RecordsetClone
.FindFirst ...
End With

I would also question why you would be doing record deletions using
a recordset clone. Why not just run the SQL to delete the data, then
requery your form? And even if there *were* a good reason to do it
the way you're doing it, why do you repeat the code that chooses
whether to go forward or back? Why not just .MoveFirst and then do
all .MoveNext's until .EOF is true?

In my opinion, your code is really badly designed. It's doing
everything in the wrong way.