468,765 Members | 1,067 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Delete record doesn't work

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
3 3203
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
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
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.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
4 posts views Thread by Susan Bricker | last post: by
4 posts views Thread by drakuu | last post: by
1 post views Thread by Swinky | last post: by
reply views Thread by Big Daddy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.