access2007 - 3420 - Object invalid or no longer set.

[Roger]

strsql is a valid string returning one record, periodically the
following fails on the 'if...' statement with error\
3420 - Object invalid or no longer set.

With Me
.RecordSource = strSql
If (Not (.RecordsetClone.EOF)) Then
.RecordsetClone.MoveLast
endif
end with

what could cause this ?
should I try assigning 'recordsetClone' to a dao.recordset ?

[Roger]

I added more diagnostics, logging the ERL when the problem occurs
10
Me.RecordSource = "SELECT * FROM qrySelectContact WHERE 1 = 2"
DoEvents

20
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
70

the log shows erl = 30, so it failed with this statement
Me.RecordSource = strSql

yet the earlier statement
Me.RecordSource = "SELECT * FROM qrySelectContact WHERE 1 = 2"

which returns no data doesn't fail

I also logged 'strsql' and I just tried a query with its value, and it
works fine, returning one row

so why is 'me' no longer valid ?

and if I loop this chunk of VBA 8295 times I get a different error
There isn't enough memory to perform this operation. Close unneeded
programs and try the operation again

so obviously something isn't being cleaned up, and since my function
only references 'me', I don't know what I can do ?

[imb]

Hi Roger,

What is the function of so many DoEvents?

Imb.

[Salad]

Roger wrote:

> strSql = "SELECT *" & _
> " FROM qrySelectContact" & _
> " WHERE " & strSql & _
> " ORDER BY baanCustomer, baanSupplier, baanPostalId,

Any idea what the value of strSQL is in your Where statement?

[Roger]

Yes, it too is logged, and as indicated, when I use the contents of
strsql as a query, it returns one record
so I know that strsql is valid

[Roger]

no reason other than to relinquish cpu for a brief moments, removing
them doesn't solve the problem

[imb]

Hi Roger,

So, strSQL seems allright.
But what does qrySelectContact in case of the problem. It is a query,
isn't it?

Imb.

[Salad]

Roger wrote:

I don't know what a ERL is. I don't have the interest to google it either.

After your SQL assignment statement I'd do a
Debug.Print strSQL

Then I'd copy the line from the Immediate window and paste it into a new
query and run it. If it executes correctly then take a .44 to your
computer and put it out of its misery.

[Roger]

qrySelectContact is a sql server view, set up as a linked table

[Roger]

> computer and put it out of its misery.- Hide quoted text -
>
> - Show quoted text -

ERL contains the last line number, in this case 20
and I logged strSql, and it runs fine when pasted into a new query,
returning one record

so assigning me.recordsource is failing, that's why I temporarily
added

Me.RecordSource = "SELECT * FROM qrySelectContact WHERE 1 = 2"

which works

I don't understand why two valid strings can't both be assigned to
me.recordsource without occasionally creating an error

[imb]

>
> I don't understand why two valid strings can't both be assigned to

> me.recordsource without occasionally creating an error- Hide quoted text -
>
>

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.

[Roger]

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

[imb]

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.

[Roger]

> Imb.- Hide quoted text -

>
> - Show quoted text -

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 ?

[imb]

>
> 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 ?- Hide quoted text -

>
> - Show quoted text -

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.

[Roger]

> Imb.- Hide quoted text -

>
> - Show quoted text -

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