How to delete one record at a time using command button?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I have a command button and a couple of textbox controls in my subform's
Detail section (with continuous form as the default view). I'd like to be
able to click the button and delete record in the subform.

Arvin Meyer has kindly wrote out the lines for me as below:

Private Sub cmdButtonName_Click()
CurrentDb.Execute "Delete * From tblTableName Where IDName ='" & Me.IDName &
"'"
Me.Requery
End Sub

This allows me to delete ALL records with the same IDName in the table.
However, since all records in my subform have the same IDName, what may I do
if I only wanna delete any one record at a time? Any suggestion is
appreciated.

Regards,
Sam
 
hi,
you will need to add an AND to the WHERE clause to bring
in another criteria to specify the exact record you want
to delete.
CurrentDb.Execute "Delete * From tblTableName WHERE IDName
='" & Me.IDName & "'" AND secondcriteria = '" & me.txt2
& "'"

something like that. if you have duplicates in the second
criteria then you may need to add another AND.
 
Thanks. I replicate your lines in my application, but an error message says
"Type mismatch". Why is that?

Private Sub cmdButtonName_Click()
CurrentDb.Execute "Delete * From tblTableName WHERE IDName ='" & Me.IDName &
"'" AND AmendDate = '" & me.AmendDate & "'"
Me.Requery
End Sub
 
Sorry, I replied to your previous thread as follows:
I have just such a button on one of my forms. It is located in a subform
which is set to Continuous forms. The button should be placed within the
continuous form itself, not in the header. The code is very simple:

Recordset.Delete

I know it sounds like it should delete the entire recordset, but it
doesn't. It just deletes the record within which the button resides.

I hope that's what you need.

Regards,

Gary
 
If you have a date/time field in an SQL string, you need to use # as the
delimiter, rather than '. You've also got an extraneous " character in the
string (before the AND). Try changing the last section of the Where clause
to:
& "' AND AmendDate = #" & me.AmendDate & "#"

Depending on your date format settings, you may also need to convert
AmendDate to mm/dd/yyyy format.

HTH,

Rob
 
Thanks Gary. It is exactly what I need :)

I posted the same question here again, just thought I could get more help
(in case Arvin didn't see my post). My apology if this has cause trouble to
anyone.

Regards,
Sam
 
Back
Top