Deleting Records in Datasheet View

Question

In MS Access (2003), I have a SELECT query consisting of both left and inner joins, displayed in datasheet view as subform. I would like to give users the ability to delete multiple records while in datasheet view. The default delete that occurs when the user chooses "Delete Record" from the context menu of a record does not work; the record disappears from the datasheet view but continues to exist in the underlaying tables and re-appears on refresh of the view.

I attempted to define delete queries in the Form_delete event, using the CodeContextObject object to get the primary key value of the selected record, and then running DELETE FROM tablename WHERE id=keyValue. This works but Access displays a warning "The Microsoft Jet database engine disabled the process because you and another user are attempting to change the data at the same time." The record is then actually deleted from the table, but instead of disappearing from the view, it is displayed as #Deleted. How do I override the default delete procedure in datasheet view?

Here is the SELECT query:

SELECT *
FROM [SELECT *
      FROM ((GeneralTable as G 
             INNER JOIN SpecificTable as S ON G.id = S.id) 
             LEFT JOIN Assigned as A on G.id = A.id)]. AS Q1 
LEFT JOIN People AS P ON Q1.pID = P.pID
WHERE (((Q1.isPrimary) Is Null Or (Q1.isPrimary)=True));

I have changed the names of the tables, but the design is SpecificTable inherits from GeneralTable, with People being assigned to records in SpecificTable in a many-to-many relationship. This query selects all records in SpecificTable, and the person, if any, assigned as primary to the record. When a record is deleted, the record in the People table should not be deleted, but the records in G, S, and A should be deleted.

Please post your SELECT query. I'm guessing that when you delete records directly from the datasheet, that you are deleting rows from the LEFT JOIN'ed table. This would not actually reduce the number of rows you see in your datasheet. — mwolfe02
I have posted it. You're right, it is deleting from the LEFT JOIN'ed table. — Christine
Try rewriting your DELETE statement as DELETE FROM YourTableToDelete WHERE EXISTS (<table expression>); — onedaywhen

HK1 HK1 · Accepted Answer · 2011-11-18T02:41:09

The first answer I'm going to give you is one that I think most experienced Access programmers would probably give you: Avoid trying to facilitate deleting multiple records in a form that displays records using any complicated joins or subqueries. In other words, avoid doing what you're trying to do.

My second suggestion is to try something similar to what you're already trying to do, an SQL Delete query. I'm guessing you're probably using the DoCmd.RunSQL statement. I recommend that instead you use the following code:

CurrentDb.Excecute "DELETE * FROM tblMyTable WHERE ID = " & Me!ID, dbFailOnError
Me.Requery

But I don't think this completely solves the problem of getting conflict error messages.

As a third suggestion, you could put a Delete button on your main form that runs the above code. You'll have to pull the ID value from the subform.

My fourth and final suggestion is similar to my third one, and one that I can only present in theory. You could create your own right-click menu and put a Delete menu item on it that runs the above code. I admit there are a few details about this approach that I don't know how to do like showing that menu at the correct time and location and allowing the user to select and delete multiple rows.

Deleting Records in Datasheet View

1 Answers