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.