Recycle Bin 2
By Richard Rost
7 months ago
Send Records to Recycle Bin in MS Access Part 2
In this tutorial, I will show you how to create a Recycle Bin system in Microsoft Access that allows you to save deleted records from any table for future recovery. We will design a Recycle Bin table, set up the code to store deleted record data including the username and deletion date, and update your forms to send records to the Recycle Bin instead of permanently deleting them. This is part 2.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Recycle Bin implementation, Recycle Bin table design, Recycle Bin button code, table-agnostic Recycle Bin, saving deleted records, capturing deleted date, storing username in Recycle Bin, linking record ID, record data as long text, restoring deleted records, customer form Recycle Bin, modular Recycle Bin code, parameterizing Recycle Bin function, event handler for Recycle Bin, deleting records with SQL, user environment variable username, updating and requerying form after deletion
Subscribe to Recycle Bin 2
Get notifications when this page is updated
Transcript
This is part two of my Recycle Bin video series. No, we're not putting Access in the Recycle Bin. We're making a Recycle Bin so we can drop records in there in case you might need them in the future.
This is part two, so if you haven't watched part one yet, go watch that first and then come on back.
Alright, so in part one, we got to this point where we listed all of the fields in our table and their values, and now we've got all this in one string. Now we need to make a table, a Recycle Bin table, to drop this information into so that in the future, if we need it, we can go in there and get it.
So let's create Table Design, and I want to keep this so it's table-agnostic. In other words, I can drop records from any table in here. It doesn't matter.
So it's going to be just a Recycle Bin ID. That's my autonumber.
We're going to put in here a Deleted Date. Alright, what date was the record actually deleted from the table, or Recycled Date if you want to call it that, and that I will default to where my default right there equals now.
Okay, what else do we need in here? How about the username? It would be nice to know who deleted it. I'll show you how to get that in just a minute.
The Table Name - what table was it deleted from?
The Record ID, which will be a number - that'll be the primary key of that table. So if it's Customer ID, it'll be a Customer ID. If it's Contacts, it'll be a Contact ID.
And then of course the Record Data, which can be a lot, including long text fields. So this itself has to be Long Text.
So is this table going to be very optimized for searching? No, not really. You can, but the point of this is you've got a repository where you can go back and find the information if it's deleted and you need it.
Let's save this as my Recycle Bin table primary key. Alright, save it, close it.
Now we're ready to go back into our code. Let's go back to our customer form. Go into the code for this button here.
Alright, so instead of message boxing that record string, we're going to do some stuff. We're going to drop it into that table.
So, set rsBin, our recycle bin, equals db.OpenRecordset. What do we open up? The RecycleBinT.
Okay, rsBin.AddNew, we're going to add a new record. And then when you're done adding a new record, what do you do? rsBin.Update. And then rsBin.Close and set rsBin = Nothing. Alright, close everything up when you're done with it.
Now in the middle here, this is where we put our data in there. Now you can set the deleted date here if you want to. We have it defaulting in the table. That's up to you.
The username, rsBin.username - how do you get that? Well, that's an environment variable. Environment variable username. I have a whole separate video on how that works. Basically, you're getting the username of the person on the computer logged into Windows. It's not 100 percent secure because it can be spoofed or changed, but generally speaking, it's good enough for most office environments.
Here's a video that describes this in a lot more detail if you want to learn more. And of course, if you really want to lock your system down, I've got a whole security seminar. I'll put a link to that down below as well. This thing covers just about everything.
Alright, so we know the username. The rsBin table name is going to be CustomerT. The rsBin record ID is going to be whatever is in the Customer ID field on the form that we're on.
And the rsBin record data, the long text field, is going to be that string we just made, recordString.
Yeah, you can use a With in there if you like to use With. I don't use With that often unless I get really long names I'm dealing with. I think it's just easier to write this way. That's personal preference though.
Now, we're all done with this. We can then delete the record that's on the screen. I like to do it with SQL. That's just my preference.
So I'm going to go - well, we can do it before the db is released - we can say db.Execute "DELETE FROM CustomerT WHERE CustomerID = " & CustomerID on the screen.
And we're all done with all of this. We can close the form or requery the form - whatever you want to do. It's up to you. Let's just requery the form.
It'll put you back at the first record. So we'll say Me because we're on the customer form now: Me.Requery.
Otherwise, you'll be sitting on a deleted record. It'll say Deleted everywhere.
Alright. Let's give it a shot. Save it, debug, compile once in a while. Close it. Open it. Let me delete myself or recycle myself.
You ready? Alright. Are you sure you want to send this record to the Recycle Bin? Yep. Okay. I'm going. Look at that, 1 of 32. Let's check our Recycle Bin. Oh, look - there we have it.
Alright. Deleted Date, Username is amaker. Don't ask - my computer's name is amaker. It's short for amacron. I don't know why they did that. Customer ID or CustomerT ID 1. And then here's the data.
And if we click on that and go - let me do this. Let me make this a little bit bigger. You can see it's all in there. See? There's all that. Whoops, it's too big. Precise.
Okay, there's all the stuff that was in that record.
Let's do another one. Let's find - let's do - oh, wait, Tashi Ar. Let's recycle Tashi Ar. She's gone anyways. Click. Are you sure? Yep. Boom. That's gone, down to 31. Check the Recycle Bin. There she is, right there.
Beautiful. So now you've got a place for all your dead soldiers to go when you recycle them. Alright. Now this is working great.
Okay, but as you'll notice when we built this thing, it's very specific to the customer form. And a lot of places in here we have CustomerT, CustomerID, Customer here. I want to make this so it'll work on any form, so literally all I have to do is drop this button onto a form, change a few parameters.
We'll use an event handler for this. So instead of having code under this button, we'll use a function in here. It's called an event handler function where you can make a function, stick it in here. We'll have to put a few parameters in there.
We'll change the parameters. We'll put the name of the table and the form itself in here, and then it'll work on any form we drop it into.
And we'll cover that in tomorrow's video. So once again, tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now because I'm going to keep recording.
But there you go. That's part two. Now you've got a fully working Recycle Bin for your customer table. And if you're happy with that, you can stop there. Tomorrow we're going to make it more modular, so we can literally drop that button into any other form we want, change a few parameters in the button, and then it's good to go.
Okay. So I'll see you back tomorrow for part three.
That's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. See you tomorrow.
TOPICS:
Creating a table-agnostic Recycle Bin table
Setting up fields in the Recycle Bin table
Using AutoNumber for Recycle Bin ID
Adding Deleted Date with default value
Capturing the Username who deleted the record
Storing Table Name and Record ID in Recycle Bin
Using a Long Text field for Record Data
Saving deleted records to the Recycle Bin table
Retrieving the Windows username in VBA
Inserting deleted record data using VBA recordset
Using SQL to delete records from the main table
Requerying or refreshing the form after deletion
Validating Recycle Bin records after deletion
Identifying form-specific code to be made modular
COMMERCIAL:
In today's video, we're continuing with part two of the Access Recycle Bin series. You'll learn how to build a table to store deleted records, including fields for the deleted date, username, table name, record ID, and all the record data. I'll show you how to save information into this Recycle Bin table using VBA, collect the Windows username, and safely delete records from your main table while moving them to the bin. By the end, you'll have a working Recycle Bin for your customer records, and we will discuss how to make this solution even more flexible in the next part. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary purpose of creating a Recycle Bin table in Access as described in the video?
A. To permanently store deleted records for auditing and possible future recovery
B. To speed up access to current records
C. To automatically back up the database
D. To increase the security of all tables
Q2. Why did the instructor want the Recycle Bin table to be "table-agnostic"?
A. To ensure it could accept records from any table in the database
B. To make it easier to delete the Recycle Bin table itself
C. To optimize performance for one specific table
D. To prevent other users from accessing the data
Q3. Which field in the Recycle Bin table is used to track when a record was deleted?
A. Deleted Date
B. Archived Time
C. Modified Timestamp
D. Drop Date
Q4. How is the username of the person who deleted a record captured for the Recycle Bin?
A. By retrieving the username from an environment variable
B. By entering it manually each time
C. By automatically using the primary key of the table
D. By using the default name set in Access
Q5. What data type should the Record Data field in the Recycle Bin table be set to and why?
A. Long Text, because it may need to store large amounts of data from various records
B. Short Text, to reduce storage size
C. Number, to store numerical IDs
D. Date/Time, to track when data was deleted
Q6. What is the recommended method for removing a record from the main table after adding its data to the Recycle Bin table?
A. Use an SQL DELETE statement based on the record's ID
B. Remove it manually through the datasheet view
C. Use a built-in Access function called archived delete
D. Set the record status to inactive
Q7. What happens to the form in Access after the record is deleted and Me.Requery is called?
A. The form refreshes and displays the first record in the recordset
B. The form closes automatically
C. The deleted record stays on the form, marked as deleted
D. The database restarts
Q8. Why is the described Recycle Bin table not optimized for searching?
A. Because it uses a Long Text field that may contain a lot of varied data
B. Because it lacks a primary key
C. Because it is set to read-only
D. Because it stores only numerical values
Q9. The instructor mentions plans to make the Recycle Bin button more modular in the next video. What main change will support this modularity?
A. Creating an event handler function with parameters for table and form names
B. Rewriting all code for each table individually
C. Eliminating the use of environment variables
D. Removing all user prompts
Q10. In the current implementation, which of the following is TRUE about reusing the Recycle Bin code for different tables?
A. It currently references the customer table specifically, so it must be modified for other tables
B. It can instantly be reused on any table without changes
C. It deletes data from multiple tables at once by default
D. It automatically detects what table is active and uses that
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone continues our series on creating a custom Recycle Bin in Microsoft Access. Just to clarify, we are not actually putting Access itself in the Recycle Bin. Instead, we are building a system where you can "recycle" records from your database tables. This means instead of deleting records permanently, you send them to a special place so you can recover them later if needed.
This is part two of the series, so if you have not watched part one yet, I recommend you start there so everything will make sense.
Previously, we got to the point where we built a string containing all the field names and values from a record that is about to be deleted. The next step is to create a table to store those records after they are "recycled."
I create a new table using the Table Design view. My goal is for this table to work with records from any table in the database, making it completely table-agnostic.
The fields I add to the Recycle Bin table are as follows:
- Recycle Bin ID: an AutoNumber to uniquely identify each recycled record.
- Deleted Date: a Date/Time field to track when the record was recycled. I set its default value to the current date and time so it fills in automatically.
- Username: a Text field for storing the username of the person who deleted the record.
- Table Name: a Text field to note which table the record originally came from.
- Record ID: a Number field holding the primary key value of the record.
- Record Data: a Long Text field to store all the original data values from the record, including any large text fields.
This table is not especially optimized for searching, but its main purpose is to provide a repository where you can retrieve record information if something gets deleted unfairly.
Once the Recycle Bin table is built and saved, I return to my customer form and go into the code behind the button responsible for recycling records. Previously, this button just showed the record data in a message box. Now, we need to have it save that information into the new Recycle Bin table.
To do this, I open the Recycle Bin table as a recordset, add a new record, and update the fields as needed. After adding the new record, I update and close the recordset to avoid any dangling connections.
Inside this process, I make sure to fill out each field. The Deleted Date can be set here if you wish, but the default set in the table will cover most cases. Getting the username involves pulling the username from the Windows environment variables. This is not bulletproof security, since someone could spoof or change it, but in most office settings it is sufficient for tracking purposes. If you want to see a more detailed explanation on this, I have another video dedicated to handling usernames and an entire security seminar available as well.
For the Table Name, I plug in the source table of the record, in this case CustomerT. The Record ID field gets filled in from the value on the form. The Record Data field is loaded up with our string containing all the field data.
You can use a With statement for tidier code if you like, especially if field names get long, but I prefer assigning each field directly for simpler maintenance.
Once the record data is safely stored in the Recycle Bin, I proceed to delete the record from the original table. I prefer to use an SQL DELETE statement for this. Immediately after, I requery the form to refresh its records. Without doing this, Access will leave you sitting on a deleted record that just shows "Deleted" for all its fields.
I then walk through the workflow to test the whole process. Deleting a customer, confirming the action, and seeing the record count decrease accordingly. Then, I check the Recycle Bin table and there is the recycled record, complete with deleted date, username, record ID, table name, and all the record data. To illustrate further, I recycle another customer, and verify that both records are present in the Recycle Bin.
At this stage, we have a fully functional recycling system for the customer table. All deleted records are safely tucked away and retrievable if needed.
However, you will notice that the current setup is very specific to the customer form and requires code changes for each table you want to add this to. There are several hard-coded references to CustomerT and CustomerID, so the process is not as generic as it could be.
My goal for the next lesson is to generalize this system. I want to be able to drop the recycle button onto any form, adjust a few parameters, and have it work with any table, not just customers. To do this, I will use an event handler function. By creating a flexible function that takes parameters such as the table name and the form object, we can reuse it universally across your database.
That will be the focus of tomorrow's video. For those of you who are members, you can access it right away. For everyone else, tune in tomorrow for the next step in building a modular, table-agnostic Recycle Bin for Access.
You now have a solid Recycle Bin system in place for your customer table. If you are happy with this setup, you can stop here. If you would like a more flexible solution that works with any form, join me in part three.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Creating a table-agnostic Recycle Bin table
Setting up fields in the Recycle Bin table
Using AutoNumber for Recycle Bin ID
Adding Deleted Date with default value
Capturing the Username who deleted the record
Storing Table Name and Record ID in Recycle Bin
Using a Long Text field for Record Data
Saving deleted records to the Recycle Bin table
Retrieving the Windows username in VBA
Inserting deleted record data using VBA recordset
Using SQL to delete records from the main table
Requerying or refreshing the form after deletion
Validating Recycle Bin records after deletion
Identifying form-specific code to be made modular
|
