RecordsetClone BOF stays True! 1

[TheAceMan1]

Howdy All . . .

The following is a function setup to repeat a problem that shows BOF stays true:

[blue]Public Function qTest(ID As Long) As Boolean
   Dim rst As DAO.Recordset
   
   Set rst = Me.RecordsetClone
   [purple][b]Debug.Print[/b][/purple] ID & " StartBOF = " & rst.BOF;
   
   If Not rst.BOF Then
        rst.MovePrevious
         [purple][b]Debug.Print[/b][/purple] " PrevBOF = " & rst.BOF;
   End If
   
   [purple][b]Debug.Print[/b][/purple]
   rst.Close
   Set rst = Nothing
   
End Function[/blue]

Take note of the [purple]debug[/purple] statenments. The function is called by the following code in a command button but1.

[blue]Private Sub but1_Click()
   Dim x As Long
   
   For x = 1 To 4
      DoEvents
      qTest x
   Next

End Sub[/blue]

x is simply existing values in an autonumber field Named [blue]ID[/blue]. Note when the button is clicked the immediate window reveals the following:

[blue]1 StartBOF = False PrevBOF = True
2 StartBOF = True
3 StartBOF = True
4 StartBOF = True
1 StartBOF = True
2 StartBOF = True
3 StartBOF = True
4 StartBOF = True[/blue]

In the function I close and set rst to nothing, then on successive calls I dim and set a recordsetclone. Microsoft says the folowing in help for a recordsetclone:

[blue]When a new Recordset object is opened, its first record is the current record.[/blue]

Can someone tell me why BOF is true on successive calls? ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I believe the clone is a shallow not a deep copy. I am guessing there is some property that when you move to bof it is being changed on the actual recordset. But I checked and the actual recordset bof is still false. However if you do this I think it appears to work as you expect (but you may have to set the current record).

Public Function qTest2(ByVal ID As Long) As Boolean
   Dim rst As DAO.Recordset
   Dim rs As DAO.Recordset
   Set rs = Me.Recordset
   Set rst = rs.Clone
   Set rs = Nothing
   Debug.Print ID & " Clone StartBOF = " & rst.BOF
   If Not rst.BOF Then
        rst.MovePrevious
        Debug.Print " Clone PrevBOF = " & rst.BOF
   End If
   Debug.Print
   rst.Close
   Set rst = Nothing
End Function

The recordsetclone and the recordset.clone are cloned in different ways I guess. The .clone does not have a current record when you clone it.

 

[TheAceMan1]

MajP . . .

Its good to hear from you! ... and I agree with all you've stated. You've related to some of my very suspicions. I'm about to throw a monkey-wrench in this by changing the function to the following (note the only changes in [purple]purple[/purple]):

[blue]Public Function qTest(ID As Long) As Boolean
   Dim [purple][b]db As DAO.Database[/b][/purple], rst As DAO.Recordset
   
   [purple][b]Set db = CurrentDb[/b][/purple]
   Set rst = [purple][b]db[/b][/purple].OpenRecordset(Me.RecordSource, dbOpenDynaset)
   [green]'The recordsource at this point is a simple SQL statement of 3 fields ...
   ' ... the very same I mentioned earlier ... ID as AutoNumber, actName as Text, Amount as Currency[/green]
   
   Debug.Print ID & " StartBOF = " & rst.BOF;
   
   If Not rst.BOF Then
        rst.MovePrevious
         Debug.Print " PrevBOF = " & rst.BOF;
   End If
   
   Debug.Print

   Set rst = Nothing
   set db = Nothing
   
End Function[/blue]

Now when I hit the button I Get:

[blue]
1 StartBOF = False PrevBOF = True
2 StartBOF = False PrevBOF = True
3 StartBOF = False PrevBOF = True
4 StartBOF = False PrevBOF = True
1 StartBOF = False PrevBOF = True
2 StartBOF = False PrevBOF = True
3 StartBOF = False PrevBOF = True
4 StartBOF = False PrevBOF = True[/blue]

Now my mind is completely blown? ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[strongm]

>Microsoft says the folowing in help for a recordsetclone:
>
>Quote (Microsoft)
>When a new Recordset object is opened, its first record is the current record

The documentation is unfortunately misleading. Specifically, a clone does not open a new recordset

What Microsoft actually say about a recordset clone (with my bolding to show what it is that you have done that starts resulting in True being output from qtest) is

When you create a Recordset with the Clone method, it initially lacks a current record. To make a record current before you use the Recordset clone, you must set the Bookmark property or use one of the Move methods, one of the Find methods, or the Seek method

Now, the first time that form's recordsetclone property is set (done when the form is opened) it uses the recordset clone method, so the above applies. (And whilst not documented, the absolute position is set to 0, which is why MovePrevious moves us to absolute position -1, i.e. BOF, and thus explains the results that you get).

But once we have a current record for the clone the problem does not rearise... subsequently, whilst the form remains open (and the recordsource remains unchanged), the current record of the recordsetclone is whatever you set it to.

And everytime you do:

Set rst = Me.RecordsetClone

all you are doing is getting a pointer to the RecordSetClone, not creating a new object at all. So once you've positioned the current record to BOF, then that's what you get everytime you set rst

> close and set rst to nothing

As mentioned above, rst is just a pointer to recordsetclone. Setting it to nothing is not doing what I suspect you think it is doing (and you cannot close a recordsetclone)

>I'm about to throw a monkey-wrench in this
>Now my mind is completely blown? ...

Why? This is doing exactly what we'd expect it to do. You are opening a new recordset, which sets the current record to the first record (which is not BOF), just as per the documentation that you quoted. You then move to the previous position, which is BOF.

 

[TheAceMan1]

To All ...

So sorry. Having my prior post removed as the following line of code is missing:

[blue]Public Function qTest(ID As Long) As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset(Me.RecordSource, dbOpenDynaset)
   'The recordsource at this point is a simple SQL statement of 3 fields ...
   ' ... the very same I mentioned earlier ... ID as AutoNumber, actName as Text, Amount as Currency
   
   Debug.Print ID & " StartBOF = " & rst.BOF;
   
   If Not rst.BOF Then
      [purple][b]rst.FindFirst "[ID] = " & ID[/b][/purple]
      rst.MovePrevious
      Debug.Print " PrevBOF = " & rst.BOF;
   End If
   
   Debug.Print

   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

... and this corrects the debug printing to the following (note: I'm hitting the button twice)

[blue]1 StartBOF = False PrevBOF = True
2 StartBOF = False PrevBOF = False
3 StartBOF = False PrevBOF = False
4 StartBOF = False PrevBOF = False
1 StartBOF = False PrevBOF = True
2 StartBOF = False PrevBOF = False
3 StartBOF = False PrevBOF = False
4 StartBOF = False PrevBOF = False[/blue]

This restores my confidence in recordsets but we still have the problem with [blue]recordsetclone[/blue]. Im gonna throw this at microsoft and see if I get an answer ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[strongm]

>Im gonna throw this at microsoft

Why? Recordsetclomne is doing exactly what is should do, as far as I can see.

 

[TheAceMan1]

Howdy [blue]strongm[/blue] ...

I must have been editing while you were posting 23 Jul 13 4:41. At any rate your explanation does afford resolution. Many many thanks to you and [blue]MajP[/blue] as well.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]