From: Jim Devenish on
I have a continuous form. I can select a number of records by clicking
on a record selector and then shift-clicking on another one. The
whole group from the first to the second is selected. If I then
select 'Delete record' from the Edit menu, I am asked if I wish to
delete n records.

Good! It works fine but I would like to perform the same activity by
means of a button that I place on the form.

If I create a button to delete a record with the help of the Control
wizard I end up with code such as:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

After sorting out the meaning of the magic numbers I get to:

DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70

When I run this it only deletes one, not many, records.

Furthermore, the help system informs me that the DoMenuItem has been
replaced RunCommand!! However exploring the RunCommand gets me no
further. By the way I am using Access 2000.

The menu item seems to know that I have selected more than one
record. How I can do this in code?
From: Jim Devenish on
I have a partial solution to my problem. When using the Delete record
item directly from the Edit menu I discover that the Delete event is
called for each selected record. This enables me to count how many
records have been selected and then set my own message:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As
Integer)
If MsgBox("You are about to delete " & nDeletions & " plants" &
vbCrLf & vbCrLf & "Are you sure?", vbYesNo, "Deleting plants") = vbNo
Then
Cancel = True
End If
Response = acDataErrContinue
nDeletions = 0
End Sub

Private Sub Form_Delete(Cancel As Integer)
nDeletions = nDeletions + 1
End Sub

Private Sub Form_Open(Cancel As Integer)
nDeletions = 0
End Sub

This works OK.

However I would still like to provide a button on the Form. I thought
that this would work:
Private Sub deleteButton_Click()
On Error GoTo Err_deleteButton_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
Exit_deleteButton_Click:
Exit Sub
Err_deleteButton_Click:
If Err.Number <> 2501 Then
MsgBox Err.Description, , Err.Number
End If
Resume Exit_deleteButton_Click
End Sub

but it only deletes one record. I tried removing the first line:
DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, ,
acMenuVer70
If I do this then nothing is deleted.

When one uses the menu item directly, the system seems to know which
records have been selected but when one uses code with DoMenuItem, you
have to do the selection. There seems to be no way of selecting the
whole group.

Any suggestions?
From: Douglas J. Steele on
The proper way to do deletions is to use a Delete query, not to try and use
the antiquated DoMenuItem method.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Jim Devenish" <internet.shopping(a)foobox.com> wrote in message
news:3771a3a5-78e6-4843-a1eb-8d4e6d6b8003(a)l20g2000yqm.googlegroups.com...
>I have a partial solution to my problem. When using the Delete record
> item directly from the Edit menu I discover that the Delete event is
> called for each selected record. This enables me to count how many
> records have been selected and then set my own message:
>
> Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As
> Integer)
> If MsgBox("You are about to delete " & nDeletions & " plants" &
> vbCrLf & vbCrLf & "Are you sure?", vbYesNo, "Deleting plants") = vbNo
> Then
> Cancel = True
> End If
> Response = acDataErrContinue
> nDeletions = 0
> End Sub
>
> Private Sub Form_Delete(Cancel As Integer)
> nDeletions = nDeletions + 1
> End Sub
>
> Private Sub Form_Open(Cancel As Integer)
> nDeletions = 0
> End Sub
>
> This works OK.
>
> However I would still like to provide a button on the Form. I thought
> that this would work:
> Private Sub deleteButton_Click()
> On Error GoTo Err_deleteButton_Click
> DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, ,
> acMenuVer70
> DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
> Exit_deleteButton_Click:
> Exit Sub
> Err_deleteButton_Click:
> If Err.Number <> 2501 Then
> MsgBox Err.Description, , Err.Number
> End If
> Resume Exit_deleteButton_Click
> End Sub
>
> but it only deletes one record. I tried removing the first line:
> DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, ,
> acMenuVer70
> If I do this then nothing is deleted.
>
> When one uses the menu item directly, the system seems to know which
> records have been selected but when one uses code with DoMenuItem, you
> have to do the selection. There seems to be no way of selecting the
> whole group.
>
> Any suggestions?


