Folks,

I have a form in datasheet view that is based on a query. The query shows all the records in table A that don't have a child record in Table B. The query is a simple right outer join showing columns from table A and a criteria on the id field from table B that is set to null. This shows me all the records in A that don’t have a child in B. The objective is to allow the user to delete some or all of the displayed records. The interesting thing is that if a record is deleted from this view I get the "You're going to delete one record message" and the record goes away in the datasheet view, but if the form is refreshed the record reappears. It would appear that the record is removed from the forms recordset clone but not the actual recordset. I could swear that I have done this many times in the past and it always resulted in the record being deleted. Any idea what is going on?

I opened the forms record source query and get the same behavior there.

You can see this if you go into Northwind and add the following query.

SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipName, Orders.ShipAddress, [Order Details].OrderID FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE ((([Order Details].OrderID) Is Null));

Assuming there are some orders that don't have any details you will see them. Delete them from the query datasheet view, then refresh/requery. The supposedly deleted records are there again. I am sure there is a good reason you can't delete records this way, but I learned something. I reconstructed the query to use a subquery instead of the outer join and it works as expected. It just runs much more slowly.

The interesting thing here is that Access says it deleted the records, and they show deleted from the datasheet, but they are not removed from the underlying tables. As I said, I corrected the functionality using a subquery, but it is slow for tables with many records. I'd like to understand why the above query does not allow deletes, but acts like it does.

Looking forward to enlightenment.

Doug

Here is Allen Browne's summary of what can make a query read-only. See if it helps you get to the bottom of your problem.

Looked at the list. The only item that might apply is "The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.", but it does not seem to be the root cause. The example in my email that used Northwind did use the primary key and child key fields. This would not really be an issue if you got an error message when deleting records from the query in datasheet view, but you don't. You get the normal Access warning of "You are going to delete # Records. Do you want to proceed?" or something like that. The records then are deleted from the datasheet, but not from the underlying tables.

Looking for enlightenment here.

Upload the database so we can look. It must be compressed into a zip file to upload.

Thanks.

Just copy the sql from my initial message into a copy of Northwind and you will see, assuming there are orders without order details.

I don't have a copy of Northwind to try to find your problem in YOUR database, sorry. If you'd like to provide a sample of YOUR database for us to look at, that would be great, though.

Thanks but this is a production project, that I can not send out. Northwind is free and available from Microsoft. Up until Access 2010 I think it was installed if you did a full install of Access. It is in the Samples directory. I have two solutions to the issue at hand I was just searching for an explanation of the behavior. The desired action can be obtained using a subquery in place of the outer join, or by setting the query property to Dyanaset(Inconsistent updates).

I still would like to know why Access reports records as being deleted when in actuality they are not.

According to MSDN,

In a Dynaset type recordset, you can edit bound controls based on a single table or tables with a one-to-one relationship. For controls bound to fields based on tables with a one-to-many relationship, you can't edit data from the join field on the "one" side of the relationship unless cascade update is enabled between the tables. For more information, see the topic that explains when you can update records from a query.
In a Dynaset type recordset with Inconsistent Updates, all tables and controls bound to their fields can be edited.
In a Snapshot type recordset no tables or the controls bound to their fields can be edited.

Thank you.

It's always good to be prodded into digging a little deeper. Thank you.