472,789 Members | 1,180 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,789 software developers and data experts.

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 2487

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Steve | last post by:
I have several pairs of synchronized subforms in an application. I have a Delete button for each pair that uses the following code or similar to delete a record in the second subform: ...
2
by: Paul Malcomson | last post by:
Hi. I'm having terrible trouble with a form that displays several parent/child relationships at one time. It is a sales force hierarchy - Sales force, district, territory, sales rep are the...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
3
by: matthewemiclea | last post by:
So I think this may be a stretch, but I have a subform that is displayed on a main form. The subform is based on a query that brings records from a table(It does not bring all fields, but the main...
13
by: Lee | last post by:
Hello All, First of all I would like to say thank you for all of the help I have received here. I have been teaching myself Access for about 4 years now and I've always been able to find a...
6
by: MLH | last post by:
When the vehicle entry form (frmVehicleEntryForm) first opens, the additional owner SubForm control (frmAddnlOwnrListSubForm) is enabled. You can click on it and it will accept the focus. But after...
18
by: Robert Jacobs | last post by:
Please advise... I currently have a Mainform (Customers) and a Subform (Service Requests) with a one to many relationship (one customer, many service requests) with a CustomerID that is unique...
10
beacon
by: beacon | last post by:
Hi everybody, This is probably going to sound unorthodox, but I have to log records that are deleted...I know, go figure. Anyway, I have a form with a (continuous) subform, and on the subform I...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just canít get any where on. My databse mostly includes...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.