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

Re: Delete record doesn"t work

On Nov 27, 2:17 pm, "Phil Stanton" wrote:
> 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.

Re: Delete record doesn"t work

On Nov 28, 6:17 am, "Phil Stanton" wrote:
> 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.

Re: Delete record doesn"t work

Thanks Helen

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

Thanks a lot

Phil


wrote in message
news:9a738a54-8c66-4900-98ef-60e6cab0dad5@b40g2000prf.google groups.com...
> On Nov 28, 6:17 am, "Phil Stanton" wrote:
>> 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.
>
>