Solved Cannot delete record from linked SQL Server table using VBA (1 Viewer)

[mikenyby]

Hello all,

In my database I have a table "images" that is ODBC linked to a SQL Server table. I have two forms for this table, frmAddImage and frmEditImage. I have no problem adding data and updating data using the two forms, but I cannot delete records using the forms. I can however, delete the record by opening the table view in access, right clicking on the row, and clicking "delete". This method deletes the record in both Access and SQL Server. But I need a front-end method on a form for the end user to delete records if necessary. And before you ask, yes, there is a primary key (imageID) and it is assigned and recognized in both Access and SQL Server.

I have tried deleting the records using both the acCmdDeleteRecord and RunSQL DELETE methods:

Private Sub CmdDelete_Click()
    If MsgBox("Are you sure you want to delete this image? This action cannot be undone.", vbYesNo, "WARNING") = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
        MsgBox "Image deleted from database.", vbOKOnly, ""
    Else
        Exit Sub
    End If
   
End Sub

And:

Private Sub CmdDelete_Click()
    If MsgBox("Are you sure you want to delete this image? This action cannot be undone.", vbYesNo, "WARNING") = vbYes Then
        DoCmd.RunSQL ("DELETE * FROM Images WHERE ImageID = Forms!frmEditImage!cboImageID")
        MsgBox "Image deleted from database.", vbOKOnly, ""
    Else
        Exit Sub
    End If
   
End Sub

For both of them I get this error message that says I'm about to delete 0 rows from the specified table.

After clicking Yes, I check the table and the record still exists.

Does anyone have any ideas?

Thanks!

 

[mikenyby]

NEVER MIND!

I made a foolish mistake. I closed the form without saving when I renamed the control to 'cboImageID'. So the SQL statement was referencing a control that didn't exist. Fixed that, works fine.