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

On Nov 27, 2:17 pm, "Phil Stanton" <p... (AT) stantonfamily (DOT) co.uk> wrote:
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.

On Nov 28, 6:17 am, "Phil Stanton" <p... (AT) stantonfamily (DOT) co.uk> wrote:
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.

Thanks Helen

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

Thanks a lot

Phil


<helenwheelss (AT) yahoo (DOT) com.au> wrote