Access 2007 - Prompt and Error while deleting a record

Posted on 2007-10-13
Last Modified: 2013-11-28
I have a split database. I have a button on a form bound to table (tblCalls).  The intent of this button is to delete the current record.   I am using the following code in the OnClick event to do this deletion:

            SQL = "DELETE * FROM tblCalls WHERE ID = " & Me.ID
            DoCmd.RunSQL SQL

This comes back with the following warning: "You are about to delete 1 row(s) from the specified table".  This is correct, but I would like this question to not appear.  How do you do this?  In Access Options/Advanced I unchecked the Confirm/Document Deletions check box, but that didn't do anything.

If I answer Yes to the above question, I get the following error message "can't delete 0 record(s) in the delete query due to key violations and 1 record(s) due to lock violations.
Selecting Yes to this message is suppose to run the query.  The record is not deleted.  There are no other records associated with this parent record.  I have set "Cascade Delete Related Records" to TRUE.

I have set "Record Locks" on the form to "Edited Record".   The record was not edited. I tried moving to another record with the following code:

            Dim iCurrentID
            iCurrentID = Me.ID
            ' Move away from current record so it can be deleted
            DoCmd.GoToRecord acDataTable, "tblCalls", acNext
            ' Delete Call - This record must exist
            SQL = "DELETE * FROM tblCalls WHERE ID = " & iCurrentID
            DoCmd.RunSQL SQL

 I get the error "GoToRecord isn't available now".  What does this error message mean?

Thanks for your help

Question by:SheahaST
    LVL 77

    Expert Comment

    Docmd.setwarnings False

    ....delete your records

    Docmd.setwarnings True

    Author Comment

    Thank you.  The above does get rid of the warnings, but the code still does not delete the current record.  Any answers for the rest of my problem?

    Author Comment

    Setting the form's Record Locks to "No Locks" and then deleting the record with the above SQL command appears to delete the record from the table but not from the form.  Navigating back and forth gives a "Deleted Record" error, and eventually I reach a reference to a field in the deleted record.
    LVL 58

    Accepted Solution

    You are in effect trying to delete the current record directly in the table, and not from the current recordset.

    To delete the current record, use one of these:

        RunCommand acCmdDeleteRecord

    To delete from the table (but not from the recordset, as you noticed), use

        DoCmd.RunSQL "DELETE FROM tblCalls WHERE ID=123"   ' confirmation msg
        CurrentDb.Execute "DELETE FROM tblCalls WHERE ID=123"   ' no confirmation

    Any open recordset containing that record will display a "#deleted" error message. Note also that a form normally locks the current record to prevent just that.

    Good luck!

    Author Comment

    Thank you very much harfang.  Not only for the solution but for clarifying in my head the differences between a recordset and a table.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now