EOF to determine empty recordSet doesn't work with RecordSetClone

[ions]

According to the help files

**
If you open a Recordset object containing no records, the BOF and EOF properties are set to True, and the Recordset object's RecordCount property setting is 0. When you open a Recordset object that contains at least one record, the first record is the current record and the BOF and EOF properties are False
**

However when I use the RecordSetClone method this is no the case.

I have the following code.

Dim Solv As DAO.Recordset
Set Solv = Me.Bill_of_Lading_Sub.Form.RecordsetClone
Debug.Print Solv.RecordCount
Debug.Print Solv.EOF
If Solv.EOF Then
        Exit Sub
End If

When I clone a recordset with 2 records EOF remains TRUE. Hence the test for an emptry recordset doesn't work for RecordsetClone. The RecordCount property works however.

Which makes me wonder is the EOF a good test for an empty recordset? I've been using it all along?

 

[WayneRyan]

ions,

I'm using Access 2000 and just gave that a try.

With no records, the clone gave True/True
With 2 records, the clone gave False/False

Wayne

 

[ChrisO]

Ions.

The RecordsetClone is already a copy of the Form’s recordset so why make a copy of a copy?

With Me.Bill_of_Lading_Sub.Form.RecordsetClone
    If (.RecordCount) Then
        [color=green]' Run
        ' your
        ' code
        ' here[/color]
    End If
End With

No variables need creating so none need closing or destroying and no references required.

Regards,
Chris.

 

[ions]

Hi Wayne I just tested it with other forms and even tried just using Me.RecordsetClone instead of Me.Bill_of_Lading_Sub.Form.RecordsetClone and it doesn't work. I am using Access 2002

Hi Chris O

your code uses less memory Nice. But does it execute faster? Assume I am going to reference the form many times (within the with.... end with clause) doing operations such as .findfirst etc...

I've read that if you reference a control more than once you should store the result in a variable and then just use the variable instead of the reference. Although this technique uses more memory it's suppose to execute faster.

If you only reference the form only once then definately use your style. I am not sure if your technique references the form more than once or if Access references the form every time you use the . operator.

Thanks for your responses.

 

[gonkowonko.com]

with a DAO recordset easiet way to see if recordset is empty is do a

.movelast
.movefirst

then do a if .recordcount = 0 then BANG!

Access doesnt seem to know how many records in a recordset until its gone to the end. Ive used this plenty of times and it doesnt seem to be too memory intensive but i might be wrong

 

[ChrisO]

G’day Ions.

Sometimes what we read is either misleading, misinterpreted or just plain wrong.

For something like the difference between opening a recordset of the clone or using the clone directly, memory size would be of little importance. Strictly speaking, the clone is not a copy of the data in the underlying recordset nor is the opened recordset a copy of the data in the clone. But some things like absolute position pointers are copied.

Speed is also a far over rated subject. If someone has to ask the question ‘But does it execute faster?’ means they have not noticed a difference and have not tested it. Also, when dealing with Forms for instance, there seems to be little gained in trying to shave microseconds or even milliseconds of some result that needs to be displayed. At 80Hz the screen will take 12 milliseconds to display the result and yet we see people claiming to have dropped the processing time from 6 to say 3 milliseconds???

Neither memory size or speed is a concern in the code I posted, it’s simply done that way for reliability. We do not need to know the true record count in order to know if there are any records, all we need to know is if there are zero records. For example, doing a MoveLast, MoveFirst before checking if there are records will raise an error if there are no records.

So I think memory size and speed are far overrated subjects while reliability is underrated.

Regards,
Chris.

 

[ions]

Thanks for everyone's input.

Still the fact remains my EOF is broken using RecordClone and that is the recommend way of determing empty recordsets. Any ideas? Chris your way is probably 100% full proof too because you just test if RecordCount = 0 (see problem with RecordCount below) I just want to resolve this EOF because I have used this test for all my previous code.


Just some research I did on determing empty recordsets and RecordCount.

****************************************************
Help file says this.

"The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed."

Access 2000 Power Programming by F. Scott Barker states that "It has been long understood that the RecordCount property wasn't going to be necessarily accurate without first perfoming the MoveLast method of the recordset." P. 159

However, probably the statement if recSet.recordCount = 0 is accurate for determining empty recordset.

F. Scott Barker also states that "To see wheter your recordset has any records in it, use the BOF and EOF properties."

Help file

**
If you open a Recordset object containing no records, the BOF and EOF properties are set to True, and the Recordset object's RecordCount property setting is 0. When you open a Recordset object that contains at least one record, the first record is the current record and the BOF and EOF properties are False
**

 

[ChrisO]

EOF is not broken for DAO recordsets, Wayne has already said that.

I’m saying, why create a DAO recordset of the clone?…the clone is already a DAO recordset.

You seem to be having a problem somewhere so can you upload a little sample for us to test?

Regards,
Chris.

 

[ChrisO]

The only way I can simulate what you are getting is by first moving the clone to EOF and then opening a DAO recordset of the clone. If we do it that way, the EOF for the just opened DAO recordset is True.

The DAO recordset gets a valid copy of the clone absolute position…EOF.

 

[ChrisO]

If your form is set to Data Entry = Yes then the clone will be at EOF.

 

[ions]

It's broken on my Machine Chris. I am using Access 2002

I am testing it using your code style

With Forms![Clean Solvent].SolvItemSub.Form.RecordsetClone
Debug.Print .RecordCount
Debug.Print .EOF
End With

It always works on the first try. But then when you change the contents of the sub form and run the same code again the RecordCount property is correct but the EOF property remains what ever it was from the initial trial.

So it works on the first test. Then I change the Parent form link field, the sub form gets requeried, I run the test and the EOF doesn't change.

 

[ions]

Data Entry is set to NO

 

[ChrisO]

The absolute position marker for the clone is static so it will stay where you leave it.

How about just uploading a little sample for us to test?

 

[ions]

Demo File

For all my EOF's I get False. Should get True for the third and forth records

See attached file

 

[ChrisO]

Well maybe a way to look at it is that a recordset (including the clone of the recordset) bound to a form moves to a temporary record when there are no records.

It would also appear that the EOF is not set when that temporary record is created.
(Something I’m glad I never put money on.)

However, we can still force the EOF on the clone… see attachment.

Moral of the story: - to determine if the clone has any records don’t use EOF but rather use (.RecordCount) and use the clone directly and not a recordset of the clone.

If you wish to run through the clone then use EOF as the end marker for the loop.
If you wish to run through the clone more than once then do a .MoveFirst on the clone but do it after a test for clone records.

With Me.RecordsetClone
    If (.RecordCount) Then
       .MoveFirst
       Do Until .EOF
           [color=green]' Run
           ' your
           ' code[/color]
           .MoveNext
        Loop
    End If
End With

BTW… when we use (.RecordCount) as an argument in the If statement it will be coerced into True or False. So a (.RecordCount) of zero will be regarded as False because it is equal to zero and any other value will be regarded as True even though that value is not equal to True.

So: -
If (.RecordCount) Then……works but
If (.RecordCount = True) Then…….would not. ( the .RecordCount can not be equal to –1)

Hope that helps.
(And now back to some serious programming…just got a new TV with remote control. )

Regards,
Chris.