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

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)



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Wylie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Tue, 16 Jan 2007 17:52:28 -0500, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

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

Douglas J. Steele wrote:
> 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)
>
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Wylie" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >

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 wrote:
> Douglas J. Steele wrote:
> > 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)
> >
> >
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no private e-mails, please)
> >
> >
> > "Wylie" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
>
> 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

"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)
> On Tue, 16 Jan 2007 17:52:28 -0500, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>
>> 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.

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

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

(please reply to the newsgroup)

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
> news:(E-Mail Removed)
>> On Tue, 16 Jan 2007 17:52:28 -0500, "Douglas J. Steele"
>> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>>
>>> 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.
>
> 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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
message news:%(E-Mail Removed)
> "Dirk Goldgar" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> 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?

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.

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

(please reply to the newsgroup)