RunCommand acCmdDeleteRecord creates empty record

Posted on 2006-04-06
Medium Priority
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

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

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

ID: 16397862
<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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 58

Accepted Solution

harfang earned 1600 total points
ID: 16398560

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

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

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

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

ID: 16449665

> 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!

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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