Dirty
By Richard Rost
3 years ago
Working with Dirty Records in Microsoft Access
In this Microsoft Access tutorial, I'm going to teach you what dirty records are and how to avoid certain problems that come up with them. For example, if you try to open up a form with related information and the parent form is dirty you might not get what you were expecting.
Members
Members will learn how to create an undo button that is only active when the record is dirty. We will then also make a prompt to ask the user if they want to save changes when a record is edited. We will use the dirty property, the undo method and the before and after update events.
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!
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Me.Dirty, Dirty Records, Pencil, Dirty Property, On Dirty Event, Undo, prompt to save changes
Subscribe to Dirty
Get notifications when this page is updated
Intro In this video, we will talk about the Dirty property in Microsoft Access, what it means when a record is "dirty," and how incomplete data edits can affect forms, related tables, and reports. You will see how the Dirty property works in real time, how to identify unsaved records using form icons, and why records must be saved before they appear in related forms or queries. I will also show you how to use simple VBA code like Me.Refresh to make sure your data changes are properly saved when working with multiple forms.Transcript In today's video, we are going to talk about the dirty property. What does it mean when a record is dirty?
Before we get started today, if you have not watched my invoicing video, go watch that first. This is the database we are going to be using for today's example.
Here I am in my TechHelp Free template. This is a free database. You can download a copy from my website if you want to. You will find the link down below.
If you open up any form, like here is me, and I make a change and put something in here, notice up in that upper left hand corner, that symbol changes to a pencil. There is a little pencil icon right there.
What does that pencil mean? That means the record is dirty. That means you are in the process of editing it or changing the data, but that record has not been committed to the table yet. It has not been saved.
At this point, if I open up the customer table and look in here, my name is still Richard, not Richard A-A-A, because this record is dirty. The data just exists in the form in the computer's memory. It has not been saved to the table yet.
Generally, this does not make that much of a difference, but it can cause some problems.
For example, let's say you go to a new record and you type in Aaron Adams. If you have not saved the record yet and you go to click on orders to create an order for Mr. Aaron Adams, the order form opens up. Normally, if you watched my invoicing database, you know that this combo box gets the customer value from the open customer form.
But if I drop this box down right now, these are sorted alphabetically. There is no Aaron Adams. Why? Because this record has not been committed to the table yet. It is still dirty. It is not going to show up in any related forms, table boxes, list boxes, queries, reports, or any of that stuff.
What you have to make sure you do is save the record. There are a lot of ways to save the record. You can move off of the record and back to it. You can click on refresh and that will refresh the record. That does a Me.Refresh if you are a VBA programmer. You can click on the save button. That will also save the record.
But your users should not have to deal with all that. Access is supposed to just save stuff as you enter records.
How could we have the order form save this record before it opens up that order form? Well, it is just one line of code. If you have never done any VBA programming, go watch this video first - it is about 20 minutes long and teaches you everything you need to know to get started. VBA is not scary, folks.
Where do we put that one line of code? Right-click, Design View, and in the order button, right-click, Build Event. Before we go and try to open that form, we are going to say Me.Refresh.
Me is the form that you are currently on, which in this case is the customer form. It is going to save that record, commit it to the table, then open up the order form where CustomerID equals the current CustomerID.
Save that, close it, open up the customer form again. Let me go back over here. Let me delete Mr. Aaron Adams. Delete, he is gone.
Open it back up again. Now, let us add another new record, Aaron, Apple, whatever. Before I save that record, I am going to hit the order button and look at that. He shows up here now. Why? Because this record was saved to the table first, then this form opened up.
If you remember from the invoicing video, this combo box gets its default value from Forms!CustomerID. That is why, if you are opening up a form or even a subform and you are not seeing the right stuff in there, you have to make sure you refresh the record of the form that you are leaving first.
This also shows up when you open up the invoice report. In fact, if you watched my invoicing video, you will recall that I mentioned that when we built this button. There is the Me.Refresh right there. The way it works is that it refreshes the stuff on the invoice or on the order form first, then it opens up the order report. If you do not do that and you get changes on your order form, they are not going to show up in your report.
That is that simple.
If you want to learn more about this dirty property, I cover it in more detail in my Access Expert classes, Level 1 and Level 5, and then from the programming standpoint, Access Developer 19. I will put links to these down below.
As always, when I did my research for this video, I googled to see what other people have to say about the topic and Michael, over at No Longer Set, has a really cool article on the dirty property and he has some code and stuff like that for more advanced developers. Check it out. I will put a link down below so you can click on it.
For my members, we will do a little extended cut with some cool extra tricks and tips I am going to show you. I will show you how to make an undo button that is only enabled when the record is dirty. As soon as the record goes dirty, that button becomes enabled so you can click on it. If not, if they have not edited the record, they cannot click on the undo button. We will use the OnDirty event for that.
Another popular thing that lots of people ask me about is making it so that when users change a record, Access will say, hey, you have changed this record. Are you sure you want to save the changes? We will check the dirty property to see if the record was changed before the user leaves it. If so, it will ask, hey, you have made changes. Are you sure you want to save these changes?
That will be coming up in the extended cut for the members. Silver members and up get access to all of my extended cut videos and Gold members can download these databases.
But that is your fast tip video for today. I hope you learned something and I will see you next time.Quiz Q1. What does it mean when a record is referred to as "dirty" in Microsoft Access?
A. The record has been deleted from the database
B. The record contains invalid data
C. The record is being edited and changes have not been committed to the table yet
D. The record has been archived
Q2. What visual indicator shows that a record is dirty on an Access form?
A. A lock symbol
B. A red X
C. A pencil icon
D. A green checkmark
Q3. When a record is dirty, where does the new or changed data exist?
A. It is only stored in the database table
B. It is stored in the form's memory but not yet in the table
C. It is saved to the hard drive immediately
D. It is updated in all related forms and reports
Q4. If you add a new customer but do not save the record, what is likely to happen when you try to open a related order form?
A. The new customer will appear in related combo boxes immediately
B. The new customer will not appear in related forms because the record is still dirty
C. The record will be automatically saved
D. The form will crash
Q5. Which of the following is NOT a way to commit a dirty record to the table in Access?
A. Move off the current record and return to it
B. Click the save button
C. Use Me.Refresh in VBA
D. Ignore the changes and close Access
Q6. What does the VBA code Me.Refresh accomplish in the context of saving records?
A. It deletes the current record from the table
B. It refreshes the form's appearance
C. It saves/commits the current record to the table
D. It opens a new form
Q7. Why might related forms or reports not display new or edited records immediately?
A. Because the new or changed record remains dirty until saved
B. Because Access caches all data
C. Because the database needs to be compacted
D. Because relationships are not defined
Q8. What is one method mentioned to ensure a record is saved before opening another form, like the order form?
A. Use Me.Refresh in the button's VBA event before opening the new form
B. Close and reopen the entire database
C. Add a new table
D. Increase the form's refresh rate
Q9. What Access event can be used to enable features such as an Undo button only when the record is dirty?
A. OnClick event
B. OnDirty event
C. OnCurrent event
D. OnLoad event
Q10. What can you do to prompt users to confirm saving changes only if they have actually modified a record?
A. Always ask on form exit regardless of changes
B. Use the dirty property to check for changes and prompt the user
C. Lock all records permanently
D. Never allow edits
Answers: 1-C; 2-C; 3-B; 4-B; 5-D; 6-C; 7-A; 8-A; 9-B; 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 video from Access Learning Zone focuses on the concept of the dirty property in Microsoft Access. What exactly does it mean when a record is marked as dirty?
To start, make sure you are familiar with my invoicing video, as I will be using that particular database for our demonstration today.
In the TechHelp Free template database, which you can download from my website, any time you open a form and modify a record, you will notice a pencil icon appears in the upper left corner of the form. This pencil signifies that the record is current being edited, but those changes have not yet been committed to the table. In other words, the data is temporarily stored in computer memory within the form and has not yet been saved.
For example, if you change a record but have not saved it, and then open the underlying table, you will see that the changes are not yet reflected. They remain only in the form because the record is still dirty.
While this characteristic may not always present issues, it can cause problems in some scenarios. Suppose you are entering a new customer, say Aaron Adams, but you have not yet saved this record. If you then try to create an order for Aaron Adams by opening the order form, you will not find Aaron listed in the customer dropdown. This is because the new record has yet to be saved in the table, so it is not available to any related forms, queries, or reports.
To solve this, you need to ensure the record is saved before proceeding. There are several ways to save a record in Access. You can move the focus away to another record and then return, use the refresh command, or click the save button. Each of these actions will commit the data to the table. However, users should not have to worry about manually saving their changes.
A good practice is to automate this process, especially when opening another form that depends on updated data. For example, before opening the order form from the customer form, you can use a single line of VBA code to refresh the current form and save any changes made. This guarantees the new or edited record exists in the table when the related form opens.
This code goes into the event procedure behind the button that opens the related form. Specifically, you add the command to refresh the current form before opening the new form. This ensures the most up-to-date customer information is available for selection.
After making these adjustments, try entering a new customer and then immediately open the order form. The record will now appear correctly, because it was saved just before the form was launched.
If you recall from the invoicing video, some elements—such as default values in combo boxes—depend on the current form's data. Therefore, it is vital to refresh and save the record in your primary form before opening related forms, subforms, or generating reports.
This requirement also applies to reports. When you create, for example, an invoice report, you want to make sure that any unsaved changes on your invoice or order form are saved first, so the report displays the correct data. Without this extra step, your changes may not be reflected in the final output.
If you are interested in a deeper discussion about the dirty property, I cover it in more detail in my Access Expert Level 1 and Level 5 courses, as well as from a programming perspective in Access Developer 19. You can find links to those materials on my website.
Additionally, while researching this topic, I found an insightful article by Michael at No Longer Set that goes further into the dirty property, especially for advanced users. You can find a link to that article on my site as well.
In today's Extended Cut video for members, I will demonstrate some advanced tips, such as how to create an undo button that becomes enabled only when the record is dirty. This button allows users to undo edits only when changes are pending. We will also use the OnDirty event for this purpose. Furthermore, I will show you how to add a prompt that alerts users about unsaved changes before they leave a record, ensuring they are given the chance to confirm whether they want to keep their changes.
Silver members and above have access to all extended cut videos, and Gold members can download all the databases covered in my tutorials.
That wraps up today's quick tip. If you want to see a complete video tutorial with step-by-step instructions on everything I discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Explanation of the dirty property in Access forms
Identifying the pencil icon indicating a dirty record
Understanding when data is saved to the table
Problems caused by unsaved (dirty) records
Example: missing new records in related forms
Ways to save a dirty record (navigation, refresh, save button)
Using Me.Refresh in VBA to save records
Placing Me.Refresh in button event code
Ensuring related forms display updated data
Importance of refreshing records before opening reports
Impact of dirty records on combo boxes and reports
Updating the current record before proceeding to related forms or reports |
