Use of Keyboard Delete Key

[gray]

Hi All

WinXPPro
Access 2002 SP3


I have a Mainform with a Subform. I have built my own delete routine using a custom delete button on my main form and placed Cancel = vbCancel in the subform's Form-Delete event. However, for consistency of use, I'd like to call my own delete routine from the normal keyboard Delete key.

The problem I have is that my subform records are built from an 'aggregate' query... i.e.

SELECT Tgt_Tbl.* FROM System_Settings_History_Notes AS Tgt_Tbl ,(SELECT max(Version) as Max_Version,ID FROM System_Settings_History_Notes GROUP BY ID) AS Max_Results WHERE Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version AND Deleted <> Yes AND Tgt_TBL.Record_Table_Name = 'Cost_Types' ORDER BY Tgt_Tbl.List_Order ASC

From research, I believe, this effectively 'disables' the Delete key as, when pressed, I get the:

"Records Not Deleted. Data is Read-Only" prompt (allowdeletions and permissions factors check out OK).

I thought I would be able to trap an event before Access produces the Read-Only prompt and then action my own delete routine but neither the Form-Delete or Form-Keydown events seem to fire??

Does anyone know how I might be able to get around the Read-Only prompt and to action my delete please?

Thanks once again....

 

[WIS]

Hi All

WinXPPro
Access 2002 SP3


I have a Mainform with a Subform. I have built my own delete routine using a custom delete button on my main form and placed Cancel = vbCancel in the subform's Form-Delete event. However, for consistency of use, I'd like to call my own delete routine from the normal keyboard Delete key.

The problem I have is that my subform records are built from an 'aggregate' query... i.e.

SELECT Tgt_Tbl.* FROM System_Settings_History_Notes AS Tgt_Tbl ,(SELECT max(Version) as Max_Version,ID FROM System_Settings_History_Notes GROUP BY ID) AS Max_Results WHERE Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version AND Deleted <> Yes AND Tgt_TBL.Record_Table_Name = 'Cost_Types' ORDER BY Tgt_Tbl.List_Order ASC

From research, I believe, this effectively 'disables' the Delete key as, when pressed, I get the:

"Records Not Deleted. Data is Read-Only" prompt (allowdeletions and permissions factors check out OK).

I thought I would be able to trap an event before Access produces the Read-Only prompt and then action my own delete routine but neither the Form-Delete or Form-Keydown events seem to fire??

Does anyone know how I might be able to get around the Read-Only prompt and to action my delete please?

Thanks once again....

By the looks of yr qry, it would not be updateable and therefore is RO.

Run the qry on its own and look at the bottom of the recordset - if the * is grayed out then it is not updateable, hence RO.

 

[gray]

Thanks WIS... I ran the query as you advised and ,indeed, it is greyed out... so that's conclusive about the recordset being RO...

Is there any way you know of that I can trap the Delete key being pressed before Access detects the recordset being Read-Only? I could then call my own delete...

cheers

 

[WIS]

Thanks WIS... I ran the query as you advised and ,indeed, it is greyed out... so that's conclusive about the recordset being RO...

Is there any way you know of that I can trap the Delete key being pressed before Access detects the recordset being Read-Only? I could then call my own delete...

cheers

The ways that come immediately to mind are:

Run an updateable qry as the Recordsource for the subfrm - probably not possible.

If you can identify the record(s) that need deleting, run VBA code to do it.

Can you trap the Delete key with an "OnKey" event?

 

[gray]

Hi WIS

Thanks for your ideas WIS... much appreciated!

Actually, it's a custom VBA delete routine that I'm trying to fire but I'm unable to call it from the keyboard Delete key because the Form-Delete event is not fired (due to the recordset being Read-Only).

I've tried with various Key events both on the main and subforms but they don't seem to get fired under these circumstances either... I've a feeling I have to temporarily suspend cascading deletes in order to work around the Read-Only recordset? But I'm not quite sure how to do that ... I'm guessing wildly!

Any further suggestions WIS / anyone please?

Thanks

 

[WIS]

Hi WIS

Thanks for your ideas WIS... much appreciated!

Actually, it's a custom VBA delete routine that I'm trying to fire but I'm unable to call it from the keyboard Delete key because the Form-Delete event is not fired (due to the recordset being Read-Only).

I've tried with various Key events both on the main and subforms but they don't seem to get fired under these circumstances either... I've a feeling I have to temporarily suspend cascading deletes in order to work around the Read-Only recordset? But I'm not quite sure how to do that ... I'm guessing wildly!

Any further suggestions WIS / anyone please?

Thanks

Cascading deletes won't solve your problem. The qry will still be RO as you are using an aggregate.

Are you prepared to put a Delete cmd button on the form - I do this regularly and have it coloured 255 (Red).

I then run VBA code to handle the deletion.

 

[gray]

Hi WIS!

In fact, I started with a custom Delete button (on my main form) which happliy performs delete requests via VBA... (Note: I use the SelRecord / SelRestore method for multiple datasheet selections as suggested on various sites - very handy, if anyone out there is struggling!) But to maintain consistency of user-experience, I wanted to invoke that VBA from the Keyboard Delete Key too. I managed to get this working but then I had to implement an aggregate query to populate my subform... that's when things went all quiet .

Since I can't find an event being fired, I presume there must be some 'hidden, internal' event actioned in order for Access to reject use of the Keyboard Delete key. If this is the case, then I have probably hit a brick wall.... nothing new there!

Or I may be able to effect population of my subform is some other way... sadly, it took me days to suss the present query.

Many thanks for your time... always appreciated.

Cheers

 

[WIS]

Hi WIS!
Since I can't find an event being fired, I presume there must be some 'hidden, internal' event actioned in order for Access to reject use of the Keyboard Delete key. .

There is a book "Access97 Developer's Handbook" by Litwin, Getz, Gilbert which has a CD. This CD contains many sample db's one of which I'm sure has a db which shows all events firing and the order in which they fire. I have found this invaluable because quite often, events occur/don't occur as you would expect.

 

[gray]

Hi

I've discovered there is another event I might be able to manipulate called :-

object_KeyPress(KeyAscii As Integer)

So presumably it works at the object level rather than form. The $64,000 question is, where do I find that event in Access so I can add code to it? Any ideas?

Thanks