Deleting multiple records from form

  • Thread starter Thread starter Miguel Velez
  • Start date Start date
M

Miguel Velez

I want my cmdDelete button to delete one or many records
depending upon what the User highlighted. I do not want
them to have to click the Del button on the keyboard but
use my button on the form. Right now only one record per
command button click deletes. I cant's get the loop
right.I am back to:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
[frmPSRSubform].SetFocus
DoCmd.RunCommand acCmdDeleteRecord
etc.

Thanks
 
Add a field named Selected to your table. Add Selected to your form for the user
to select the records he wishes to delete. Put the following code in the click
event of CmdDelete:

Dim SQLStr As String
SQLStr = "DELETE YourTable.* " _
& "FROM Your Table "
& "Where Selected = True;"
Set Warnings False
DoCmd.RunSQL (SQLStr)
Set Warnings True
 
Hi Miguel

It seems that your button is on the main form, but you are deleting the
records from the subform. The trouble is that when the subform loses focus
(to click your button), the selection range disappears.

Is it possible to put your delete button on the header or footer of the
subform? Then it will work.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Thanks for the information but I want to do it without
adding any extra fields to the tables. I have actually
found a way that works really well and I thought you (or
others) may be interested.

It utilises the On Timer event; set the Timer interval to
say 1000. Then added this code:

---------------------------------------------
Dim strSQL As String

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

If MsgBox("Are you sure you want to delete the record
(s)?", _
vbExclamation + vbYesNo, _
"Please Confirm...") = vbYes Then
MsgBox ("I am going to delete: " & strSQL)
DoCmd.RunSQL strSQL
Me.Requery
End If

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdDelete_Click

End Sub

Private Sub Form_Timer()
Dim i As Long

If Me.SelHeight = 0 Then Exit Sub

strSQL = "Delete * from
Where "

With Me.RecordsetClone
.MoveFirst
.Move Me.SelTop - 1

For i = 1 To Me.SelHeight
strSQL = strSQL & "TableID = " & ![TableID]
& " or "
.MoveNext
Next i
strSQL = Left$(strSQL, Len(strSQL) - 4) & ";"

End With
End Sub
---------------------------------

You may need to add quotes or hashes around the "!
[TableID]" if the field is a string or a date (for dates
you may have to use the Format function to get it in the
right format).

Miguel
-----Original Message-----
Add a field named Selected to your table. Add Selected to your form for the user
to select the records he wishes to delete. Put the following code in the click
event of CmdDelete:

Dim SQLStr As String
SQLStr = "DELETE YourTable.* " _
& "FROM Your Table "
& "Where Selected = True;"
Set Warnings False
DoCmd.RunSQL (SQLStr)
Set Warnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com



Miguel Velez said:
I want my cmdDelete button to delete one or many records
depending upon what the User highlighted. I do not want
them to have to click the Del button on the keyboard but
use my button on the form. Right now only one record per
command button click deletes. I cant's get the loop
right.I am back to:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
[frmPSRSubform].SetFocus
DoCmd.RunCommand acCmdDeleteRecord
etc.

Thanks
Click to expand...


.
Click to expand...
 
The buttons are in the correct form or subform (I'm trying
to do the same thing in many forms). I've found a way to
do it; if you're interested, see my reply to "PC
DataSheet" for the code.

Thanks.

Miguel
-----Original Message-----
Hi Miguel

It seems that your button is on the main form, but you are deleting the
records from the subform. The trouble is that when the subform loses focus
(to click your button), the selection range disappears.

Is it possible to put your delete button on the header or footer of the
subform? Then it will work.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Miguel Velez said:
I want my cmdDelete button to delete one or many records
depending upon what the User highlighted. I do not want
them to have to click the Del button on the keyboard but
use my button on the form. Right now only one record per
command button click deletes. I cant's get the loop
right.I am back to:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
[frmPSRSubform].SetFocus
DoCmd.RunCommand acCmdDeleteRecord
etc.

Thanks
Click to expand...


.
Click to expand...
 
Back
Top