Delete action in subform trying to delete multiple records

[Jeff]

In a subform I have a simple SQL statement that links an order details table
to a product table. The form is used to enter order details. Most fields are
obviously from the details table, with only a UOM field from the product
table.

This has worked fine for years, I thought.

The problem I seem to have is that if the user makes a mistake in putting a
product in the order they then naturally want to delete the line out of the
order. However, when they try this they now get a message that the detail
record can't be deleted due to related records in the 'Production' table.
The production table isn't even in the query statement.

I assume that besides deleting the detail record it is also trying to delete
the product record which is linked to the production table.

I have tried to find info about how Access determines what to delete in a
query where there is more than one table involved. Which table does it
delete from?

The confusing bit is that I am sure this problem didn't exist before as I
have had no reports from users and have not modified this area recently.
Maybe users just didn't report this issue and worked around it...!!

Anyway, any help appreciated.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

[deluxeinformation]

Jeff wrote:

In a subform I have a simple SQL statement that links an order details table
to a product table. The form is used to enter order details. Most fields are
obviously from the details table, with only a UOM field from the product
table.

This has worked fine for years, I thought.

The problem I seem to have is that if the user makes a mistake in putting a
product in the order they then naturally want to delete the line out of the
order. However, when they try this they now get a message that the detail
record can't be deleted due to related records in the 'Production' table.
The production table isn't even in the query statement.

I assume that besides deleting the detail record it is also trying to delete
the product record which is linked to the production table.

I have tried to find info about how Access determines what to delete in a
query where there is more than one table involved. Which table does it
delete from?

The confusing bit is that I am sure this problem didn't exist before as I
have had no reports from users and have not modified this area recently.
Maybe users just didn't report this issue and worked around it...!!

Anyway, any help appreciated.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

Perhaps someone established a relationship between the two tables.
Click on Tools, Relationships and see if there is a relationship
defined between the two tables. If so, double-click on the line
joining them to bring up the 'Edit relationships' dialog. Here one can
specify whether or not Access automatically deletes related records.

Bruce

[Jeff]

Not possible. I work in an isolated environment. And relationships are
impossible because these two tables are linked from different back-end MDBs.

Thanks anyway
Jeff

<de***************@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>
Jeff wrote:

>In a subform I have a simple SQL statement that links an order details
table
to a product table. The form is used to enter order details. Most fields
are
obviously from the details table, with only a UOM field from the product
table.

This has worked fine for years, I thought.

The problem I seem to have is that if the user makes a mistake in putting
a
product in the order they then naturally want to delete the line out of
the
order. However, when they try this they now get a message that the detail
record can't be deleted due to related records in the 'Production' table.
The production table isn't even in the query statement.

I assume that besides deleting the detail record it is also trying to
delete
the product record which is linked to the production table.

I have tried to find info about how Access determines what to delete in a
query where there is more than one table involved. Which table does it
delete from?

The confusing bit is that I am sure this problem didn't exist before as I
have had no reports from users and have not modified this area recently.
Maybe users just didn't report this issue and worked around it...!!

Anyway, any help appreciated.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

Perhaps someone established a relationship between the two tables.
Click on Tools, Relationships and see if there is a relationship
defined between the two tables. If so, double-click on the line
joining them to bring up the 'Edit relationships' dialog. Here one can
specify whether or not Access automatically deletes related records.

Bruce

[deluxeinformation]

Jeff wrote:

Not possible. I work in an isolated environment. And relationships are
impossible because these two tables are linked from different back-end MDBs.

Thanks anyway
Jeff

Can you post the recordsource of the parent form and the subform, and
tell us what the 'link child' and 'link master' fields are on the
subform?

Bruce