Microsoft Access forms, delete record button error-CodePudding

I have to create forms in ms access with a database linked from postgresql.

The forms need to show functionalities such as delete record, and this is where my problem is. I have created a button with a delete command:

Private Sub Delete_Click()

    If MsgBox("Are you sure?", vbYesNoCancel) <> vbYes Then
        Exit Sub
    End If
    DoCmd.RunCommand acCmdDeleteRecord

End Sub

However when I press it, the command gets cancelled and comes up with a 2501 runtime error.

I have only just started using ms access and any kind of commands so I'm unsure how to fix this issue.

I haven't tried anything as no one else seemed to have this issue.

CodePudding user response：

Generally this means that the underlying linked table does not contain enough information about the index scheme of the table to know how to formulate a DELETE statement. Specifically, it does not understand what the primary key is so that it can generate a DELETE statement that only deletes a single row.

You can verify if this is your issue by opening the Linked Table itself, highlighting the row, and pressing delete. If the row cannot be deleted from the table, you won't be able to delete it from a form based on it.

If this is the case, to solve it, right click the table and run the Linked Table Manager. When relinking the table, if Access needs more information about the index schema it will prompt for it. Help it by selecting the column(s) that comprise the primary key. It will cache this information along with the linked table and you should be able to delete the current row from your form.

Another approach would be to create a DELETE Query that references the ID in the form in the WHERE condition (i.e., Forms!MyForm!ThePKFieldName). Then, in your code behind the button, replace the

DoCmd.RunCommand acCmdDeleteRecord

with

 DoCmd.OpenQuery "qryYourQuery"

See this documentation: https://learn.microsoft.com/en-us/office/vba/api/access.docmd.openquery

If this still does not work, your ODBC driver may need to be updated, or you can use an alternative driver.