CurrentDb.Execute delete query not deleting anything when its executed from VBA

2
votes

I am trying to get my code to delete a newly created record if the user cancels. for some reason Access is not deleting the record even though the query is definitely filtering for unique IDs which exist within the table. Access is not throwing any errors.

PG_ID is the unique identifier in both tables, it is a Long Integer.

I've included a sample portion of my code below. Please help!

    Dim var_PGID As String
    Dim Delete_PG_Data, Delete_PG_Upld As String
    Dim db As Database
        Set db = CurrentDb
        var_PGID = TempVars![var_PG_ID_NEW]
        Delete_PG_Data = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group " & _
                         "WHERE PG_ID=" & var_PGID
        Delete_PG_Upld = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group_Attachements " & _
                         "WHERE PG_ID=" & var_PGID
        Debug.Print Delete_PG_Data
        Debug.Print Delete_PG_Upld
        db.Execute Delete_PG_Data, dbFailOnError
        db.Execute Delete_PG_Upld, dbFailOnError

As requested I've switched msgbox to Debug.Print. Below is the debug.print output which runs correctly when placed in an access query.

It was a timing issue. I fixed it by committing the transaction then running the delete query. Thank you all for your input!

Private Sub cmd_Cancel_Click()
On Error Resume Next
DoCmd.SetWarnings False
If TempVars![var_NewRecord] = True Then
    Do While Not Me.Recordset.EOF
            Me.Recordset.Update
            Me.Recordset.MoveNext
        Loop
            DBEngine.CommitTrans
            Me.Recordset.Close
    Dim var_PGID As String
    Dim Delete_PG_Data, Delete_PG_Upld As String
    Dim db As Database
        Set db = CurrentDb
        var_PGID = TempVars![var_PG_ID_NEW]
        Delete_PG_Data = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group " & _
                         "WHERE PG_ID=" & var_PGID
        Delete_PG_Upld = "DELETE * " & _
                         "FROM tbl_CapEx_Projects_Group_Attachements " & _
                         "WHERE PG_ID=" & var_PGID
        Debug.Print Delete_PG_Data
        Debug.Print Delete_PG_Upld
        db.Execute Delete_PG_Data, dbFailOnError
        db.Execute Delete_PG_Upld, dbFailOnError

    ''Me.Recordset.Delete
    ''DBEngine.BeginTrans
    ''DBEngine.CommitTrans
Else
    If Me.Saved Then
        DBEngine.Rollback
    Else
        If Me.Dirtied Then DBEngine.Rollback
    End If
End If
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
    Form_frm_CapEx_Edit_Project_Groups_Cont.Requery
DoCmd.SetWarnings True
End Sub
2
sqlms-accessvba
Hmm, looks like it should work if PG_ID is indeed a long int. Please replace MsgBox by Debug.Print and add the output from the Immediate window to your question (Ctrl+G takes you there). -- See also: How to debug dynamic SQL in VBA – Andre
Access may throw an error, but you don't see it because you didn't add dbFailOnError parameter to db.Execute. Try to copy the value of Delete_PG_Data and Delete_PG_Upld variables to query builder and execute there. – Sergey S.
Are you sure that TempVars![var_PG_ID_NEW] variable contains right value? – Sergey S.
Yes the variable TempVars![var_PG_ID_NEW] for the unique identifier is definitely correct. – Moehling
No offense, but I think this code is going to blow up on you rather sooner than later. Running all this stuff after On Error Resume Next is dangerous, it's masking all sorts of errors - you are updating records you didn't edit, committing or rolling back transactions you didn't start, etc. -- I have the feeling that a simple Me.Dirty = False to save the new & edited record would solve your issues. – Andre

2 Answers

1
votes

.. delete a newly created record if the user cancels

Sounds like the record doesn't get saved. Even if not, it could be a timing issue as the run the query in another context than the form.

If it really has been created, the simplest and fastest method is to delete the record from the RecordsetClone of the form.

0
votes

Use DoCmd.RunSQL

example : 

Public Sub DoSQL() 

    Dim SQL As String 

    SQL = "UPDATE Employees " & _ 
          "SET Employees.Title = 'Regional Sales Manager' " & _ 
          "WHERE Employees.Title = 'Sales Manager'" 

    DoCmd.RunSQL SQL 

End Sub

Hence your new code will be as below: 

Dim var_PGID As String
Dim Delete_PG_Data, Delete_PG_Upld As String
var_PGID = TempVars![var_PG_ID_NEW]
Delete_PG_Data = "DELETE * " & _
                     "FROM tbl_CapEx_Projects_Group " & _
                     "WHERE PG_ID=" & var_PGID
Delete_PG_Upld = "DELETE * " & _
                     "FROM tbl_CapEx_Projects_Group_Attachements " & _
                     "WHERE PG_ID=" & var_PGID
MsgBox Delete_PG_Data
MsgBox Delete_PG_Upld
DoCmd.RunSQL Delete_PG_Data
DoCmd.RunSQL Delete_PG_Upld