Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-11-01
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 18

Assisted Solution

Data-Man earned 800 total points
ID: 12463989
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

harfang earned 1200 total points
ID: 12464296
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

ID: 12464455
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

ID: 12464504
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

610 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