By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Delete record doesn't work

P: n/a
I have a form based on a complex query (Lots of tables)

If I delete a record, everything appears to be OK. Get the message "Youa are
about to delete 1 record ....". I say yes. The record count goes down
correctly. Then if I do Records-->Remove Filter/Sort there are the deleted
records back as if they had never been deleted. Same thin exactly if I do
the operation on the form's recordsource query, so that eliminates anything
to do with CancelEvent on the form. Where should I start looking

Thanks

Phil
Nov 27 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Nov 27, 2:17 pm, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
I have a form based on a complex query (Lots of tables)

If I delete a record, everything appears to be OK. Get the message "Youa are
about to delete 1 record ....". I say yes. The record count goes down
correctly. Then if I do Records-->Remove Filter/Sort there are the deleted
records back as if they had never been deleted. Same thin exactly if I do
the operation on the form's recordsource query, so that eliminates anything
to do with CancelEvent on the form. Where should I start looking

Thanks

Phil
Maybe your doing the delete on a "snapshot" query? Sorry, that
doesn't exactly answer your question, but maybe points you in the
right direction. I can't think of why else a record could actually be
deleted, but NOT REALLY be deleted.
Nov 27 '07 #2

P: n/a
On Nov 28, 6:17 am, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
I have a form based on a complex query (Lots of tables)

If I delete a record, everything appears to be OK. Get the message "Youa are
about to delete 1 record ....". I say yes. The record count goes down
correctly. Then if I do Records-->Remove Filter/Sort there are the deleted
records back as if they had never been deleted. Same thin exactly if I do
the operation on the form's recordsource query, so that eliminates anything
to do with CancelEvent on the form. Where should I start looking

Thanks

Phil
You mention that your query is based on more than one table. If your
query has a left join somewhere, I suspect what's happening is that
the delete is only removing the record on the "right" table in that
join and not removing the matching record from both tables as you
might have expected.

If the record in the "left" table wasn't deleted, when you requery,
the number of records will indicate nothing was deleted, although if
you check the fields in your query from the "right" table you'll find
they're blank because there's no longer a matching record there.

So, check all the tables used in your query and see if the delete is
removing rows from some and not others.

If that's what's happening, and you're absolutely sure you want your
query to delete the record from the "left" table as well, setting the
query's recordsettype to "Dynaset (Inconsistent Updates)" may help. Be
careful and test this on a copy of your data - when you delete rows in
joined table queries it's _very_ easy to delete rows from tables you
didn't intend to. (sorry if the warning's unnecessary, can't be too
safe eh?)

If that doesn't help you, you may want to post the SQL of your query -
that might give us more clues.
Nov 28 '07 #3

P: n/a
Thanks Helen

Spot on. Removed the left join and used an Elookup (Fast version of DLookup)
and problem solved

Thanks a lot

Phil
<he**********@yahoo.com.auwrote in message
news:9a**********************************@b40g2000 prf.googlegroups.com...
On Nov 28, 6:17 am, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
>I have a form based on a complex query (Lots of tables)

If I delete a record, everything appears to be OK. Get the message "Youa
are
about to delete 1 record ....". I say yes. The record count goes down
correctly. Then if I do Records-->Remove Filter/Sort there are the
deleted
records back as if they had never been deleted. Same thin exactly if I do
the operation on the form's recordsource query, so that eliminates
anything
to do with CancelEvent on the form. Where should I start looking

Thanks

Phil

You mention that your query is based on more than one table. If your
query has a left join somewhere, I suspect what's happening is that
the delete is only removing the record on the "right" table in that
join and not removing the matching record from both tables as you
might have expected.

If the record in the "left" table wasn't deleted, when you requery,
the number of records will indicate nothing was deleted, although if
you check the fields in your query from the "right" table you'll find
they're blank because there's no longer a matching record there.

So, check all the tables used in your query and see if the delete is
removing rows from some and not others.

If that's what's happening, and you're absolutely sure you want your
query to delete the record from the "left" table as well, setting the
query's recordsettype to "Dynaset (Inconsistent Updates)" may help. Be
careful and test this on a copy of your data - when you delete rows in
joined table queries it's _very_ easy to delete rows from tables you
didn't intend to. (sorry if the warning's unnecessary, can't be too
safe eh?)

If that doesn't help you, you may want to post the SQL of your query -
that might give us more clues.


Nov 28 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.