Deleted Records Table

[BillD]

I have a database with approx. 10,000 records. I am constantly adding and
deleting records. When I am in form view and have a record showing, I can
delete the record by clicking on the delete button on the toolbar. Instead of
deleting the record completely, I would like to append the record in a
Deleted Records table with a date and time stamp. This way I can review the
records that have been deleted at any time. Also, if someone deleted a record
that they should not have deleted, I could append the record back to the Main
RecordsTable.
Any help greatly appreciated.
Bill D.

 

[Duane Hookom]

You might want to add a couple fields to the table for status (deleted or
active) and the last date/time a record changed status.

 

[BillD]

Thanks for the reply.
I could add the fields you suggest, but this still leaves the record in the
table and the record would still show up on my " Edit /View Records Form".
The record would still show up in the Edit/View Records Form and could be
edited. For one Example- If I have a person who was deceased or moved from
the Constituency, then I do not want that record to show up in reports or
especially have someone call the deceased persons home asking for the
deceased person or send mail to the deceased person. The record would still
show up under the " Edit /View Records Form" Someone might not notice that
the person's name on the active form was deceased.
Perhaps there is anothewr easy way to solve this problem rather than
creating a table for the deleted records.

Bill D.

 

[Duane Hookom]

Your forms and reports only need to display what you want. You can set a
criteria for any of these based on the value of the status field.

If this doesn't work, you could append the record to a copy of your current
table. Add of date/time field and set its default to Now(). Then delete any
record from your current table that is in the deleted records table.

 

[BillD]

I think that this is what I was looking for. I could create a Copy of the
main Table which is named "VoterInformationTable" and name the new table
"DeletedRecordsTable". I could create aappend query that would append any
record from the "VoterInformationTable" marked delete into the new Table.
After the record was appended to the new table "Deleted RecordsTable", then I
could run a Delete Query.

Could I create both queries to run from a buton double-click event on the
Main Form?

Thanks again for the help.
Bill D.

 

[Duane Hookom]

You could use a command button to run a couple action queries. I don't know
how you want to specify which record to "delete" (append to the other table).
Once the record is in the other table, you could use a delete query like:

DELETE tblMain.*
FROM tblMain
WHERE PrimaryKeyField IN (SELECT PrimaryKeyField from tblMainDeleted);

 

[BillD]

When I am in the main form, the record showing would be the record that I
would want to delete.
I would like to have a button on the form to append the record to a new
DeletedTable and then delete the record.

 

[Duane Hookom]

I would use code like:
Dim strSQL as String
Dim db as DAO.Database
Set db = Currentdb
strSQL = "INSERT INTO tblMainDeleted(...field list....) " & _
"SELECT ... field list.... FROM tblMain " & _
"WHERE PrimaryKeyField =" & Me.PrimaryKeyField
db.Execute strSQL, dbFailOnError
strSQL = "DELETE tblMain.* FROM tblMain " & _
"WHERE PrimaryKeyField IN (SELECT PrimaryKeyField FROM tblMainDeleted"
db.Execute strSQL, dbFailOnError
Set db = Nothing

 

[BillD]

Duane:
Thanks for the code.
As I am not a programmer, I do not totally understand the purpose of the
code. Would this code be put in the Button Event procedure. Is it a delete
query?
The steps that I would like to preform are as follows:
Set up a field in the "VoterInformationTable" called "RecordStatus". I
would have a source of "Deceased" or "Moved" for this field. This text field
would be on the Edit/view records form. I could have the field marked either
"deceased" or "moved" for a record that I want to append to a copy of the
"VoterInformationTable" and then delete the record from the
"VoterInformationTable".
If this sounds like the best proper procedure, would you list the steps
that should be performed in order to accomplish the end result of deleting
the record from the "VoterInformationTable".

 

[Duane Hookom]

You stated "the record showing would be the record that I would want to
delete". The code I provided would be in the On Click event of a command
button on the form. The table and field names from your MDB would need to be
inserted where I have used bogus table and field names.

I think you would need a line of code at the begin to save the current
record and a line of code at the end to requery the form since it is showing
a record that will be deleted.
Me.Dirty = False
Dim strSQL as String
'.....
'.....
Set db = Nothing
Me.Requery

 

[BillD]

Duane:
Thanks again for the excellent detailed help.
I have looked over your code and hope to install in the database later today
or tomorrow. I may have another question if I run into problems.
I have checked your last reply as answering my question.

Thanks again,
Bill D.