RunCommand acCmdDeleteRecord creates empty record

Posted on 2006-04-06
Last Modified: 2008-02-01
I'm using Access 2003 on a 2000 database.  Under the DELETE button on a form, I use

        DoCmd.RunCommand  acCmdDeleteRecord

to delete the record showing in the form.  However, what actually happens is that the record being deleted is blanked out, and a new auto-ID value is assigned.  This new empty record then stays as part of the table.  In the ComboBox that I use to select the record (on the parent form), the deleted record then appears as "#deleted".

Even if I exit the database and restart it, the empty record is still there.  After the restart, the record just now appears as a blank line in the ComboBox.  To get rid of it, I have to do something like highlight the empty record at the normal database interface, and hit DEL, or select DeleteRecord from the EDIT menu.  After that, the record is truly gone.

Can anyone explain why the record doesn't actually get *really* deleted?  And how can I make it truly go away in VBA?  Other solutions for deleting the record would be welcome, but I would also like to understand why this happens.

Question by:DentRouge
    LVL 39

    Expert Comment

    What you are describing will happen if you use acCmdDeleteRecord before the data on the form is saved and a record is created.  Try this in the code for your command button
    Private Sub YourCommandButtonNameHere_Click()
    If Me.IsDirty = True Then                 'does the form have unsaved data?
         Me.Dirty = False
         DoCmd.RunCommand  acCmdDeleteRecord
    End If
    End Sub

    Note: a deleted record will show #deleted in the table until you do a compact to completely remove it but it should not nomally show up on a form.

    Author Comment

    Hi Thenelson,

    The records being deleted are records that already exist in the table.

    Your note at the end does help explain the presence of the empty records.  But I'm curious why the deleted record has a new ID autonumber.  And I'm curious about using DEL or the Edit|DeleteRecord command, which seems to actually get rid of the record, without leaving a blank record in it's place.  Why do these methods not require a compaction?

    For the record, the "#deleted" doesn't show up on the form, but it does show up in the ComboBox with which I select the record to display on the subsequent form.  When I restart Access, those records are no longer called "#deleted", but rather are simply blank records.  In any case, when I view the table directly, the empty records are there in the table.

    Thanks for any help on this.

    BTW is the compaction the operation that one invokes on the menu under Database Utilities?
    LVL 28

    Expert Comment

    <For the record, the "#deleted" doesn't show up on the form, but it does show up in the ComboBox with which I select the record to display on the subsequent form

    Because the row source for the combo box needs to be requeried after the record deletion, i.e.

    DoCmd 'delete record here'

    OM Gang
    LVL 58

    Accepted Solution


    Your note is confusing, because it mixes two quite different things.
    1) A deleted record remains in the mdb file until it is overwritten or until the database is compacted.
    2) A Dynaset or Keyset recordset has a fixed number of records in a fixed order. When it encounters a record that has been deleted from the table, it displays the #deleted error. The point here is that the form and the combo use different recordsets. So when a row is deleted from the form, the recordset of the combo is not aware of the fact, and still uses the previous recordcount.

    This is quite easy to experiment: open a table and a query based on the same table and display them side by side. Then try to delete a record in one window, or add records, and then see what happens in the other.

    DentRouge, [are you a relative of BlueTooth?]

    The normal behavior you get from "RunCommand  acCmdDeleteRecord" is the exact same as clicking on the "delete record" toolbar button or choosing "Edit / Delete Record". After the deletion, you should see the next record displayed or the blank "new" record if you deleted the last one.

    If you get instead a blank record and if that blank records gets saved to the table, you have a problem elsewhere. What other code to you use (e.g. Form_Current)? You could try searching the module for things like "acCmdSaveRecord" or  "Me.Dirty = False". Also, is the recordsource of your form a single table or a query with multiple tables?

    Finally, the new "blank" record gets an ID number only when you start to edit something in the record. Since you don't do it by hand, you must have a line of code writing something to one of the fields. Do you use a time stamp mechanism? If yes, which is it?

    LVL 58

    Expert Comment

    Another idea:

    Go to the table design and make the main "name" field (the one you display in the combo box) required. That way, you should get an error message at the exact point where your code saves the blank record with the new ID.

    Good luck!

    Author Comment

    Hi harfang,

    I will check out the code further to see if there is something creating a new record.  That was something I suspected also, but a look at the code convinced me that it was impossible.  But perhaps some experimenting is called for here, as you say.  I gather that the supposedly deleted records would not under any condition look like regular blank records, the next time the database is opened.  I'll get back to you.

    RE: your questions:

    The form is bound to a table.  The ComboBox uses a small query based on the same table.  The filter for the form is set in FORM_Open.


    P.S.  I am indeed very closely related to Bluetooth.  :)


    Author Comment

    Ok, I’ve figured it out.  What I had been doing was to delete the record and then close the form.  The delete blanked out the form.  Then, closing the form created the new blank record.  So, what I had to do was DoCmd.Undo before closing the form.  

    Before, when I closed the database and restarted it, the “#delete” in the ComboBox would disappear, because the box had essentially requeried, but the new blank record would appear, seemingly in place of the “#delete”.  So I had been thinking that the one had become the other, but it was an illusion, and I didn’t stop to really think about it.

    The advice to carry out a DoCmd.Undo seemed not to apply, because, after the delete, I supposedly had a new pristine record in the form, rather than one that already existed in the table.  But there was one field with a default value way down the list of fields that I wasn’t noticing, and the autonumber field got set.  So the record then qualified as “dirty”,

    I guess that, technically, thenelson told me the right thing to do, but I had said that I wanted to understand what was going on at least as much.  To that end, harfang explained things so that I understood this problem and more.  So I feel like I should give the points to harfang.  Thank you for your help!
    LVL 58

    Expert Comment


    > but it was an illusion

    We should use that more. It's a good database design concept ;)

    > technically, thenelson told me the right thing to do

    In that case, "technically",  a split would be in order. At the bottom of an open question you have the option to split points in those cases. See also: http:/help.jsp#hs5

    Anyway, thanks for points and grade, and good luck with your project!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    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…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    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.
    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…

    758 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

    14 Experts available now in Live!

    Get 1:1 Help Now