set up the deletion of the records to be written to another table

[ehonwollen]

I have a travel tracker DB that on several occcasions I had to delete records. Then later have detrmine what records were deleted. Currently I keep a paper log but would like to create a table and function to store deleted records. Is there such and animal

 

[gusbrunston]

[tt]
Hi:

You could:

1. Copy the existing table with all data to another table.

2. Design and run a delete query to delete the data from the original table.

3. Design and run a make-table query with selected records from the other table copied in step 1. The make-table destination would be the original table. Now you have a table with only the records you selected (the non-deleted records.) You also have another table with all the original records.

4. Use another make-table query to build another table with only the deleted records. If you need the space or want to keep things tidy, you could then delete the first copied table that still has all the original records in it. Myself, I'd probably keep it on a zip drive or upload it to xDrive, or the appropriate server.

I guess this is just one way to skin this cat. I'll be interested in seeing the ideas of others.

Cheers,[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[webcats]

Hi,

You could create a Tracking table and add a record when you delete anything. Just use the On Delete event of the form (You have to use a form), and put some code in like this:

Dim rs1 As ADODB.Recordset
Dim stUser As String
Dim stDate As String
Dim stAction As String

stUser = fOSUserName() 'This is a bit of code that returns
'the NT userid. Go to '

http://www.mvps.org/access/modules/index.html

to find it.

stDate = Now()

Set rs1 = New ADODB.Recordset
rs1.Open "tblTracking", Application.CurrentProject.Connection, adOpenKeyset, adLockPessimistic

With rs1

.AddNew

!User = stUser
!actDate = Now()
!Action = "*Deleted* Record # " & Me.CustomerID

.Update

End With
Save = True


Hope this helps.


"One Database to rule them all, One Database to find them,
One Database to bring them all and in the darkness bind them."