From: Jim Devenish on
On Aug 5, 4:58 pm, "Douglas J. Steele"
<NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote:
> The proper way to do deletions is to use a Delete query, not to try and use
> the antiquated DoMenuItem method.
>
> --
> Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
> Co-author: Access 2010 Solutions, published by Wiley
> (no e-mails, please!)
>

Doug
That is what I always do when deleting a single record. However one
user has asked for the ability to delete a selected group. My problem
is determining which records have been selected. The system seems to
know. So why can I not find out in code?
From: Salad on
Jim Devenish wrote:

> I have a continuous form. I can select a number of records by clicking
> on a record selector and then shift-clicking on another one. The
> whole group from the first to the second is selected. If I then
> select 'Delete record' from the Edit menu, I am asked if I wish to
> delete n records.
>
> Good! It works fine but I would like to perform the same activity by
> means of a button that I place on the form.
>
> If I create a button to delete a record with the help of the Control
> wizard I end up with code such as:
>
> DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
> DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
>
> After sorting out the meaning of the magic numbers I get to:
>
> DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, ,
> acMenuVer70
> DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
>
> When I run this it only deletes one, not many, records.
>
> Furthermore, the help system informs me that the DoMenuItem has been
> replaced RunCommand!! However exploring the RunCommand gets me no
> further. By the way I am using Access 2000.
>
> The menu item seems to know that I have selected more than one
> record. How I can do this in code?

I think you need to use Seltop and Selheight. This is a method I used.
I probably got it off the web and modified. Maybe somebody has
another method.

On the form's MouseMove event I store the current "Sel" values. If I
press a button to delete, the "Sel" values are lost, thus they are reset
in the SelRestore() routine. In this I display the list of employee
names when the Delete button is pressed. I commented out the code to do
the actual deletion.

Option Compare Database
Option Explicit

Dim lngSelTop As Long
Dim lngSelHeight As Long
Dim intMouseDown As Integer
Dim frm As Form
Dim lngID As Long
Dim s As String

Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
s = SelRecord([Form], "Down")
End Sub
Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As
Single, Y As Single)
s = SelRecord([Form], "Move")
End Sub
Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
s = SelRecord([Form], "Up")
End Sub

Function SelRecord(f As Form, MouseEvent As String) As String
Select Case MouseEvent
Case "Move"
' Store the form and the form's Sel property settings
' in the MySel variables ONLY if mouse down has not
' occurred.
If intMouseDown = True Then Exit Function
Set frm = f
lngSelTop = f.SelTop
lngSelHeight = f.SelHeight


Case "Down"
' Set flag indicating the mouse button has been pushed.
intMouseDown = True
Case "Up"
' Reset the flag for the next time around.
intMouseDown = False
End Select
End Function
Public Sub SelRestore()
' Restore the form's Sel property settings with the values
' stored in the lngSel variables.
frm.SelTop = lngSelTop
frm.SelHeight = lngSelHeight

End Sub

Private Sub CommandDelete()
' Restore the lost selection.
SelRestore

' Enumerate the list of selected employee names.
DisplaySelectedEmployeeNames()

End Sub
Private Sub DisplaySelectedEmployeeNames()
Dim i As Long
Dim f As Form
Dim rst As Recordset
Dim sDel As String

' Get the form and its recordset.
Set f = Me
Set rst = f.RecordsetClone

' Move to the first record in the recordset.
If rst.RecordCount > 0 Then
rst.MoveFirst

' Move to the first selected record.
rst.Move f.SelTop - 1

' Enumerate the list of selected records presenting
' the employee field in a message box.
lngID = 0
For i = 1 To f.SelHeight
sDel = sDel & rst![LastName] & " ,"
rst.MoveNext
Next i
MsgBox sDel

rst.Close
'sDel = Left(sDel, Len(sDel) - 2) 'remove comma
'sDel = "Delete * From tblEmployees Where EmployeeID In (" &
sDel & ")"
'DoCmd.SetWarnings False
'DoCmd.RunSQL sDel
'DoCmd.SetWarnings True

'remove them from form
'Me.Requery

Else
MsgBox "There are no records.", , "Info"
End If
End Sub