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 ?

On Dec 13 2010, 3:02*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
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 ?

Hi Roger,

What is the function of so many DoEvents?

Imb.


On Jan 31, 7:18*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

Roger wrote:

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

On Jan 31, 11:51*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
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

On Jan 31, 11:18*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
no reason other than to relinquish cpu for a brief moments, removing
them doesn't solve the problem

On Jan 31, 9:10*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Hi Roger,

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


Imb.

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.

On Jan 31, 2:08*pm, imb <im... (AT) onsmail (DOT) nl> wrote:
qrySelectContact is a sql server view, set up as a linked table

On Jan 31, 2:55*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
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