Deleting the current record using SQL

  • Thread starter Thread starter Jonathan Stratford
  • Start date Start date
J

Jonathan Stratford

Hi,

I use the following code in an attempt to delete the
current record from the table, using a button on a form.
Unfortunately it doesn't work! Can anybody tell me why?

Dim strSQL As String
strSQL = "Delete * FROM [TSpecialDays] WHERE [Special Day
Date]='" & Special_Day_Date
'MsgBox strSQL
CurrentDb().Execute strSQL


Many thanks,

Jonathan Stratford
 
Jonathan said:
I use the following code in an attempt to delete the
current record from the table, using a button on a form.
Unfortunately it doesn't work! Can anybody tell me why?

Dim strSQL As String
strSQL = "Delete * FROM [TSpecialDays] WHERE [Special Day
Date]='" & Special_Day_Date
'MsgBox strSQL
CurrentDb().Execute strSQL
Click to expand...

"it doesn't work"?

Do you get an error message? State it.

I suspect, however, that [Special Day Date] is a field of type
Date/Time, and you have to delimit that with hashes (#) not quotes(').
Format the date mm/dd/yyyy too, if it isn't already.

Is Special_Day_Date a field on the form? It helps reading the code if
you put "Me!" before it. That is not necessary.
 
Jonathan said:
I use the following code in an attempt to delete the
current record from the table, using a button on a form.
Unfortunately it doesn't work! Can anybody tell me why?

Dim strSQL As String
strSQL = "Delete * FROM [TSpecialDays] WHERE [Special Day
Date]='" & Special_Day_Date
'MsgBox strSQL
CurrentDb().Execute strSQL
Click to expand...


"Doesn't work" really dosn't provide any clues as to what
might be wrong. I'll take a couple of guesses, though.

First thing I noticed is that there is an extra ' mark.

Next, I'm wondering if the [Special Day Date] field is type
Date. If so, then the value of the Special_Day_Date text
box must be enclosed in # signs:

strSQL = "Delete * FROM [TSpecialDays] " _
& "WHERE [Special Day Date]=#" & Special_Day_Date & "#"

Another reason why what you tried "Doesn't work" could be if
the form is editing the same record you're trying to delete.
In this case, it might help if you use Me.Undo to dump the
changes before trying to delete the record.
 
Jonathan,
if this is a bound form, and you want to delete the current record, you can
use this.

Private Sub lblRecDel_Click()
Dim Cancel As Integer
Dim msg As String

msg = "This will delete the current record!" & vbCrLf & vbCrLf
msg = msg & "Do you really want to do this?"

If MsgBox(msg, vbExclamation + vbYesNo) = vbNo Then
Cancel = True
Else
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End If
End Sub

Robert Dale
 
Thanks for all your help! Sorry for being so vague, I was
in a bit of a rush!!! I tried using

DoCmd.RunCommand acCmdDeleteRecord

and that worked fine - is there any reason why the wizard
for creating a delete button creates such complicated
code for calling a menu button, when this is much simpler?

Thanks again,

Jonathan Stratford
 
Jonathan said:
Thanks for all your help! Sorry for being so vague, I was
in a bit of a rush!!! I tried using

DoCmd.RunCommand acCmdDeleteRecord

and that worked fine - is there any reason why the wizard
for creating a delete button creates such complicated
code for calling a menu button, when this is much simpler?
Click to expand...

History!
 
Back
Top