Archiving Records

[anshaik]

Hi everyone,

(Working in Access 2003)

Just began thinking about this, hopefully someone can expedite this process for me. I have a set list of data, stored in a table, say called "Table1." Users are able to scan through this data by a form that I have created. I have another table, called "Table1_Archive." Now here's my task:

What I want to do is, using SQL statements, or whatever other method, whenever the user clicks the button "Delete Record" on the form, the record will be deleted from "Table1" and be placed in the "Table1_Archive."

My thought process in creating this second table is that whenever the delete button is clicked, SQL will be processed in which all the data from the current record will be appended to this other table, and then SQL to delete the record from the original table will be executed. Actually writing this code is giving me issues right now.

Thanks for any help

 

[pbaldy]

Generally speaking it's better to leave data in one table and use an "archived" or status field to note which records are archived. That let's you easily query archived records, regular records or all records with a simple query criteria. If you really want to do it, just create an append query and a delete query that use your form for a criteria, and execute them from your button.

 

[anshaik]

Generally speaking it's better to leave data in one table and use an "archived" or status field to note which records are archived. That let's you easily query archived records, regular records or all records with a simple query criteria. If you really want to do it, just create an append query and a delete query that use your form for a criteria, and execute them from your button.

That sounds like a better idea for me, how do I add this archived status field? I assume I add a column within my previous "Table1" and add a "Archived" column? Or how does it work?

Also, when I have this archived button depressed, or whenever the user deletes a certain record, in the form would it still be displayed when a user is scrolling through the data? If yes, how would I hide all the "archived" data from the user?

 

[pbaldy]

Yes, you would go into table design and add the new field (a Yes/No field if that's going to be your only status). You'd add that field to your form so the user could check it. If the form is only supposed to show active records, you could base it on a query that only displayed active records. You could requery the form in the after update event of that checkbox, which would cause the newly checked record to fall off the form.

 

[ChipperT]

If I need to track a record a bit more I will add a Status lookup take with values such as "New", "Updated" and "Deleted". Then the status field in the records table will be a foreign key to the primary key of the Status lookup table. I also include a status_date field in the records table, updated automatically when the status changes and a "changed_by" field updated automatically with the login ID of the user. This small bit of audit trail has saved me more than once.

 

[MSAccessRookie]

That sounds like a better idea for me, how do I add this archived status field? I assume I add a column within my previous "Table1" and add a "Archived" column? Or how does it work?

Also, when I have this archived button depressed, or whenever the user deletes a certain record, in the form would it still be displayed when a user is scrolling through the data? If yes, how would I hide all the "archived" data from the user?

Adding a new Column to indicate Archive status would be one way to do it. The field would probably want to be a True/False Field with a default value of False. Whenever a record is deleted, instead of removing the record, you can set the value of the Flag to True.

One more thing. Your original Query will also need to be modified to display records only if this new field has a value of False.

 

[anshaik]

Ok, I have the archive column set up, along with a "delete" box on the form. I'm having some issues with the query--how do I hide the "archived" data from the user?

I want all the "archived" data to not be viewed by the user as soon as the form is open, and I also have an "update" button which requerys the form.

Edit:

I was going to add some code in the Form_Open, which would hide all the "archived" data. Would that be the way to do it, or is there some other easier/more efficient way?

 

[pbaldy]

I would do what I already mentioned; base the form on a query with a criteria that excludes the archived records. Requery the form when you "delete" a record.

 

[anshaik]

Now I have it working like the way you described, I think. I have my form running off a query that hides all the 'archived' data. My form is working perfectly and hides all the archived files and I am able to update the data to hide all the newly checked archives. The new issue I'm having is this:

Whenever the form is edited by the user to "Archive" a specific record, the query does not receive this information. How can I edit my code for the query I made to also update whichever record has been checked as archived?

I am doing this because I want to create another form which shows all the archived records and also the date/user which archived the file.

Also, thanks for all the help so far!! you all have been helping immensely!!

 

[pbaldy]

As I mentioned, in the after update event of whatever control the user changes to archive the record, requery the form (this is VBA code):

Me.Requery

Since the changed record will now not meet the query criteria, it should no longer be displayed.

 

[anshaik]

That part is working fine-- I am performing the requery. The form itself does remove the newly archived record, it is no longer displayed in the form.

My problem is the query itself--the one that the form is based on. It is not updating itself with that specific record to be checked off as archived.

Edit:

Ahh I just realized I was being dumb. I figured out my problem. On my new form I was referencing to the query that only showed valid records, when I was wanting to create a new query which would query the archived records from the original table. Mind blanked for a little bit there.

 

[pbaldy]

I guess I don't understand. If the form is based on the query, they would display the same records. I can't see how the query would display a record that the form does not. Can you post the db?

 

[anshaik]

Sorry about that, your comment made me realize my mistake. Edited my earlier post, figured out the issue.

 

[pbaldy]

Ah good, glad you got it sorted out.