Deleting Records does not Delete Records

  • Thread starter Thread starter Lucky Man Cree
  • Start date Start date
L

Lucky Man Cree

Does anyone know how to fix this problem?

I am viewing records in the form view and I select a record that I want to
delete.
I click on the "Delete Record" button.
I receive the message "You are about to delete one record. If you click
Yes, you will not be able to undo this operation. Are you sure you want to
delete these records?"
I click Yes.
The record seems to disappear from the recordset.
I close the form and reopen it.
The record is there again.

The only way I can actually delete the record is by deleting it directly
from the table view.

The form is set to "Allow Deletions = Yes". I don't want to have to keep
opening the table to delete records. Is there some other setting that I can
adjust to allow me to delete records from form view?

Thanks in advance!

Shannon
 
Well this one is new for the books. Is it possible that you could be looking
at a duplicated record. In my experience when you have deleted a record
within the form view, the record is deleted.

Is your form connected directly to the table or are you using a query? If
you are using a query, is there more than one table in your query? If yes,
then it is possible that it is constructed where you won't get a direct
error message or you have somehow suppressed error messages and the delete
seems to go through, but it doesn't.
 
G,
Thanks very much for your reply. No, I do not believe it is a duplicated
record. I have an autonumber field and I have checked the record number
before and after "deletion". It is the same record number which reappears.

You are correct that my form is based on a query which contains data from
two tables. Tomorrow, I will check whether or not I have suppressed error
messages in any of my VBA code. Is there any other place where I should
check for that? I imagine there are a lot of places in Access where that
kind of command could hide.

On a related note, if this is correct and I have simply suppressed the error
messages, then could you tell me under which circumstances deleting a record
would generate an error?

Thanks for your help!

Shannon
 
You are correct that my form is based on a query which contains data from
two tables.
Click to expand...

A simple Delete from a two-table query will delete only the "many"
side record. Check both tables!
 
Thanks for your reply, John.
It is the "many" side record that I wish to delete and it does not delete.
Do you mean that the "one" side record is preventing the delete of the
"many" side record? I have enforced referencial integrity in the
relationship between the tables but not enforced cascading updates or
deletes.

Shannon
 
Hi again G!

I checked all the VBA in the form and I did have "on error resume next"
in a couple of places. So I changed that to "on error goto err_blah"
and in err_blah I put "msgbox err.description" etc. I thought that
would fix it but it did not. Are there any other places that I should
check for error suppression? Are there any other things I could check
in general?

Thanks,
Shannon
 
Hello everyone! I am having troubles deleting records from form view. Does
anyone know how to fix the following problem?

I view records in the form view and I select a record that I want to delete.
I click on the "Delete Record" button.
I receive the message "You are about to delete one record. If you click
Yes, you will not be able to undo this operation. Are you sure you want to
delete these records?"
I click Yes.
The record seems to disappear from the recordset.
I close the form and reopen it.
The record is there again.

I don't believe that the record which "reappears" is simply a duplicate of
the one I'd deleted. I have an autonumber field and I have checked the
record number before and after "deletion". It is the same record number
which reappears.

The only way I can actually delete the record is by deleting it directly
from the table view. I don't want to have to keep opening the table to
delete records.

The form is set to "Allow Deletions = Yes". Is there some other setting
that I can adjust to allow me to delete records from form view?

My form is based on a query which contains data from two tables with a "one"
to "many" relationship. It is the record on the "many" side that I wish to
delete but which does not delete. I have enforced referencial integrity in
the relationship between the tables but not enforced cascading updates or
deletes. Could the record on the "one" side be preventing the delete of the
"many" side record?

It was suggested to me that perhaps I encounter errors in the process of
deleting the records but that the error messages are being suppressed. I
checked all the VBA in the form and I found "on error resume next" in a
couple of places. So I changed that to "on error goto err_blah" and, in
"err_blah", I put "msgbox err.description" etc. I thought that would fix it
but it did not. Are there any other places that I should check for error
suppression? Are there any other things I could check in general? What are
some of the things that could go wrong during the deletion of a record?


Thanks in advance!

Shannon
 
Thanks for the help guys! I've realized my error now.

I had three tables which were all "nested" in "one" to "many" relationships.
The table that I wished to delete records from (tblRequisitions) was the
middle table.

eg:
tblAddress (one) to tblRequisitions (many)
tblRequisitions (one) to tblRequisitionsChild (many)

When John mentioned that a simple delete will only delete the record on the
"many" side of the relationship, at first I only considered the "many" table
in the top relationship, which was the one that I wanted to delete in the
first place. But when I realized that the record in the "many" table of the
bottom relationship was the one that was actually getting deleted, then I
removed tblRequisitionsChild from the query. That solved the problem. Now
when I delete a record from form view, the right record is deleted.

Having tblRequisitionsChild in the query was a design flaw anyway. I didn't
need to include it there because the subform on the form was based on
tblRequisitionsChild already.

Thanks again,
Shannon
 
Back
Top