asked on

Delete selected records with a button in Datasheet view

I have a form called frmProducts with a subform on it called subProducts.

The subform is in datasheet view.

I have a 'Delete' button on my form which when pressed I want the SELECTED records in the subform deleted.

The subform has it's RecordSelectors visible - this is how the user can select mutliple records.

In the button click event I have:

subProducts.SetFocus
DoCmd.RunCommand acCmdDeleteRows

The problem is that as soon as the user Clicks the 'Delete' button the focus moves from the subform which causes the selection of records in the subform to change. The selection changes to become only the current record.

Is there a way to stop this behaviour?

Currently the only way the user can delete a range of records is by right mouse clicking on the selection to bring up the 'context sensitive' menu (also know as pop-up menu) and selecting delete from here. Obviously such menus do not cause a change of focus to occur.

The use of context sensitive menus is far from intuitive for most users however - so this is far from idea as a solution.

Just clicking a button is much better.

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rogerhammond

or, you could store the row ids manually and then use your delete button to call a method in your subform which then reselects your rows and runs the delete command mentioned.

SE081398

May I offer a thought. I've done this another way. put a boolean field in the table supporting the subform. on the sub form there is the field "Delete" with a check box. allow the user to select all the records he wants by checking the delete check. then in the command btn "delete" use a recordset

SQL = "DELETE yourtable.* FROM yourtable WHERE (deletecheck.delete)=True"
CurrentDb.Execute SQL
Me![sub frm].Requery

then all the selected records will disappear from the sub form. It's as easy as that.

AlMounkez Consultant

put the delete record into the details window

nico5038

Hi SE,

I guess the thought is to update a "mouse selection" in one go.
But you could ofcourse use the mouseup to set such a boolean field for a selected range. It will even allow you to do multiple selects before pressing the update/delete button that uses the field in the WHERE clause.

Nic;o)

holgrave

ASKER

Hi guys - sorry for the delay.

This is what I did in the end - I think it is the best solution, but if anyone wants to improve on it!

Module
-------
Public lgSelTop As Long
Public lgSelHeight As Long

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

subForm
-------
Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
lgSelTop = Me.SelTop
lgSelHeight = Me.SelHeight
End Sub

Form
-----
Private Sub bttnDelete_Click()
Dim intResponse As Integer
Dim lgCount As Long

On Error GoTo ErrorHandler

intResponse = MsgBox("Are you sure that you wish to delete " & vbCr & "the selected product(s)?", vbInformation Or vbYesNo Or vbDefaultButton2, "Confirm Product Deletion")
If intResponse = vbNo Then Exit Sub

subProducts.SetFocus
subProducts.Form.SelTop = lgSelTop
subProducts.Form.SelHeight = lgSelHeight
subProducts.Form.SelWidth = 3
DoEvents
Sleep 500

For lgCount = 1 To lgSelHeight
DoCmd.RunCommand acCmdDeleteRecord
DoEvents
subProducts.Form.SelWidth = 3
Sleep 200
Next

Me.Requery

Exit Sub

ErrorHandler:
MsgBox Err.NUMBER & " " & Err.Description

End Sub

nico5038

Small optimization could be to "drop" intResponse and code:
if msgbox("Msg",vbYesNo) = vbNo then exit sub

Success with the application !

Nic;o)