baffling type mismatch error

[Wylie]

I'm trying to shift focus out of a continuous subform back to a control
on the mainform when the user tabs off the last record. However, I'm
get type mismatch error on "If Me.Bookmark = RS.Bookmark Then" that I
can't figure out. Both parent and child forms are bound to Sql Server
tables linked into to Access.

(on control in subform)
Private Sub Field_Value_KeyPress(KeyAscii As Integer)
If KeyAscii = 9 Or KeyAscii = 13 Then
Dim RS As Recordset
Set RS = Me.Recordset.Clone
RS.MoveLast
If Me.Bookmark = RS.Bookmark Then
Forms!Devices!Device_Notes.SetFocus
End If
End If
End Sub


thanks,

wylie

 

[Douglas J. Steele]

I'm guessing that you've got references set to both DAO and ADO.

The problem is that both the DAO and ADO models have a Recordset object in
them, but they're not interchangable. Access will pick whichever Recordset
object it comes to first and use that, regardless whether it's the correct
one. You can disambiguate to get around this by using either

Dim RS As DAO.Recordset

or

Dim RS As ADODB.Recordset

Now, I hate to say it, but I can never remember whether Me.Recordset.Clone
results in an ADO or a DAO recordset. (I _think_ Me.Recordset.Clone results
in an ADO recordset, while Me.RecordsetClone, without the period between
Recordset and Clone, results in a DAO recordset.) Because I can't remember,
I'm going to suggest cheating, and using

Dim RS As Object

instead.

(either that, or play with the two others to see whether one works)

 

[John Vinson]

Now, I hate to say it, but I can never remember whether Me.Recordset.Clone
results in an ADO or a DAO recordset. (I _think_ Me.Recordset.Clone results
in an ADO recordset, while Me.RecordsetClone, without the period between
Recordset and Clone, results in a DAO recordset.)

Your memory is correct.

John W. Vinson[MVP]

 

[Wylie]

I'm guessing that you've got references set to both DAO and ADO.

The problem is that both the DAO and ADO models have a Recordset object in
them, but they're not interchangable. Access will pick whichever Recordset
object it comes to first and use that, regardless whether it's the correct
one. You can disambiguate to get around this by using either

Dim RS As DAO.Recordset

or

Dim RS As ADODB.Recordset

Now, I hate to say it, but I can never remember whether Me.Recordset.Clone
results in an ADO or a DAO recordset. (I _think_ Me.Recordset.Clone results
in an ADO recordset, while Me.RecordsetClone, without the period between
Recordset and Clone, results in a DAO recordset.) Because I can't remember,
I'm going to suggest cheating, and using

Dim RS As Object

instead.

(either that, or play with the two others to see whether one works)

I'm not quite there yet. I've switched around DAO/ADO and
Recordsetclone / recordset.clone but it still errors. Looking into the
locals I see that the Me.Bookmark is a Variant/Byte(0 to 3) while the
RS.Bookmark is a Byte(0 to 3). Is there a way to recast one of these?

-wylie

 

[Wylie]

I'm not quite there yet. I've switched around DAO/ADO and
Recordsetclone / recordset.clone but it still errors. Looking into the
locals I see that the Me.Bookmark is a Variant/Byte(0 to 3) while the
RS.Bookmark is a Byte(0 to 3). Is there a way to recast one of these?

-wylie

ok, found an out of the box solution. Once I realized that the
bookmark stopped advancing once I got to the last record I checked for
a lack of change with a static. It seems to work after a few run
throughs, does anyone see a problem I don't?

Private Sub Field_Value_KeyPress(KeyAscii As Integer)
If KeyAscii = 9 Or KeyAscii = 13 Then
Static loc As Integer
If loc = Me.Bookmark(0) Then
Forms!Devices!Device_Notes.SetFocus
Else
loc = Me.Bookmark(0)
End If
End If
End Sub

-wylie

 

[Dirk Goldgar]

Your memory is correct.

Are you sure? I think that Me.Recordset.Clone will return either an ADO
or a DAO recordset, depending on whether the form's recordset is an ADO
or DAO recordset. Both the DAO and ADO Recordset objects have a Clone
method. As far as I can tell, in an Access 2002 ADP,
Form.RecordsetClone works the same way (which makes me wonder why they
bothered changing all the wizards when they introduced ADPs).

 

[Douglas J. Steele]

Are you sure? I think that Me.Recordset.Clone will return either an ADO
or a DAO recordset, depending on whether the form's recordset is an ADO or
DAO recordset.

I think you're correct, Dirk. That makes sense. So what's the default
recordset type for a form?

 

[Dirk Goldgar]

I think you're correct, Dirk. That makes sense. So what's the default
recordset type for a form?

In an .mdb file, it's a DAO recordset. I'm pretty sure that in an ADP
it's an ADO recordset, but I really have never worked with ADPs except
in one or two quick tests. Certainly in a quick test I did now on an
upsized database (converted from .mdb to .adp), the form I looked at had
an ADO recordset.