Doug Murphy
dw-murphy at cox.net
Thu Mar 1 14:38:39 CST 2012
Thanks Mark, Makes me feel better that it isn't just me. I thought this might be an Access 2010 "feature" so ran it in 2003 and got the same result. Interesting that is looks like the delete is taking place and you get the standard warning, but it really doesn't happen in the underlying tables. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Thursday, March 01, 2012 12:15 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Question about deleteing from datasheet view Doug, I just recreated your situation(just in query, not using forms). I have a query based on the SQL below...execute, returns records, I try to delete, get a message 'Are you sure', I say yes...records go away...when I run the same SQL again...the records are back??? I think I remember in older versions (not sure which) that you would get a "query not updatable" error in cases like this...but why it is acting this way now...I have no idea. Mark M > From: dw-murphy at cox.net > To: accessd at databaseadvisors.com > Date: Thu, 1 Mar 2012 11:53:18 -0800 > Subject: Re: [AccessD] Question about deleteing from datasheet view > > Hello Jack, > > Thanks for your input. The problem here is not really with the way we > refresh/requery the form. It is the fact that records that appeared to > have been deleted really aren't. 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. > > Doug > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack > drawbridge > Sent: Thursday, March 01, 2012 11:16 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Question about deleteing from datasheet view > > Doug, > I think this may be relevant > > The Requery method updates the data underlying a form or control to > reflect records that are new to or have been deleted from the record > source since it was last requeried. > > The Refresh method shows only changes that have been made to the > current set of records; it doesn't reflect new records or deleted > records in the record source. > > The Repaint method simply repaints the specified form and its controls. > > Jack > > On Thu, Mar 1, 2012 at 11:35 AM, Doug Murphy <dw-murphy at cox.net> wrote: > > > 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. > > > > Thanks in advance. > > > > Doug > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com