Access 97 - Deleting Records via Button

[Russ2204]

Urgent help needed please.

I have a form which has a button on which when clicked I would like it to delete the record and all records linked to it.

e.g. We have a field called file ref. When you enter the name in it brings up all the records under that file ref. The form is also linked to other tables which have records relating to the file ref. When the button is clicked I would like it to delete all records under that file ref. including any records under that file ref in other tables.

How do I do this?

 

[outsourceoncall]

You need to go to relationships and set referential integrity to cascade delete on the relationship between the Main Form and the details form.

Example Northwind...if you were to delete an order you would delete all of the order details also.

The button is not an issue, once you have the relationships set, a delete Record button(on the mothership) should bang the whole thing.

 

[Russ2204]

This is to affect multiple forms/tables - how do I do this?

 

[WildHare]

Like OutSourceOneCall said, create the relationship between your tables FIRST, and set the CASCADE DELETES option to YES.

Any forms that reference the dataset represented by this join will be able to perform deletes.

Warnings :

1) Deleted records are difficult to recover when the user realizes he made an "oopsy" - there is no "Access Recycle Bin".

2) Cascaded Deletes are a powerful but potentially dangerous feature of referential integrity. The delete of ONE parent record could delete literally thousands of child records which you/your user MAY not want to do.

3) I've been building database apps for over 20 years and can count the number of times I gave a user the interactive ability to DELETE a record on the fingers of one hand, or less.

Hint: Develop a process to mark a record as "logically" deleted (inactive, removed, whatever) but don't physically delete it. Or move it to an archive table.

There's almost NO good reason to actually do a DELETE in an operational database, when it has no real benefit, and potentially serious drawbacks.

My .02 worth. Jim Hare
"Remember, you're unique - just like everyone else"

http://www.jmhare.com

 

[hceonetman]

I agree with Jim Hare - Don't give your users that kind of power. I'm also currently working in an app where we want to be able to delete employees, but only after moving them to an archive file. My Access programming skills are still being developed, however. I'd like to know how to start with a user-supplied ID number, which I would then use in a series of update queries to move the records to archive tables. I would then either use the cascade delete feature, or use separate delete queries to remove records from the main tables.

Can this be done within a macro, or does it need to be a VB module?

Thanks,
HCEONETMAN

Also, does anyone know of a good 'canned' delete routine, maybe in a sample database with related tables (don't say 'Northwind'). I borrow code whenever possible, and only write when necessary.

 

[james33]

Dear Russ2204 ,
Just use a Make Table or Append qry and then a Delete qry
If you choose the Fields from all the tables & criteria first
and then convert it to 2 Different types of qry you can use VB to run
1. The Append or MakeTable Qry
and then
2. the Delete Qry

It may be slightly more complicated as you will be referring to several tables in the queries but at least you have a record of what was deleted.
A Message Box to warn users of the non-go-backableness!!! (wow did I really use that term) of their action
May deter them from casually committing themselves.

But I wouldn't bet on it

HTH
Regards Jim