How to stop unwanted (cascading) deletes...?

[Randall Arnold]

I have an Access 2003 database designed for a plumbing operation. There is
nothing 2003-specific involved in my question so hopefully someone can
answer this.

The main table (Jobs) uses an autonumber field [Job ID] to keep track of
jobs. Each job can have any or all of 4 types of tasks, two unique
(one-to-one) and two recurring (one-to-many). The task queries link the
values of several tables together on key fields; [Job ID] is the central key
to everything.

Everything works as it should so far except one function: if I delete tasks
from any table that has a one-to-one relationship with the Jobs table, the
Job is also deleted. This is NOT desireable. Note that deleting tasks from
tables with a one-to-many relationship does NOT result in deleting the
associated Job, even if I delete every associated task. This has me
stumped! Is it the one-to-one relationship that's to blame? If so, why and
what's the solution?

Except for that nature of the relationships, the queries are constructed the
same. I disabled "enforce referential integrity" on the one-to-one
relationships hoping that would solve the problem-- there was no noticeable
effect.

This is a real problem for my client. I am determined to solve this by
week's end... can anyone point out the source of my stupidity on this one?
I'm sure it's something simple I'm overlooking...

Thanks all,

Randall Arnold

 

[Allen Browne]

If you have already disabled the cascading deletes in the Relationships
window, then something else is at work here.

Where are the deletions occurring? Is it in a form where the RecordSource is
a query that combines both tables? If so, what happens if you delete a
record directly in the query? Is it possible to base the form directly on
one table or the other as a way to solve this?

 

[Randall Arnold]

I'll focus on one of the offending queries.

It includes the Jobs table, as well as a task table, along with various
supplemental tables, all joined via primary-to-foreign keys (different in
each table). The Jobs table is only used to pull in a few fields pertinent
to the results (house location, etc). The Job data is for reference only in
*this* case and not meant to be edited in any way by the query or the form
in question.

A form is opened by the user that allows him to navigate through the tasks,
and edit fields specific to the task table *only*; other fields are either
omitted or locked. The [Job ID] is shown for reference. The user may also
add tasks, print tasks, and/or delete tasks. The delete function uses the
menu bar call created by the button wizard.

As noted previously, deleting a task from a form that is linked to a query
utlizing a one-to-many link between the task table and Job table has no
problems: pressing Delete wipes the record from the task queue (table) while
leaving it inviolate in the Jobs table. Only records created via one-to-one
relationships are deleted from both the tasks table (as they should be) AND
from the Jobs table (as they should NOT).

If I delete straight from the task query, it is also wrongfully deleted from
the Jobs table.

Does that help?

Thanks,

Randall Arnold

If you have already disabled the cascading deletes in the Relationships
window, then something else is at work here.

Where are the deletions occurring? Is it in a form where the RecordSource is
a query that combines both tables? If so, what happens if you delete a
record directly in the query? Is it possible to base the form directly on
one table or the other as a way to solve this?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I have an Access 2003 database designed for a plumbing operation. There is
nothing 2003-specific involved in my question so hopefully someone can
answer this.

The main table (Jobs) uses an autonumber field [Job ID] to keep track of
jobs. Each job can have any or all of 4 types of tasks, two unique
(one-to-one) and two recurring (one-to-many). The task queries link the
values of several tables together on key fields; [Job ID] is the central key
to everything.

Everything works as it should so far except one function: if I delete tasks
from any table that has a one-to-one relationship with the Jobs table, the
Job is also deleted. This is NOT desireable. Note that deleting tasks from
tables with a one-to-many relationship does NOT result in deleting the
associated Job, even if I delete every associated task. This has me
stumped! Is it the one-to-one relationship that's to blame? If so, why and
what's the solution?

Except for that nature of the relationships, the queries are constructed the
same. I disabled "enforce referential integrity" on the one-to-one
relationships hoping that would solve the problem-- there was no noticeable
effect.

This is a real problem for my client. I am determined to solve this by
week's end... can anyone point out the source of my stupidity on this one?
I'm sure it's something simple I'm overlooking...

Thanks all,

Randall Arnold

 

[Randall Arnold]

