Running SQL Del statement in VBA but have to get Delete response first

[curious_24]

Hi,

I'm running a sql statement code below in VBA and it works perfectly fine and deletes the record from the table but:
1. It only runs when I select yes after being prompted if I'm sure to delete the record. I need to do an if statement that takes the response of the button selected (yes or no) and if Yes run the sql statement otherwise cancel the action because if I select No when prompted it will give an error.

Dim DelIdRcd As Integer
DelIdRcd = Me.DirectorshipExperienceID.Value
Dim QryDel As String
QryDel = "Delete * From BoardMemberPositions where DirectorshipExperienceID = " & DelIdRcd
DoCmd.RunSQL (QryDel)

2. After it deletes the record, The word #DELETE appears in all the fields, although I can create a new record and than save and close the form down and open it again and the #DELETE record will be gone..... so logically it work but it's untidy and I would prefer to have all the cells cleared after deleting

I tried doing:

If Me.AfterDelConfirm Then
DelIdRcd = Me.DirectorshipExperienceID.Value
Dim QryDel As String
QryDel = "Delete * From BoardMemberPositions where DirectorshipExperienceID = " & DelIdRcd
DoCmd.RunSQL (QryDel)
End If

But that doesn't work

Thanks in advance for any help - much appreciated

[jonh]

if msgbox("delete?",vbquestion + vbyesno) = vbyes then
currentdb.execute QryDel
me.requery
end sub

[curious_24]

Thanks Jonh - that works perfectly, but the only problem is I have to click somewhere else on the form for it to work, if I've only just entered the details in and click on delete it won't work.

Thanks very much for your help :)

[jonh]

For new records there's nothing to delete so undo instead.

if msgbox(blah blah) = vbyes then if me.newrecord then me.undo else currentdb.execute sql