This recordset is not updatable 1

[CJSilver]

I have what seems to be a very strange problem. I have a query with the following SQL statement:

 SELECT [team schedule].MO_NUMBER, [team schedule].ITEM, [team schedule].TEAM, [team schedule].SCHED_DATE, [team schedule].ORDER_QTY, [team schedule].WC, [team schedule].[CUSTOMER NUMBER], [team schedule].ESTHRS, ActualQuery.WC, ActualQuery.STAT_FLAG
FROM [team schedule] LEFT JOIN ActualQuery ON ([team schedule].MO_NUMBER=ActualQuery.MO_Number) AND ([team schedule].WC=ActualQuery.WC)
WHERE (((actualquery.WC='1st end' And ActualQuery.STAT_FLAG='FINISHED')=False)) Or (((ActualQuery.WC) Is Null));

I know this query is not updatable, I am not trying to update it. But when I run this query I usually get a message that says Invalid Operation, this recordset is not updatable. But this does not always happen, some times it works, but usually I get the error message. The really strange part (to me at least) is that I have 3 other queries that this query is in and they all work fine. If this query does not run, how can it run inside another query? More to the point, why am I getting the error message at all?

Does anyone have any suggestions on what may be causing this problem?

C. Johnson

http://www.SilveradoCable.com

 

[BNPMike]

We can't see the whole picture as your query seems to contain another one.

It's not impossible that a query containing your query would run OK. Jet assembles all the query bits and treats them as one new big query. It doesn't run the bits one at a time. So if for example your outer query didn't use any of the fields in your query above, Jet would ignore the whole thing and thus not set the recordset to non-updatable.

 

[JonFer]

Try changing your where clause to this:

nz(actualquery.WC,"")<>'1st end' Or nz(ActualQuery.STAT_FLAG,"")<>'FINISHED'

It will not prevent the LEFT JOIN results and will still filter the records.

 

[cmmrfrds]

It makes sense to me that if only fields on the left side of the outer join are in the select list, then the query can be updateable without confusion. When you add fields, in this case, from the right side to the select list then it becomes a guess on the database engine side of what you really want. My guess is that the same query with an equal join would be updateable, or take the fields from the right side out of the select list and it will be updateabale.

The error is probably because the query is being included as a dynaset on a Form which is an updateable recordset.

 

[PHV]

Take a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CJSilver]

Thank you all for responding.

I figured out what the problem is. I am joining ([team schedule].WC and ActualQuery.WC. Since the fields are not exactly equal I wanted to see both fields in the query( so that I could verify that the query was displaying the correct data), this is what was causing the problem. WHen I display just one of the WC fields then the query runs fine. That is also the reason why all the other queries that this query was in did not have any problems. In those queries I was not displaying both WC fields.

C. Johnson

http://www.SilveradoCable.com