I forgot to respond to one point: I don't know that I can just base the form
on the table alone. I need fields from related tables for the user's
purpose. Using queries is the only way I know to do this.

Randall Arnold

If you have already disabled the cascading deletes in the Relationships
window, then something else is at work here.

Where are the deletions occurring? Is it in a form where the RecordSource is
a query that combines both tables? If so, what happens if you delete a
record directly in the query? Is it possible to base the form directly on
one table or the other as a way to solve this?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I have an Access 2003 database designed for a plumbing operation. There is
nothing 2003-specific involved in my question so hopefully someone can
answer this.

The main table (Jobs) uses an autonumber field [Job ID] to keep track of
jobs. Each job can have any or all of 4 types of tasks, two unique
(one-to-one) and two recurring (one-to-many). The task queries link the
values of several tables together on key fields; [Job ID] is the central key
to everything.

Everything works as it should so far except one function: if I delete tasks
from any table that has a one-to-one relationship with the Jobs table, the
Job is also deleted. This is NOT desireable. Note that deleting tasks from
tables with a one-to-many relationship does NOT result in deleting the
associated Job, even if I delete every associated task. This has me
stumped! Is it the one-to-one relationship that's to blame? If so, why and
what's the solution?

Except for that nature of the relationships, the queries are constructed the
same. I disabled "enforce referential integrity" on the one-to-one
relationships hoping that would solve the problem-- there was no noticeable
effect.

This is a real problem for my client. I am determined to solve this by
week's end... can anyone point out the source of my stupidity on this one?
I'm sure it's something simple I'm overlooking...

Thanks all,

Randall Arnold

 

[Allen Browne]

Okay: that's good trouble-shooting. You now know that it is the query that
is causing the problem, not the form or its code.

I take it that Jobs is the primary table, and Tasks is the related table. If
the fields from the Job table are for display only, could you use text boxes
bound to DLookup() expressions? This would set you free from the need to use
a query as the source for the form.

Alternatively, are you able to change the query in a way that solves this?
For example, is the JobID field from the Job table (the primary key), or
from the Tasks table (the foreign key)? Does switching it so you are using
the foreign key make a difference?

If it's important to retain the query, you could also experiment with the
join type in the query. Double-click the join line, and choose the option
"All records from Tasks, and any matches from Jobs".

If you can get the query working okay, you can expect the form to work
correctly also.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I'll focus on one of the offending queries.

It includes the Jobs table, as well as a task table, along with various
supplemental tables, all joined via primary-to-foreign keys (different in
each table). The Jobs table is only used to pull in a few fields pertinent
to the results (house location, etc). The Job data is for reference only in
*this* case and not meant to be edited in any way by the query or the form
in question.

A form is opened by the user that allows him to navigate through the tasks,
and edit fields specific to the task table *only*; other fields are either
omitted or locked. The [Job ID] is shown for reference. The user may also
add tasks, print tasks, and/or delete tasks. The delete function uses the
menu bar call created by the button wizard.

As noted previously, deleting a task from a form that is linked to a query
utlizing a one-to-many link between the task table and Job table has no
problems: pressing Delete wipes the record from the task queue (table) while
leaving it inviolate in the Jobs table. Only records created via one-to-one
relationships are deleted from both the tasks table (as they should be) AND
from the Jobs table (as they should NOT).

If I delete straight from the task query, it is also wrongfully deleted from
the Jobs table.

Does that help?

Thanks,

Randall Arnold

If you have already disabled the cascading deletes in the Relationships
window, then something else is at work here.

Where are the deletions occurring? Is it in a form where the

RecordSource

is
a query that combines both tables? If so, what happens if you delete a
record directly in the query? Is it possible to base the form directly on
one table or the other as a way to solve this?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I have an Access 2003 database designed for a plumbing operation.

There
is

nothing 2003-specific involved in my question so hopefully someone can
answer this.

The main table (Jobs) uses an autonumber field [Job ID] to keep track of
jobs. Each job can have any or all of 4 types of tasks, two unique
(one-to-one) and two recurring (one-to-many). The task queries link the
values of several tables together on key fields; [Job ID] is the

central
key

to everything.

