How to Build a Custom Record Delete

[whdyck]

I'm using Access 2003.

I'd like to override the default behaviour for deleting records in a form.

Specifically, I want to build my own custom delete procedure so that when the user presses the Del button, my code fires to complete the deletion of the selected record(s). In order to do that, I'd set Allow Deletions = No for that form. I'd also want to code the KeyDown event for the Del key so that if record(s) are selected, my custom delete code fires, else the default behavior for the Del key happens.

Is this possible? I'm primarily interested in how I might code the KeyDown event.

Thanks.

Wayne

 

[RainLover]

If you set Allow Deletions = No then you can't delete. So why do that?

Instead of using the Delete key on the Keyboard create your own Button and place your Delete code behind that. Use the On Click Event.

 

[whdyck]

Instead of using the Delete key on the Keyboard create your own Button and place your Delete code behind that. Use the On Click Event.

I should have mentioned that I'd like this code to work within Datasheet view, where the user could otherwise select and delete multiple records. However, I just noticed that KeyDown does not seem to work in this view, nor does a CommandButton appear.

So it appears that what I want to do is impossible. I suppose I could make my form a subform and put the CommandButton on the parent, but that's not gonna happen in my case.

Wayne

 

[RainLover]

Wayne

That explains a little bit more. You could have a check box where you can select One, Many or All records. Then hit the Command button in the Header.

Also if you use a check box you can hide the records rather than delete them. This is a good idea for when mistakes happen. You as Admin could go into the table and uncheck that/those records.

Does this help.

 

[whdyck]

You could have a check box where you can select One, Many or All records. Then hit the Command button in the Header.

If I add a command button to the Header, that command button does not seem to appear in Datasheet view.

Wayne

 

[RainLover]

I believe you will have to use the Datasheet as a Sub Form.

Place the Command Button on the Main Form.

 

[missinglinq]

You can do this by trapping the native <Delete> Key using the Form_KeyDown event.

As has been said, you need to leave AllowDeletions set to Yes, then

1. In Form Design View, with the Form itself selected, go to Properties - Events
2. Scroll down to the last Property, KeyPreview, and set it to Yes
3. Then use the code below

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = 46 Then
   MsgBox "Delete Pressed!"
   KeyCode = 0
  End If
End Sub

Simply replace the line

MsgBox "Delete Pressed!"

with the code you want executed when the native <Delete> Key is pressed.

Note that the Form_KeyDown event won't fire unless the KeyPreview Property is set to Yes.

Linq ;0)>

 

[whdyck]

You can do this by trapping the native <Delete> Key using the Form_KeyDown event.

That's beautiful. It works!

If I add the bolded code:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyDelete Then
[B]       If Me.SelHeight > 0 Then[/B]
            MsgBox "Delete Records!"
            KeyCode = 0
[B]       End If[/B]
    End If
End Sub

it comes very close to doing what I want: If I select one or more records and press Del, I see "Delete Records!"; if I have my cursor in a field, it deletes the char at the cursor, just as I want. (And it works in Datasheet view, too, much to my surprise.)

I say "very close" because it also displays "Delete Records!" if I merely select an entire field of a given record rather than the entire record. What is the most efficient way to identify whether one or more entire records are selected? I'm thinking I might need to use SelWidth, too?

Thanks!

Wayne

 

[missinglinq]

To delete mulitpl eRecords, you'd need to follow Rainman's advice

...You could have a check box where you can select One, Many or All records...

Then open up a RecordSet, loop through it, delete all of the Records where the Checkbox Field is ticked, then untick it.

Alternately, you could execute a SQL Delete Statement to do the same thing.

You might also consider his other suggestion:

...you can hide the records rather than delete them....

Many of use never actually Delete Records; rather we mark then 'inactive,' using a Checkbox, and simply Requery the RecordSource, only displaying Records that are not marked. Among other things, it makes recovery of accidentally 'deleted' Records easier!

Linq ;0)>

 

[whdyck]

Alternately, you could execute a SQL Delete Statement to do the same thing.

This is my preferred technique. I'll loop through the selected records to build my SQL statement, then issue the SQL Delete Statement and requery.

I'm working on an existing form, and I need to make it behave as closely to before as possible, so I need to actually delete the records.

I'm thinking that I can test for full record selections with the following code:

If Me.SelHeight > 0 And Me.SelWidth >= Me.Recordset.Fields.Count Then

Or is there a better way?

Thanks.

Wayne

 

[whdyck]

You can do this by trapping the native <Delete> Key using the Form_KeyDown event.



As has been said, you need to leave AllowDeletions set to Yes, then

1. In Form Design View, with the Form itself selected, go to Properties - Events
2. Scroll down to the last Property, KeyPreview, and set it to Yes
3. Then use the code below

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

[B]If KeyCode = 46 Then[/B]
[B]MsgBox "Delete Pressed!"[/B]
[B]KeyCode = 0[/B]
[B]End If[/B]
[B]End Sub[/B]

Simply replace the line

MsgBox "Delete Pressed!"

with the code you want executed when the native <Delete> Key is pressed.

If I thus bypass Access's default record-deletion process when pressing Del in this way, is there also a way to turn off the menu item Edit | Delete Record?

I don't want the user to be able to delete records using the Access default process, including the menus, unless I can somehow substitute my custom Delete code for the default menu behaviour.

Thanks.

Wayne

 

[whdyck]

If I thus bypass Access's default record-deletion process when pressing Del in this way, is there also a way to turn off the menu item Edit | Delete Record?

Actually, if I just set Allow Deletions = No for the form, my custom routines based on pressing Del still seem to work, and the menu selections are disabled.

Wayne

 

[RainLover]

Do you realise that you can disable the delete key if you do not show the "Record Selectors"

This covers the Sub Form. For the Main form go to your relationships and make sure Cascade Delete is not checked. As a matter of fact I don't click either Delete or Update on any Relationship.