delete record | datasheet view | multiple tables

[JK]

I'm using a database I created using the service call Mgt template.

The main form displays the customer's contact information and then there is
a datasheet view (subform) of the workorders that have been issued for that
account.

I want to give the db user the ability to delete a specific workorder.
There are many ways im sure this could be done. I'm looking for your advice
on the best way.

Query? New form where the user can enter the workorder ID and click delete?
I'm a little rusty w/MS Access; I could use a point in the right direction.

Thx.

 

[Allen Browne]

So the user should be able to select a record (work order) in the main form,
and all the line items for the work order in the subform should
automatically be deleted too? You can do that with a cascading delete.

1. Open the Relationships Window (Tools menu.)

2. Add both your tables (unless already there.)

3. If you do not see a line joining the 2 tables, drag the primary key of
the WorkOrder table and drop it onto the matching field in the
WorkOrderDetail table: Access opens the Create Relation dialog. If you do
have the join line already, double-click it: Access opens the Edit Relation
dialog.

4. In the dialog, check the boxes for Referential Integrity and for
Cascading Deletes. (If the key field is an AutoNumber it can't be updated,
so cascading updates will not apply.)

5. Click Ok to apply this. If you get a message saying existing data
prevents this relation, cancel. Use the Unmatched Query wizard to locate and
remove the data in the WorkOrderDetail table that doesn't match the primary
key value of the main table. Then return and create the relation again.

Once you have the relation with cascading delete, you can delete the record
in the main form (e.g. use the Toolbar button for delete, or Delete Record
on the Edit menu, or use the command button wizard if you want a button on
the form.)

When you delete a record in your main form, the confirmation dialog will
mention "cascading deletes in other tables", and the subform records will be
deleted as well.