periodically this function fails with the above error
when executing 'If (Not (frm.RecordsetClone.EOF)) Then'

since assigning the recordsource didn't generate an error
I presume that the data (4 records in the latest case) exists
and that it would be displayed on the continuous form

so what is wrong with 'recordsetclone' ? a timing issue ?
a coding error ?


Option Compare Database
Option Explicit

Public Sub newRecordsource(frm As Form, strsql As String)
dim lngCount as long

On Error Resume Next
If (frm.Dirty) Then
frm.Dirty = False
End If

On Error GoTo fErr
If (frm.RecordSource <> strsql) Then
frm.RecordSource = strsql
On Error GoTo fErr
If (Not (frm.RecordsetClone.EOF)) Then
frm.RecordsetClone.MoveLast
lngCount = frm.RecordsetClone.RecordCount
frm.RecordsetClone.MoveFirst
End If
Else
frm.Requery
End If

fExit:
On Error Resume Next
Exit Sub

fErr:
errorLog "newRecordsource " & frm.name & " " & strsql & " " & Erl
Resume fExit
Resume Next
End Sub

"Roger" <lesperancer (AT) natpro (DOT) com> wrote

I'm surprised it work at all. Try setting a recordset variable to
RecordsetClone. Also you may need to do a frm.Requery after you set the
record source and before the recordsetclone.

Public Sub newRecordsource(frm As Form, strsql As String)
dim lngCount as long
dim rsClone as DAO.Recordset

On Error Resume Next
If (frm.Dirty) Then
frm.Dirty = False
End If

On Error GoTo fErr

If (frm.RecordSource <> strsql) Then
frm.RecordSource = strsql
On Error GoTo fErr

set rsClone = frm.RecordsetClone

If (Not (rsClone .EOF)) Then
rsClone .MoveLast
lngCount = rsClone.RecordCount
rsClone .MoveFirst
End If
set rsClone = Nothing
Else
frm.Requery
End If

fExit:
On Error Resume Next
Exit Sub

fErr:
errorLog "newRecordsource " & frm.name & " " & strsql & " " & Erl
Resume fExit
Resume Next
End Sub

You could also do a dcount on strSql instead of recordsetclone.

On Sep 2, 6:28*am, "ron paii" <n... (AT) nospam (DOT) com> wrote:
1) recordsetClone is a dao.recordset so I don't understand why it
would not fail with rsClone
isn't an apple an apple ?

2) will using rsClone.moveLast retrieve all the records from sqlserver
2005 like recordsetClone
which is the intent of the function, instead of trickling the
data, causing performance/deadlock
issues

"Roger" <lesperancer (AT) natpro (DOT) com> wrote

Normally you want to assign it to a variable of type recordset, which you
used to edit or view the recordset. Your function creates and destroys a new
object with each call to RecordsetClone .

MoveLast will force the server to send all the records to you application. A
SQL count * call would be much move efficient, allowing the server to do the
work and returning only the count. Filling the recordset will cause a
performance issue as opposed of only retrieving the records as needed.
Deadlock can only occur if a record is changed.

"ron paii" <none (AT) nospam (DOT) com> wrote in
news:j3qi51$opb$1 (AT) dont-email (DOT) me:

This makes me crazy. There is simply NO REASON to set a variable
pointing to the RecordsetClone -- just use a WITH structure and save
the extra work:

If (frm.RecordSource <> strsql) Then
frm.RecordSource = strsql
On Error GoTo fErr

With frm.RecordsetClone
If (Not (.EOF)) Then
.MoveLast
lngCount = .RecordCount
.MoveFirst
End If
End With
Else
frm.Requery
End If

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"ron paii" <none (AT) nospam (DOT) com> wrote in
news:j3r1qk$bnl$1 (AT) dont-email (DOT) me:

That's not correct at all. The form's RecordsetClone is a recordset
that already exists as soon as the form has a recordset. You can
clone a Recordset with the Clone command, but there's no reason to
do so -- you already have a recordset intialized and available to
you to work with.

There is no advantage to doing this with a recordset that ALREADY
EXISTS. It is only when you're opening a new recordset that you must
assign it to a variable to insure that it doesn't go out of scope.

This is COMPLETELY FALSE. RecordsetClone is an OBJECT, not a METHOD.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"David-W-Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote

Thanks David
I always assumed that RecordSetClone created a new object, being as I could
set multiple references to it and move the cursors independently.

"David-W-Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote

That's true, but I normally do more with the clone. So I find it easer to
use a variable.

"ron paii" <none (AT) nospam (DOT) com> wrote in
news:j452dt$lqm$1 (AT) dont-email (DOT) me:

What are you doing that makes it easier? What exactly are you doing
with the form's RecordsetClone?

That is, as opposed to the Clone of any old Recordset, which
definitely requires a variable assignment to be used?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"ron paii" <none (AT) nospam (DOT) com> wrote in
news:j4523k$joq$1 (AT) dont-email (DOT) me:

Hmm. Good point. I'll have to meditate on that one.

But it's very clearly not a new object -- it exists already, since
you don't have to initialize it in order to use it.

Of course, maybe when you're assigning it to a variable you are
getting a copy instead of the original object? I'll have to think a
bit on how to test that out...

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/