Help - Wish to Delete Record Using a Command Button on a Form

[Bucyruss]

This has got me a bit confused. According to Access on-line help, the proper command to delete a record is:

DELETE * FROM

WHERE Field = 'Some Value'; OR
DELETE [Table.*] FROM

WHERE Field = 'Some Value'

Is there some reason this command cannot be used at the click event on a command button? I have tried every variation I can think of and this command will not compile (in "*" only form, compiling halts at the *, in [Table.*] form, the compiling halts at 'FROM'.

All I am trying to do is to enable the user to delete a single record by pressing a command button (record to be deleted based on an existing value on the form). This seems like simple stuff, but it will not work and I'm at a loss to figure out why.

 

[Robert Dunstan]

Have you tried creating a command button using the wizard? This will create the code for you.

 

[Matt Leidemer]

Private Sub cmdDelete_Click()

Dim DBS As Database
Dim RST As Recordset
Dim QDF As QueryDef

Set DBS = CurrentDb
Set QDF = DBS.CreateQueryDef("DeleteRecord")
QDF.SQL = "delete * from table_name where key_field = 'requested_value'"
Set RST = QDF.OpenRecordset()

RST.Close
DBS.QueryDefs.Delete "DeleteRecord"
DBS.Close

End Sub

- Matt

 

[AlaskanDad]

If you want to delete the CURRENT RECORD, you only need one line of code for your command button's OnClick event:

DoCmd.RunCommand acCmdDeleteRecord

If the field you mentioned filters out all of the other records, you will have only one record left and that will be your current record.

If the code is stupidly simple and it works, it ain't so stupid.