Advice on Deleting records 1

[MinnKota]

Having read some posts here, I have decided to rarely (if ever) delete records. Instead, I will move them to a table that stores these deleted records.

What is the simplest way to do this? It will be run on the click event of a button on my form.

 

[scriverb]

One way is to create an action APPEND query with your criteria for deleting records and execute that from behind your button. Then immediately run a delete query that deletes the records from the table. The trick here is to determine from a query what the best way is idetentify your records to delete. You will have to provide more information on table names, fields, criteria and I can help with creating the necessary queries.

If your users are identifying the records to be deleted then you can add an additional Boolean field to your table and display the field as a checkbox on your data entry/edit form. When the users decides to delete a record for whatever the criteria a simple check of the checkbox sets it up for your batch delete process at a later time.

Now, you can however just leave them where they are and with use of a Boolean field idetnify them as INACTIVE. This way they stay in the same table, you can use a query in the form to eliminate them for displaying purposes, you can select on the field for True to see old records, and you can change them back to active by a simple check of the box again.

This works quite well in many instances. Post back with the method that you would like to use and I can help you set it up.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[mph1]

I agree with your position about deleting records. If I have a record that is no longer needed, I have a radio button or some other method to indicate that this record is "Past" or something to that effect. I do not create another table to store these records. By doing so would create redundancy. By clicking on the radio button, the record becomes flagged. For the reports that I would need, I just put something in the Criteria of the query under that particular field so it does not show. By the same token, if you would like a report listing only those records that were "Past", create a new query and change the criteria of the field.



HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[MinnKota]

One reason why it may be nice to actually move the records is for simplicity in future development. I don't want to have to always be differentiating between active and nonactive record when I design new queries.

 

[scriverb]

Will you ever have reason to reactivate a record? Or, display the deleted records? Or, print a report of old deleted records? Well, the method of active vs inactive with it's requirement to include the criteria selection in future development of queries is relatively small as compared to having to write queries and processes to delete and undelete, print special reports from a different table, provide a form for looking up deleted records, etc.

I guess it is up to you to weigh these issues. If you are just going to keep them in another just in case you need to look something very rarely at a later time then it makes sense. You can always combine the two tables in a UNION query to make a single recordset. This is fairly easy to do since both tables will be identical.

Six of one, 12 of the other??? Your choice. Hope we haven't muddied the waters here for you. Just trying to give you all of the issues that I have had to consider when I design tables and processes.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[MinnKota]

I have really appreciated the advice. I hadn't thought of your method, and I may not use it in this situation, but I will most certainly use it in the future.

 

[scriverb]

MinnKota: Just a little more food for thought. I have used this Active/Inactive flagging method quite a bit. A real nice effect can be had by using a query to populate your form for add/editing your Active records. I have added a Command button to this form that has a caption which says "Change to InActive Records". When the user has a need to look at old records(Inactive) then I change the RecordSource for the form to a query that selects only the Inactives. I lock all the control fields to keep them from being modified and only allow the user to select from a combobox the record identifier which refreshes the data to the picked record. The only control that they are allowed to change is the Active/Inactive Boolean checkbox control. This is only done if it makes sense to allow the old records to be made active again. The command button when clicked to bring in the Inactive records has its caption changed to read "Change to Active Records". I also make visible a red box which is placed behind the main control that identifies the record but is bigger so that the control is surrounded by RED border. This done so that the user remembers that these are Inactive Records. On the reverse I unlock the controls, change the Recordsource back to the query that selects only the Actives, make the Red Box invisible, and change the caption of the command button.

That's how I use this technique. Just thought you could think about it maybe even expand on it in your situation.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[TheAceMan1]

How are ya MinnKota . . . . . .

Extremely important area here! Especially where accidental deletions are concerned!. All designers would do well to give this area extreme attention, for it this area that can undo all our work and send any company crashing to the ground.

If information is vital as in any business, I don't allow deletions. I have a popup that informs the user to contact adminstration for this. Its a pain and it pisses off the users, but its saved a great many accounts & such over the years. From accidential deletions to people who are pissed off at the company and seek to do harm. For me its the same as [purple]An Ounce Of Prevention Is Worth A Pound Of Cure[/purple].

In parallel with my buddy [blue]scriverb's[/blue] suggestion, I rely on backups for any lost data. This way the database grows no larger than it has to.

See Ya! . . . . . .

 

[scriverb]

AceMan: How are you this morning. Great advise here. Backup, Backup, and Backup. Oh, and don't allow deletion of records.

By the way could you send me an email (see my profile) please.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[hymn]

another way is place the code below on a button which removes a record from one table and moves to another table via a form but you could amend

Private Sub Command39_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 1, , acMenuVer70
DoCmd.OpenForm "frmDeletedPerson", , , acNew
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "frmDeletedPerson"
End Sub

dont forget to change the form names

Hope this helps
Hymn