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

[enquiries]

Hi AccessForum users
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

[ItsMe]

The prompt you are getting is not associated with your VBA. Therefore, you get an error when you click no. You can suppress the prompt by using something like
DoCmd.SetWarnings False
DoCmd.RunSQL (QryDel)
DoCmd.SetWarnings True

The preferred method is not to use Docmd.RunSQL
CurrentDb.Execute QryDel

Either method will avoid the ability to abort. If you want to include the ability for the user to exit VBA without executing, use a msgbox with an argument that includes vbYesNo or vbOKCancel.

Take a look at the help files for MsgBox and post back with any questions for us.

[ipisors]

As far as your 2nd question, you should Requery the form or control after running this code.

[enquiries]

Thank you both for your help - am getting somehwere
1. ItsMe - Okay, I've managed to get it working with:
If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo) = vbYes Then
CurrentDb.Execute QryDel
Me.Requery
End If

But I still need to click somewhere else on the form before click on the DELETE Button, it won't let me delete if I've just entered in the details - I'm not sure if this will be a problem, but I prefer it would.

2. ipisors - I added on Form.Requery and that clears it up perfectly thanks



Thanks again very much for your help

[enquiries]

I can't figure out how to delete my posts Lol - so I've just removed the wording from this and reedit it as a side question. How do you delete posts on here please? Thanks

[ItsMe]

...ill need to click somewhere else on the form before click on the DELETE Button, it won't let me delete if I've just entered in the details...

I am not sure I follow. You can save the current record before running your query to ensure newly entered data is included in the SQL action query.

If me.dirty = true then me.dirty = false

[ipisors]

>>But I still need to click somewhere else on the form before click on the DELETE Button, it won't let me delete if I've just entered in the details - I'm not sure if this will be a problem, but I prefer it would

I think I agree with ItsMe basically but I am just curious on the details.....By 'won't let me delete' what exactly happens? Visually nothing? An Error?

[enquiries]

Got it fixed - just added an UNDO button that deletes the record if it hasn't been saved and the DELETE button works perfectly as it is.

Thanks for all your help