RunCommand acCmdDeleteRecord deletes record, but blank new record inserted immediately afterwards

Posted on 2004-11-01
Last Modified: 2008-03-17
I have a problem where I have a parent form (fEditAgreement) and a continuous subform (tTerm_subform) that displays related records.  Each instance of the term subform contains an Edit button.  When that Edit button is clicked, a new form (fEditTerm) is opened that is filtered to that term.  This works fine.  However, I also have a delete button on fEditTerm.  I currently have the onClick event set to the function below:

Private Sub cmdDeleteTerm_Click()
    'Delete item
    RunCommand acCmdDeleteRecord
    'Close form
    DoCmd.Close acForm, "fEditTerm"
    'Refresh subform
End Sub

What ends up happening is that the current record in fEditTerm is deleted, but a blank new record is immediately inserted at the same time.  How do I keep that blank record from being inserted?


Question by:emilysam
    LVL 18

    Assisted Solution

    Can the users add new records in the subform?  If you don't want them to do this change the Allow Additions property of the suform to no.

    What happens if you perform the requery before you close the fEditTerm form?

    Normally when a requery occurs the focus is on the first record.

    LVL 58

    Accepted Solution

    Considering the code:

        'Delete item
        RunCommand acCmdDeleteRecord
                                           <----- what happens here?                
        'Close form
        DoCmd.Close acForm, "fEditTerm"

    After the record is deleted, the form displays a new record. The event Form_Current is triggered, even for the Me.NewRecord.
    Do you have something happening in the Form_Current Event? For example setting of some default values?

    Another idea is to add:

        Me.AllowAdditions = False

    Just before the Delete command. This way, the new blank record is never created. And thus cannot be saved.

    Yet another idea:

    Put "Stop" just before the delete command. Then execute line by line. You should see Form_Current if that event has a handler.

    Good Hunting!

    Author Comment

    The fEditTerm subform is doing dual-duty as an add and an Edit form.

    Putting in the line Me.AllowAdditions = False before the delete works fine.  However, I now get a confirmation prompt that I wasn't getting before "Are you sure you want to delete this record?".  How do I turn that off?


    Author Comment

    Figured it out.  Had to use:

        DoCmd.SetWarnings False


        DoCmd.SetWarnings True

    Before and after the delete statement.

    Thanks.  Since you both got the answer right, I'm splitting the points

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    933 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

    17 Experts available now in Live!

    Get 1:1 Help Now