Recycle Bin 3
By Richard Rost
7 months ago
Send Records to Recycle Bin in MS Access Part 3 In this Microsoft Access tutorial I will show you how to create a generic Recycle Bin function to handle deleted records for any table in your database. We will cover moving your existing code to a global module, making it reusable by adding parameters for the table name, primary key field, and ID, updating your form buttons, and ensuring the active form refreshes after sending a record to the Recycle Bin. This is part 3. MembersIn the extended cut, we will learn how to restore deleted records from the Recycle Bin back into your main tables, similar to using the Restore feature in the Windows Recycle Bin. I will show you how to add a Restore Record button to your form and explain the process to move the data from the recycle table back to its original location. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Recycle Bin function, generic delete function, SendToRecycleBin, global module, event handler, dynamic table name, dynamic primary key, Screen.ActiveForm.Requery, restoring deleted records, soft delete implementation, form button Recycle Bin, handling child records, recycle child records
Subscribe to Recycle Bin 3
Get notifications when this page is updated
Transcript
Today is part three of my Recycle Bin series where we're making a Recycle Bin that you can dump the deleted records into. If you haven't watched parts one and two yet, go watch those first and then come on back.
Alright, so our Recycle Bin is working, but just for the customer. What I want to do next is make a generic function that we can use so that we don't have to customize it every time we want to drop this into a new form.
Let's take this code - everything inside the Recycle Bin click - because we're going to leave that. We still need the button. Actually, no, we don't need that because we're going to make it an event handler. So take all of this stuff, cut it out - snip - go back over here and go to your global module. If you don't have one, create one.
Alright, open it up. And in here, I'm going to paste it down at the bottom right there.
The first thing we have to do is make it public so everybody can call it, and we need to make it a function. You can't have it as an event handler unless it's a function. Even though it's not returning a value, that's just the way they did it. Let's make this SendToRecycleBin.
Now, there's some information we have to send to it - all the stuff in here. So we need to send it the table name, the primary key field name, and the value of the field that we are recycling.
Up here, we're going to say TableName as String, PKFieldName as String, and then the ID as a Long. I like to make these so that the user who's using it, who's writing the code, knows exactly what this is. That's the table, that's the primary key field name, and that's the ID.
Now we just have to make some substitutions. Everywhere you see CustomerT, we have to replace it with TableName. So here's what I'm going to do. I'm going to hit Find - Control F - and I'm going to put in here CustomerT. We're going to replace this.
I hate how this thing moves around. Access team, fix that. This box should not move and it always moves into the upper left corner of my very, very wide monitor.
We're going to replace that. Actually, you know what, we're not going to use this for replacing, we're just going to use it for finding because we have to do some gymnastics with this since this is a variable and it's actually inside the string here.
Let's stick to the current procedure. I don't want to leave this procedure. A procedure is either a Sub or a Function. Hit Find Next.
Here's the first one. See how it moved? So this is inside the string. We have to go like this: end the string, add TableName, and then start the string again. That's going to put CustomerT in there because it's coming in as a variable inside that string.
Let's find the next one. Here it is right here. So again, replace this. I'm just going to copy this. Replace that CustomerT with that.
Find the next one. Down here, same thing. Copy that and stick it right there. Get rid of those extra spaces. And that's probably the last one. No more. That's why we stick to the current procedure. I will not leave this function.
So what's the PK field name? That is what's going to go where CustomerID is. I'm going to copy that to my clipboard. I'm going to now search for CustomerID. Find.
There's the first one. Same thing: close the string, ampersand, field name, ampersand, open the string back up again. This line is getting too long, so we're going to move the WHERE condition down to the next line.
Let's find the next one. Now, this is the value, so be careful with this stuff. That's the value. Might as well do it while we're here. Let's put the ID there.
Find next. That is the value, so that'll just be ID.
So that's all of them.
Now our function should work by sending it the table name, the primary key field name, and the ID value of the record we want to recycle. It will still ask us if we're sure, it will loop through all the fields in this table, create that thing, add this stuff, and delete the right record.
The only problem we're going to have is this: the "Me". If I debug compile, it'll probably yell at me. See? You can't use Me in a global module because Me only works in forms and reports. So that's a big problem. How do we get which form we're on?
Well, we can assume that whatever we're recycling is the current form that's active, and we can get that with Screen.ActiveForm.Requery. Whatever form we're on, we requery it.
Now we can use SendToRecycleBin as a function in an event. I call it an event handler. Save it. Debug compile. All should be well.
Close it. Let's first test it and make sure it works with the customer.
So, Design View. Open this guy up. Now, we removed its event. That's okay. In the On Click box right here, we're going to say: =SendToRecycleBin. There it is. First thing is Table Name: CustomerT. Next, primary key field name: CustomerID. What's the ID value? That's going to be the actual CustomerID on the form, so that's going to be CustomerID, just like that.
Be very careful because sometimes it will try to treat that as a string. Make sure it doesn't. Make sure if anything it puts brackets around it for you.
Now save it, close it, and let's test it. Let's recycle Jean-Luc. Ready? Click. Are you sure? Yes.
We'll see if he's in the recycle bin. Up here... there's Jean-Luc. It's working.
Now let's test it with a contact. And I got rid of the form, didn't I? We'll just open up the Contacts form here. Let's see. Contacts.
So these are all contacts, and I used to have a button on the Customer form to open Contacts, but we deleted it in the first lesson. That's okay. Let's just drop our button down here.
Go to the Customer form. I'm going to copy this guy. Just copy the whole button. Design View, and let's drop it down here at the bottom. I'll just make this smaller.
There's my Recycle Bin button. Make it look pretty. Whatever you want to do.
Open it up, and now we just modify its properties. The table here is ContactT, ContactID, and again, ContactID. Hit OK. Save it. Close it. Save it. Close it. Open it back up. There's Contacts.
Let's get rid of "First MVP Award." Ready? Click. Are you sure? Yep. It disappeared, and there it is: ContactID record 24. Now in here... there's all the data that was in there. It's working.
Now, a couple of things. First of all, if you're dealing with something like Orders, if you recycle the parent record, you won't be able to delete the order if it's got line items and you have referential integrity set up in your relationships. But if you do delete this guy or recycle it, you're also going to want to recycle all of these child records too.
That sounds like something we're not going to cover today. If you want to see me do that, let me know, and if enough people post a comment down below, I will make a follow-up video showing you how to also do all the child records. You can store them all in the same recycle entry.
Right in the RecycleBinT. You can have an order in here and have all of its child information right in there. It's definitely possible. It just adds another recordset loop in the string creator.
Another thing is, right now, if you decide, "Oh crap, I deleted somebody that I need," you'd have to go in here and get the information. At least you have it. You just copy and paste everything else and put it back in the table. It would be nice, like Windows, how Windows has the Recycle Bin tool. You can go in here and just click a button and say, "Restore that for me."
For example, here's my Recycle Bin form, and I have Jean-Luc Picard, I've got me, because I previously recycled those records that are not in here anymore. It would be nice if I could just click on Restore Record. Are you sure? Yes. And it puts me back. Isn't that pretty cool?
I'm going to show how I did that in the Extended Cut for the members. Some of our members in up get access to all of my Extended Cut videos. Not just this one - all of them. There are hundreds of them by now. Gold members can download these databases and you get access to my Code Vault. Everybody gets some free training every month and it's really cool. So join today. Check it out. You'll find more information down below.
There you go, folks. Now you have a fully functioning Recycle Bin for your Access database. It's one record at a time, but still, it'll save you in a pinch if you realize, "Oh crap, I deleted that customer last month," and this will save you some time rather than having to implement a new field into all of your tables, forms, and queries, and all that stuff.
I use this myself personally. I don't delete much, but some of the things I do delete, I want to make sure I can get them back if I need to.
There you go. That's your TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you next time, and members, I'll see you in the Extended Cut.
TOPICS:
Creating a generic SendToRecycleBin function
Passing table name, primary key, and ID to the function
Converting hard-coded table and field names to variables
String concatenation within SQL statements in VBA
Replacing Me with Screen.ActiveForm for global modules
Assigning the function to form event handlers
Testing the Recycle Bin function with different tables
Configuring button properties for different forms
Explaining referential integrity and parent-child record issues
COMMERCIAL:
In today's video, we're continuing with Part 3 of the Access Recycle Bin series. We're going to take the custom code we built earlier and turn it into a generic SendToRecycleBin function that works with any table and any primary key field, so you can reuse it across multiple forms in your database. I'll show you how to set up the function, pass the right arguments, and handle issues like replacing fixed table and field names with variables and using Screen.ActiveForm instead of Me. We'll test the function with both customer and contact records to make sure everything works smoothly. In the Extended Cut, you'll learn how to restore deleted records straight from your Recycle Bin form with a simple click. 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 main goal of the video tutorial discussed in the transcript?
A. To create a custom Recycle Bin for use only with the customer table
B. To build a generic Recycle Bin function that works with any table and form in Access
C. To set up automatic backups for all database tables in Access
D. To demonstrate deleting records without any way to recover them
Q2. Why is the Recycle Bin feature being moved into a generic function?
A. To improve database performance for large datasets
B. To avoid customizing the code every time it is added to a different form
C. To increase the security level of the database
D. To limit the functionality to just customers and contacts
Q3. Which three pieces of information does the SendToRecycleBin function require as parameters?
A. Table name, primary key field name, and the ID value of the record to recycle
B. Table name, database version, and user name
C. Field data type, table relationships, and user permissions
D. Primary key value, number of fields, and table relationships
Q4. When dynamically referencing the table name and field names in SQL queries within the function, what modification must be made to the strings in the code?
A. Use double brackets for table and field names in the SQL
B. Concatenate the variable values with the rest of the string outside the quotation marks
C. Add variables directly inside the quotes
D. Replace all variables with hard-coded values
Q5. What is the problem with using 'Me' in a global module in Access?
A. 'Me' causes syntax errors in all types of procedures
B. 'Me' only works within forms and reports, not global modules
C. 'Me' can only be used in queries and macros
D. 'Me' is reserved for Access system modules only
Q6. How does the new function handle updating the form after recycling a record, given that 'Me' cannot be used?
A. By requering every form in the database
B. By using Screen.ActiveForm.Requery to refresh the current form
C. By closing and reopening the form manually
D. By refreshing the subform only
Q7. What must users be cautious about when setting the control source for the ID value in the On Click event expression?
A. Ensuring that the field name does not have spaces
B. Making sure it is not treated as a string unless necessary
C. Always enclosing the value in quotation marks
D. Using only numeric ID values
Q8. What issue is mentioned when trying to recycle parent records that have related child records and referential integrity enforced?
A. The parent cannot be recycled if there are child records, and child records should also be recycled
B. Only the parent is recycled and child records are left orphaned
C. Access automatically recycles all child records with the parent
D. Referential integrity is automatically disabled
Q9. What is a suggested future improvement for the Recycle Bin discussed in the video?
A. Adding a Restore function to put recycled records back into the original table
B. Limiting the Recycle Bin to only administrators
C. Encrypting all recycled records for security
D. Requiring user login for every recycle action
Q10. According to the video, what benefit does this Recycle Bin approach offer over adding a new field to every table for 'IsDeleted' or similar logic?
A. It allows for better performance on queries
B. It avoids the need to modify every table, form, and query in the database
C. It makes the database read-only
D. It prevents any accidental deletion from happening
Answers: 1-B; 2-B; 3-A; 4-B; 5-B; 6-B; 7-B; 8-A; 9-A; 10-B
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 my Recycle Bin series with part three, where we are building a Recycle Bin feature in Microsoft Access that lets you temporarily store deleted records instead of permanently removing them. If you have not yet gone through parts one and two, I recommend starting with those before you continue here.
So far, our Recycle Bin system is functional, but it only works for the customer records. Now, I want to improve it by designing a generic function that we can reuse with any table or form in your database, without needing to tailor the code for each one.
To begin, I take the logic from the event that runs when you click the Recycle Bin button. Since I want to make this process more flexible, I move this code into a module, which lets other parts of the application call it. If you do not already have a general module, create one in your project, then paste the code at the end of that module.
Next, I modify the procedure so that it is a public function instead of an event handler. Even though functions commonly return values, in this case, it acts mostly like a subroutine, but Access requires this format for how it will be called from events. I name the function SendToRecycleBin.
For this function to work with any table, I need to pass in some specific information: the name of the table, the name of the table's primary key field, and the ID value of the record I want to recycle. So, the function will take parameters for these three items. This makes it clear, both for me and for anyone else using or maintaining the code, which pieces of information must be provided.
With that in place, I have to update all the references inside the function. Instead of hardcoding the table name, I make sure to use the TableName parameter wherever needed. Similarly, where the primary key name or ID value is involved, those are now replaced with the PKFieldName and ID parameters. Since these items appear inside quoted strings in the code, I need to break up the strings and include the variables at the right spots. I use Find in the VBA editor to search for references to things like the old CustomerT and CustomerID, so I update each instance accordingly and keep the adjustments limited to just this function.
One important issue I have to fix is that the code previously used Me to refer to the current form, but that does not work in a global module. Instead, I switch to using Screen.ActiveForm.Requery. That will update the current, active form after a record is recycled, which is exactly what we need.
After these changes, the function is ready to be used as an event handler on any button in any form. I save and compile the code to check for errors, then move to the testing phase.
To test, I first go to the Customer form. I open it in design view, take the old Recycle Bin button, and point its On Click event to use =SendToRecycleBin, passing in the table name (CustomerT), the primary key (CustomerID), and the current value of the CustomerID field from the form. This ensures the function knows exactly which record to move to the Recycle Bin. I pay attention to how Access fills in those parameters so that I do not run into issues with it treating them as plain text instead of references.
Once saved, I try deleting a customer. After confirming the prompt, I check the Recycle Bin table and see that the record has been moved there as expected.
As the next test, I switch to a different form, in this case the Contacts form. Since I had removed the button earlier, I copy it from the Customer form, paste it onto the Contacts form, and modify its properties for the correct table and field (ContactT, ContactID). Again, I save and test by recycling a specific contact record. It is immediately moved to the Recycle Bin, and all its details are preserved.
Now, a few additional notes. If you use this system with tables that have parent and child relationships, such as Orders and Order Details, remember that referential integrity rules in Access can prevent deleting parent records that still have related child records. If you want to recycle a parent record and all its child records together, you'll need to enhance this logic to recycle all related records as a group. That requires some more advanced looping, which I am not covering in this lesson. If you are interested in seeing that, let me know, and if there is enough demand, I will make a separate tutorial about it.
Another thing to keep in mind is the process for restoring records. At the moment, you can recover a deleted record by copying the information from the Recycle Bin table and pasting it back into the original table if needed. As a future enhancement, having a dedicated restore button on your Recycle Bin form, similar to how the Windows Recycle Bin works, would be a great addition. For example, with the press of a button, you can bring back a deleted record right into your working table.
I will be covering how to set up a Restore Record feature in the Extended Cut of this video, available to members. If you are interested in seeing how to implement this and many other advanced features, I encourage you to look into my membership options. Members have access to all Extended Cut videos, downloadable databases, the Code Vault, and more free training each month.
With this Recycle Bin function, you now have a flexible way to recover deleted records in your Access database without retrofitting every form and table. It is a smart safety net for those accidental deletions, saving you time and helping prevent loss of important information.
As always, you can find the complete video tutorial with step-by-step instructions on everything we discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Creating a generic SendToRecycleBin function
Passing table name, primary key, and ID to the function
Converting hard-coded table and field names to variables
String concatenation within SQL statements in VBA
Replacing Me with Screen.ActiveForm for global modules
Assigning the function to form event handlers
Testing the Recycle Bin function with different tables
Configuring button properties for different forms
Explaining referential integrity and parent-child record issues
|
