RunCommand acCmdDeleteRecord creates empty record

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
DentRougeAuthor Commented:
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?
omgangIT ManagerCommented:
<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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


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?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
DentRougeAuthor Commented:
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.  :)

DentRougeAuthor Commented:
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!

> 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.