468,272 Members | 2,146 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Delete action in subform trying to delete multiple records

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
Nov 1 '06 #1
3 2279

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

Nov 2 '06 #2
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

Nov 5 '06 #3

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

Nov 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Steve | last post: by
18 posts views Thread by Robert Jacobs | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.