Hi Roger,

Can the form's OnActivate event cause the problems?
Whenever you assign a value to Me.Recordsource the OnActivate event is
triggered.

Imb.

On Feb 1, 6:35*am, imb <im... (AT) onsmail (DOT) nl> wrote:
the onActivate event consists of this
me.requery
docmd.maximize

this is a continuous form, where people can drill down to a specific
record and make a change
so the requery is meant to ensure that any changes are updated on the
continuous form
I don't know of any other way to accomplish that

On Feb 1, 6:31*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

Hi Roger,

Instead of a Me.Requery in the OnActivate event you can also use
Me.Dirty = False in the OnDeactivate event, to ensure that the last
not-yet-saved record will be saved without leaving the record.

Possibly, this Me.Requery is (sometimes) in conflict with the
execution of the query after the assignment to Me.Recordsource.
For the rest, I do not see the solution for your problem.

Imb.

On Feb 1, 3:09*pm, imb <im... (AT) onsmail (DOT) nl> wrote:
I'm bypassing the OnActivate event and for over a week no errors were
logged
today an error was logged after line 30, indicating that
me.recordsetClone is the invalid object

strSql = "SELECT *" & _
" FROM qrySelectContact" & _
" WHERE " & strSql & _
" ORDER BY baanCustomer, baanSupplier, baanPostalId,
baanDeliveryId"
Me.RecordSource = strSql
DoEvents
30

If (Not (Me.RecordsetClone.EOF)) Then
40
Me.RecordsetClone.MoveLast
DoEvents
50
Me.RecordsetClone.MoveFirst
DoEvents
60
End If

strsql is also logged and when pasted into a query, runs fine and
returns two records

since I'm using access2007, this seems to work

dim rs as DAO.recordset

strSql = "SELECT *" & _
" FROM qrySelectContact" & _
" WHERE " & strSql & _
" ORDER BY baanCustomer, baanSupplier, baanPostalId,
baanDeliveryId"
Set rs = currentdb.openRecordset(strSql)
If (Not (rs.EOF)) Then
rs.MoveLast
DoEvents
rs.MoveFirst
End If


Set Me.Recordset = rs

is there any issues with setting a form's recordset in this fashion ?

Hi Roger,

Though Me.Recordset is also available in A2003, I do not use that.
The reason why? Or you “borrow” your Recordset from another form, with
unexpected side effects when the original Recordset is changed, or –
when it is specific to that one form – you declare an unneccessary
recordset.

I think your problem is a timing problem. Bypassing the OnActiviate
events gave already relief, but apparently not enough.
In that light I do not think you solve the problem with an additional
recordset and that assigning to Me.Recordset. Nor with more DoEvents:
some day you will meet the problem again.

I guess the problem is with the RecordsetClone.
From the rest of your code I conclude that you want to find the total
number of records in your set (RecordsetClone.MoveLast,
RecordsetClone.MoveFirst).
Do you really need that at this moment of loading, or could that be
derived later on through a command button on the form?
What happened when you skip those lines?

The query is apparently “heavy” in terms of number of records or in
the use of many functions (such as Dlookup) in the query.
Can you narrow down the query to limit the number of records, or
simplfy the query by leaving off some functions?

My philosophy is to use “thin” queries, that is retrieve only those
records that are relevant on this moment for this purpose. It is fast,
and I do not need to manually (or optically) distinguish between
records, because that is already done by the computer.


Imb.

On Feb 16, 8:11*am, imb <im... (AT) onsmail (DOT) nl> wrote:
the query can return 0 to N records depending on the user's search
criteria, in this case it returned 2 records
the query 'qrySelectContact' is a sql server view, so from an access
view point it is pretty thin

the reason for the moveLast/moveFirst is to solve a bug with
access2007 and sql server2005 when the number of records is large,
something about controlling network bandwidth by sending x records /
sec, which creates sql server locking issues

In the past, I opened a case with microsoft support about this
problem, and they confirmed it to be a bug to be fixed in access2010,
and this is the workaround, causes all records to be received from sql
service

I will try the recordset approach to see what happens