Everything works as it should so far except one function: if I delete tasks
from any table that has a one-to-one relationship with the Jobs table, the
Job is also deleted. This is NOT desireable. Note that deleting

tasks
from

tables with a one-to-many relationship does NOT result in deleting the
associated Job, even if I delete every associated task. This has me
stumped! Is it the one-to-one relationship that's to blame? If so,

why
and

what's the solution?

Except for that nature of the relationships, the queries are

constructed
the

same. I disabled "enforce referential integrity" on the one-to-one
relationships hoping that would solve the problem-- there was no noticeable
effect.

This is a real problem for my client. I am determined to solve this by
week's end... can anyone point out the source of my stupidity on this one?
I'm sure it's something simple I'm overlooking...

Thanks all,

Randall Arnold

 

[Randall Arnold]

Many thanks for the response, Allen. I'll sure give your suggestions a
shot!

But I'm *still* hung up over why one-to-many queries don't exhibit this
behavior....

Randall Arnold

Okay: that's good trouble-shooting. You now know that it is the query that
is causing the problem, not the form or its code.

I take it that Jobs is the primary table, and Tasks is the related table. If
the fields from the Job table are for display only, could you use text boxes
bound to DLookup() expressions? This would set you free from the need to use
a query as the source for the form.

Alternatively, are you able to change the query in a way that solves this?
For example, is the JobID field from the Job table (the primary key), or
from the Tasks table (the foreign key)? Does switching it so you are using
the foreign key make a difference?

If it's important to retain the query, you could also experiment with the
join type in the query. Double-click the join line, and choose the option
"All records from Tasks, and any matches from Jobs".

If you can get the query working okay, you can expect the form to work
correctly also.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I'll focus on one of the offending queries.

It includes the Jobs table, as well as a task table, along with various
supplemental tables, all joined via primary-to-foreign keys (different in
each table). The Jobs table is only used to pull in a few fields pertinent
to the results (house location, etc). The Job data is for reference

only

in
*this* case and not meant to be edited in any way by the query or the form
in question.

A form is opened by the user that allows him to navigate through the tasks,
and edit fields specific to the task table *only*; other fields are either
omitted or locked. The [Job ID] is shown for reference. The user may also
add tasks, print tasks, and/or delete tasks. The delete function uses the
menu bar call created by the button wizard.

As noted previously, deleting a task from a form that is linked to a query
utlizing a one-to-many link between the task table and Job table has no
problems: pressing Delete wipes the record from the task queue (table) while
leaving it inviolate in the Jobs table. Only records created via one-to-one
relationships are deleted from both the tasks table (as they should be) AND
from the Jobs table (as they should NOT).

If I delete straight from the task query, it is also wrongfully deleted from
the Jobs table.

Does that help?

Thanks,

Randall Arnold

If you have already disabled the cascading deletes in the Relationships
window, then something else is at work here.

Where are the deletions occurring? Is it in a form where the

RecordSource

is
a query that combines both tables? If so, what happens if you delete a
record directly in the query? Is it possible to base the form directly on
one table or the other as a way to solve this?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


I have an Access 2003 database designed for a plumbing operation. There
is
nothing 2003-specific involved in my question so hopefully someone can
answer this.

The main table (Jobs) uses an autonumber field [Job ID] to keep

track

of

jobs. Each job can have any or all of 4 types of tasks, two unique
(one-to-one) and two recurring (one-to-many). The task queries link the
values of several tables together on key fields; [Job ID] is the central
key
to everything.

Everything works as it should so far except one function: if I delete
tasks
from any table that has a one-to-one relationship with the Jobs

table,
the

Job is also deleted. This is NOT desireable. Note that deleting tasks
from
tables with a one-to-many relationship does NOT result in deleting the
associated Job, even if I delete every associated task. This has me
stumped! Is it the one-to-one relationship that's to blame? If so, why
and
what's the solution?

Except for that nature of the relationships, the queries are constructed
the
same. I disabled "enforce referential integrity" on the one-to-one
relationships hoping that would solve the problem-- there was no
noticeable
effect.

This is a real problem for my client. I am determined to solve this by
week's end... can anyone point out the source of my stupidity on

this
one?

I'm sure it's something simple I'm overlooking...

Thanks all,

Randall Arnold