[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