🚀 go-pugleaf

Robert
You are probably aware of this but it is best to say it.
In a 'One to Many' relationship, in your case I guess the one side is the
table CustList and the many side is the table CustOrders.  If you wish to
delete a record in table CustList and it has related CustOrders then these
records must be deleted or else they would become orphans - this is not
possible.  So the relationship between CustList and CustOrders must have
CascadeDeletes active.  You can see this by checking the properties of this
relationship in MS Access.

Now by doing an INNER join between the two tables then you will only get the
records in thew CustList table that have one or more records in the table
CustOrders.  DISTINCTROW is an MS Access extension keyword that allows the
recordset to able to be edited where it may normally not, don't think you
need it here.

It is also not a good idea to do the following in the SELECT clause
CustOrder.*, best to spell out the actual fields you want to see in the
result set.  Also it is best to have the primary keys present in the result
set, the primary keys from all tables may be necessary.

The other problem is that with ADO you will need to identify the unique
table if you only want to delete a record from the CustOrders table.  Unique
table is a property of the recordset.

I would check to see if you have CascadeDeletes turned on.

Hope some of this helps..
Daryl


"Robert Emmons" <remmons@aurigen.com> wrote in message
news:am4suvgp2h473hlagl3ur1jimaamfdi7u5@4ax.com...
> I have a query which shows Customer names and orders in a result
> table:
>
> SELECT DISTINCTROW CustList.Name, CustList.ID, CustOrders.*
> FROM CustList INNER JOIN CustOrders ON CustList.ID = CustOrders.ID
> ORDER BY CustList.Name;
>
> If I run the query in Microsoft Access, I can delete any row from the
> CustOrders table by selecting the corresponding query row, and hitting
> the <Del> key.
>
> However, the deletion does not work the same in a TADOQuery result
> set.  If I try to delete a row, it tries to delete the corresponding
> rows in *both* the CustOrders *and* CustList tables.  This generally
> results in an error message: "The record cannot be deleted or changed
> because table CustOrder includes related records."
>
> Can I force the delete to apply only to the CustOrders table as it
> seems to in Microsoft Access?   Alternatively, can I trap the error in
> the "BeforeDelete" TADOQuery event, and execute my own delete query?
>