deelet from 2 tables

[Guest]

Hi,
I have the following code behind a button:

DoCmd.SetWarnings True
DoCmd.RunSQL "DELETE FROM fy02_basetable WHERE (SELECT orderno FROM
estimates WHERE cmborderno = [forms]![estimates]![cmborderno]);"


I am hoping for users to be able to delete a record from one table, while
also deleting a record in another table with the same orderno value. Does
anyone know how I can achueve this?

Thanks in advance
geebee

 

[peregenem]

I am hoping for users to be able to delete a record from one table, while
also deleting a record in another table with the same orderno value. Does
anyone know how I can achueve this?

Use DRI (declarative referential integrity), specifically by cascading
the delete. The DDL (data definition language) for this would be (for
the referencing table), this example taken from the Northwind example
database

CREATE TABLE [Order Details] (
OrderID INTEGER NULL
CONSTRAINT [OrdersOrder Details] REFERENCES Orders (OrderID)
ON DELETE CASCADE
ON UPDATE NO ACTION, ...)

 

[Guest]

DELETE only works on one table at a time. If the two tables from which you
wish to delete records are related- which they must be, otherwise how do you
identify which records to delete from the second table?- then you can set the
'Cascade Delete Related Records' property on the relation so that deleting a
record from the table on the '1' side of the relationship will deletion all
the related rows from the table on the 'many' side of the relationship.

Good Luck!

 

[Van T. Dinh]

Enforce Referential Integrity between the One Table and the Many Table with
the option Cascade Delete. With this, when you delete the One Record, the
Many Records will be automatically deleted also.

However, use this with caution as it is too easy to delete ...