Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    55

    Unanswered: Copying a record before it is deleted

    Hi! I have created a microsoft access database and I have a form that is used to delete records that are not needed.

    What I want to do is when the user clicks on the delete button the record is copied or save to another table before it is actually deleted from the actual table the form uses to get the record.

    Something like coping a record from one table to another.

    I know what I want to do but the problem is I have no idea on how to do it!

    Can you help me please. Thanks very much.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Short answer: You can't.

    Long Answer: Use unbound controls and manually perform the delete. You cannot "trap" the delete as it happens because Access does not support table-level triggers. However, you can say "Click this here button to delete", then when said button is clicked, copy the record to your historical table, THEN manually delete.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2005
    Posts
    39
    Since you are not actually deleting the record, why not insert an attribute called show and default it to true. Modify your form to return only records with show = true (or yes or -1) and change the form's delete button to update show to false (or no or 0). Then refresh or repaint the form after every "delete".

    Alternatively, just before the form loads, have the original table copied as tblArchive. Then afterwards before the db closes, compare the original table with tblArchive and select the unmatched (i.e., deleted) records into tblDeletedHistory?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't see why you cant have a button which when clicked, runs a query (or vba) to copy the current record to another table and then delete the record from the main table with a confirmation that it's deleting it from the source. A simply requery (or refresh) command after the delete will refresh the current recordset. Maybe I'm missing something but this seems reather easy to do.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry Teddy, pkstormy is correct. It's as simple as setting up an Append Query, adding the record to a DeletedRecordTable, then deleting the record from the original table. Taurus' idea is valid too, especially if you may have aneed to make the record active at some point in the future, as I have in some apps.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Oct 2005
    Posts
    55
    Thanks very much teddy,taurus, pkstormy and missingling. But I still do not know how to do the task. Can any of you guide me as how to start!

    Yes I am really lost on this one.

    Thanks again!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Step 1 - Make an append query which will append the same record source on your form (i.e. same table) to another table (i.e. DeleteLogTable). Make sure the criteria for your primary key column is something like = Forms!MyForm!MyPrimaryKeyField so you're only grabbing that one record on the form. Match the fields on the append query to the fields of the table your appending to - DeleteLogTable (i.e. easiest way to create your DeleteLogTable is to clone your main table - delete all the records in it, or copy and paste the table, etc....).

    Step 2. Make your delete query which is basically the same thing as the append query but just make it a delete query (i.e. use the same criteria as mentioned above for the primary key).

    Step 3. On the "delete" button on your form, run the append query, then the delete query (i.e. on the OnClick event of the "delete" button:
    docmd.openquery "MyAppendQuery"
    docmd.openquery "MyDeleteQuery"
    Forms!MyForm.requery

    If you don't want the warnings to show then
    docmd.setwarnings false
    docmd.openquery "MyAppendQuery"
    docmd.openquery "MyDeleteQuery"
    docmd.setwarnings true
    Forms!MyForm.requery

    Personally, if it's data you really don't want in the table (i.e. make it easier for totals, reports, etc.), then I'd get rid of it, otherwise I'd go with taurus's idea about possibly putting in a true/false field indicating that the record is "invalid" but keep in mind you'll need to consider that field in any queries and other stuff where you only want to show "active" records. In one sense it could make it easier for your reports, queries, etc. and on the other hand it could make it more difficult. Something you have to decide depending on how the program works and the deleted records are used (hopefully it's not just because of space.)
    Last edited by pkstormy; 09-24-06 at 00:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Sample Delete System

    Here's a very rough/quick sample of my previous post.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •