Opening delete queries in VBA does not run them

Sandra Smith
Sandra Smith used Ask the Experts™
on
Ok, this is really simple and I may just be getting extremley tired.  I have a delete button that when the user clicks it should simply open two delete queries.  Well, it does not work.  If I manually go to the query window and open each object, it deletes teh records, but the attached code does not fired the two queries.
Private Sub cmdDelete_Click()
'If user has selected a current update that they do not wish to send in the
'email, this will delete those selects items.  This deletes the item completely
'and there is no going back.

    DoCmd.OpenQuery "qdeTasks_DeleteUpdate"
    DoCmd.OpenQuery "qdeTasks_DeleteEmail"
    Me.Refresh

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:

try:

currentdb.execute "qdeTasks_DeleteUpdate", dbFailOnError

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

The advantage of using the Execute method is that you can run the query without having to supress warnings, and if you use the dbFailOnError in the Optional parameters, you can actually trap for and display custom error messages
Sandra SmithRetired

Author

Commented:
fyed, I will try that.  What is really weird is that I put the delete string in the code and used the DoCmd.RunSQL, but it does not delete on the first click, I have to click twice.  However, the code is in the click event.
Private Sub cmdDelete_Click()
'If user has selected a current update that they do not wish to send in the
'email, this will delete those selects items.  This deletes the item completely
'and there is no going back.
Dim strDeleteUpdate As String
Dim strDeleteEmail  As String

strDeleteUpdate = "DELETE tblTasksUpdates.* " & _
    "FROM tblTasksUpdates INNER JOIN tblTasksEmails ON " & _
    "tblTasksUpdates.UpdateID = tblTasksEmails.UpdateID " & _
    "WHERE tblTasksEmails.Delete = Yes "
    
strDeleteEmail = "DELETE * " & _
    "FROM tblTasksEmails WHERE tblTasksEmails.Delete = Yes "
    
    
    DoCmd.RunSQL strDeleteUpdate
    DoCmd.RunSQL strDeleteEmail
    Me.Requery
    Me.Refresh

End Sub

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

But your original post not about RunSQL, which can be used for action queries.

The OpenQuery method should only work except for Select and Pivot table queries.
Sandra SmithRetired

Author

Commented:
Ok, your idea led me to what was really wrong.  The record, when the delete checkbox is click, has not been saved yet.  I put in code to save the record first, before the delete code runs, and now it works.  It does delete, but now I have the work delete showing up in the text boxes, I thought Me.Requey and Me. Refesh or Me. Repaint would clear this deleted record on the form.  Should that be another question?
Top Expert 2016

Commented:
this will run

strDeleteEmail = "DELETE * " & _
    "FROM tblTasksEmails WHERE tblTasksEmails.Delete = Yes "

DoCmd.RunSQL strDeleteEmail

or you can use
currentdb.execute strDeleteEmail,dbfailonerror


this part

strDeleteUpdate = "DELETE tblTasksUpdates.* " & _
    "FROM tblTasksUpdates INNER JOIN tblTasksEmails ON " & _
    "tblTasksUpdates.UpdateID = tblTasksEmails.UpdateID " & _
    "WHERE tblTasksEmails.Delete = Yes "


needs to be changed to


strDeleteUpdate = "DELETE tblTasksUpdates.* " & _
    "FROM tblTasksUpdates " & _
    "Where tblTasksUpdates.UpdateID in( select UpdateID " & _
    "from tblTasksEmails WHERE tblTasksEmails.Delete = Yes "

 DoCmd.RunSQL strDeleteUpdate

or

currentdb.execute strDeleteUpdate,dbfailonerror



Sandra SmithRetired

Author

Commented:
No, I was just putting up the SQL to show I was having the same problem.  But when I saw your code, I realized that was the correct way as I had used this before, which also trigged the fact that I was not saving the record first.
Sandra SmithRetired

Author

Commented:
Actually, I also got the form to update properly, so I am good.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial