CODEWITHSUNDEEP
sqlms-accessvba
Moehling
Moehling

Reputation: 37

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

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

Upvotes: 2

Views: 4215

Answers (2)

Gustav
Gustav

Reputation: 55806

.. 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.

Upvotes: 1

Adarsh Madrecha
Adarsh Madrecha

Reputation: 7886

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

Upvotes: 0

Related Questions