On Current
By Richard Rost
6 years ago
Learn the On Current Event in Microsoft Access
In this Microsoft Access TechHelp tutorial, I will show you how to run VBA code when the user opens a form and moves from record to record. This is handy if you are doing things like changing the visibility or colors of different fields based on user input.
David asks: "In my form, I have certain fields that are made not visible based on the user’s input in other fields. For example, if the customer is not active, I can hide his Credit Limit. I used an After Update event for this. However, when I open the form or move from record to record, the Credit Limit field is still visible. How do I hide this field based on the current customer’s existing value?"
Members
I'll show you how to make custom subroutines (so you don't have to have the same VB code twice in your form), changing the color of fields and the background color of the form, using the MsgBox function to return a value (yes or no), setting the customer's credit limit to $0 if they become inactive, changing a check box into a Toggle Button, and setting its caption and color properties too! Lots more for members!
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!
 Links
After Update: https://599cd.com/AfterUpdate
Subscribe to On Current
Get notifications when this page is updated
Intro In this video, we talk about how to control the visibility of fields on your Microsoft Access forms based on user input, such as hiding the credit limit field for inactive customers. I will show you how to use the After Update event to make form controls visible or hidden and explain why your code might not work as expected when moving between records. We'll cover how to use the form's On Current event so your fields update correctly every time you switch records or open the form.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's question comes from David.
David sent me a very long email, and I tried to ascertain exactly where he was going with it, so I rewrote his question a little bit. But you get the gist.
He says that in his form, he has certain fields that are made not visible based on the user's input in other fields. For example, if the customer is not active, he can hide the credit limit field. He used an after update event for this. That means you check the box "Active" and it shows the credit limit. If you uncheck the box, it will hide that field completely so the user does not have to look at it.
However, when he opens the form or moves from record to record, the credit limit field is still visible. He asked, "How can I hide this field based on the current customer's existing value?"
In other words, here is a basic customer form. There is an "Is Active" field, and if this customer is not active, he wants to hide the credit limit. If they are not an active customer, we will assume they do not have a credit limit.
You can put an after update event in this box here, which I will show you in a second, which will basically hide this. However, when you move from record to record, that code does not fire because it is locked to the after update event. So how do you do that?
First things first. If you do not know what an after update event is, I have another tutorial on my channel that explains how to use an after update event to change something when you update a field. You type in someone's name or you check a box or anything like that. It is called an after update event.
Looks like I uploaded it back in 2008 with 56,000 views so far and I want to know who these five people are that gave me a thumbs down. And check out that classic font, Access Learning Zone. Wow. That is old.
Stop now and go watch that tutorial if you do not know what after update events are. I will put a link in the description box below this video.
Now that we know what after update events are, what I want to happen is when I click on this box, I want my after update event to hide or show this credit limit box. More importantly, when I move from record to record, I want that to update.
So, let us put the after update event in first. Go to design view. Go to the "Is Active" check box. Make sure you are on the check box and not the label. Labels do not have events in them, so make sure you click on the check box.
On your event tab, find "After Update." This is the after update event. Click on the dot dot dot button. If you see the builder window appear, pick "Code Builder." Here is my Visual Basic editor.
We are going to use a little If Then statement in here to change whether or not that field is visible based on its value. Here, I am going to say "If IsActive" (it is the name of my field). "IsActive" is a check box, so it is basically a Boolean value (True/False, Yes/No, On/Off). I do not have to say "equals True" - that is just assumed, because this is a Yes or No value.
So, I can just say:
If IsActive Then
' now I want to make that field visible
' let us see what the name of the field is
' CreditLimit. It is just CreditLimit
And then we have a label next to it, that is Label11. So, we have to make both of these things invisible. Let us just do this box for now and see what happens.
So, that is CreditLimit. I am going to say:
CreditLimit.Visible = True ' Make it visible
Else
CreditLimit.Visible = False
And if there is a little "End If" statement. I have videos for that too. I will put a link below.
Let us see this in action. Save it. Close the Visual Basic editor. Close the form. Open it back up again.
Now, notice if I move from record to record nothing happens yet. We have not gotten to that part yet. Let us just check the "Active" box here. Click. I just checked it on, so that box is made visible, which it was before. That is fine. Turn it off, and now it hides.
Now, here is one of the benefits of the newer versions of Access. In the older versions, it just did not work that way. You would have to specify code for the label separately. But, since this is a joined label - it belongs to that text box - you do not have to do that in the newer versions of Access. Otherwise, we would just need another line of code that says LabelName.Visible also equals True or False.
Now, here is the problem. I checked this box off and now this is invisible. If I move to the next record, look at that. Kirk here is active but his box is still hidden. That is because this code only runs when you change the check box.
Now, if I click on it, it will come back. So I want this code here to run when I move from record to record and when this form is opened and closed. Because if I close this and open it back up again, he is inactive but I am still seeing his credit limit.
So what do we do?
Let us go back into our Visual Basic code. Design view. On the design tab there is a button that says "View Code." That is the fastest way to get in there, or just leave this open instead of closing it, just minimize it.
So, I have my after update code all set up here. What I need to do is find the On Current event for the form itself. Go to the form's properties, click on this little box. Now I am on the form properties. Find the On Current event. On Current says, "the macro function that runs when focus moves from one record to another." It also includes the first record when you open a form.
I am going to go to On Current, click the dot dot dot, and that puts me back in the Visual Basic editor, and now we are in here inside Form_Current.
Now, put whatever code you want in here to run when you move from record to record. I am just going to copy and paste the same thing. That is not great programming standards but we will talk about that in just a minute. The bottom line is, for right now, the same thing will happen twice here. It will happen when the box is updated and when you move from record to record.
Let us give it a try. Save that. Close the form and reopen it. Look, now he is not active and his box is gone. Move to the next record. He is active and his box appears. Move to the next record. Active and it is there. Turn that off. Go back. Go back. Same. On, off. Move to the next record, and so on.
What happens is now it will run in the after update event and it will run in the on current event, which happens when you open the form and when you move from record to record.
I mentioned a second ago this is not necessarily great programming technique. Ideally, what you want to do is create your own function or your own subroutine and put that code in there once, and then call that subroutine from both of these other places. That way, if you make changes to this, you do not have to remember to change it in two locations or more, and you will have the same code that you want to run in different places, three, four, five, ten spots inside your form.
So, for that, we want to make a custom subroutine. If you want to see how to do that, I am posting it as a members only video. I will not only show you how to set up a custom subroutine so your code can run from one place but I put a bunch of other tricks and stuff in here too.
I will show you how to change the color of the form as you move from record to record. Notice the difference between active and inactive customers: the background of the form changes. You can change fields. I change this to a toggle button instead of a check box. Look - as you move from record to record the button goes up and down. The caption changes from "Active" to "Not Active" and the color of the caption text as well.
Plus, if you take someone who has a credit limit and they are active and you mark them inactive, I will show you how to prompt the user and ask, "Hey, do you want to set this customer's credit limit to zero?" If you say no, it leaves it alone in there. If you go back to active you will see that thing. But if you say yes, it zeroes it out. That way, their credit limit does not show up in all your reports.
Come back in here and notice it is zero now. See?
Once again, that is all covered in the members only edition of this TechHelp video.
How do you become a member? Just click on the join link below this video. You will see a list of all the options available. Silver members and higher get access to the extended TechHelp videos. Watch the little video there that pops up in the window to explain all the different membership levels.
Even if you do not want to become a member, that is okay. I am still going to be posting these free TechHelp videos on my channel; just members will get extra content. They will get longer stuff.
Make sure you subscribe to my channel. Click on the subscribe button; that is free. If you click on the little bell and pick "All," you will get email notifications whenever I release new videos. Do not worry; nothing is changing. I am still going to be making these free TechHelp videos for everybody. For your charge - just members will get more stuff. There are extra perks.
Make sure you stop by my website and subscribe to my Access forum. If you have questions, send them to me on the TechHelp page. Of course, members will get priority for me answering their questions, but I still will read your questions if you send them to me.
If you have not taken my free Access Level 1 class, there is the link. It is absolutely free. It is on YouTube. It is on my website. Three hours long - wait, where is the thing? Three hours long, just like Gilligan's tour. It will cover all the basics of Access if you have never used Access before. Or, even if you have been using it for a while and you are not sure how you are doing stuff, check out that. It will give you some good fundamentals.
If you like Level 1, Level 2 - which is another hour plus long video - is just a dollar.
Thanks for watching. I hope you learned something and we will see you next time.Quiz Q1. What is the main issue David encountered in his Access form?
A. The credit limit field is not being calculated correctly
B. The credit limit field remains visible when moving between records, regardless of the customer's active status
C. The "Is Active" field is not updating
D. The form does not save changes made to records
Q2. What event did David originally use to hide or show the credit limit field?
A. On Load event
B. After Update event
C. On Click event
D. Before Insert event
Q3. Why does the credit limit field not update properly when moving to a new record?
A. The On Click event is only triggered when the form opens
B. The After Update event only runs when the field is updated, not when the record changes
C. The credit limit control is not bound to any field
D. The form does not support conditional formatting
Q4. Which event should be used in addition to the After Update event to make the credit limit field update when moving between records?
A. On Open event
B. After Insert event
C. On Current event
D. Before Update event
Q5. In the provided code example, how is visibility of the credit limit field controlled?
A. By setting CreditLimit.Visible based on the value of IsActive
B. By changing the Enabled property of the field
C. By hiding the entire form
D. By deleting the control from the form
Q6. What programming practice does Richard say is better than copying the same code into multiple event procedures?
A. Using macros instead of VBA code
B. Creating a custom subroutine and calling it from multiple places
C. Using conditional formatting
D. Using static variables
Q7. In newer versions of Access, what is the benefit related to labels joined to text boxes?
A. Joined labels are hidden automatically with their control
B. Labels cannot be hidden at all
C. Each label must always be handled separately
D. Labels do not exist in newer versions
Q8. What is the recommended action if you are unfamiliar with After Update events?
A. Ignore the video
B. Watch Richard's earlier tutorial on After Update events
C. Use only On Load events
D. Read the Access help file only
Q9. What happens when you implement the visibility code in both the After Update and On Current events?
A. The credit limit field stays hidden permanently
B. The credit limit field correctly appears or disappears when updating the Active status and when moving between records
C. The field keeps reloading every second
D. The field can only be shown, not hidden
Q10. For advanced functionality, such as changing the form background or prompting to reset a credit limit, where can viewers find more information?
A. In the same free video
B. In the Access Level 1 class only
C. In the members-only extended video
D. On Microsoft's official site
Answers: 1-B; 2-B; 3-B; 4-C; 5-A; 6-B; 7-A; 8-B; 9-B; 10-C
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 a question sent in about making certain fields visible or hidden on a form based on other user inputs, specifically within Microsoft Access. I am your instructor, Richard Rost.
The scenario we are looking at is this: suppose you have a customer form with a checkbox indicating if the customer is active. If the customer is not active, you want to hide their credit limit field, since inactive customers should not have a credit limit displayed. The original solution involved using the After Update event of the checkbox to toggle the visibility of the credit limit field. This works when you check or uncheck the box during normal form use. However, the challenge arises when you open the form or navigate between records. In those situations, the credit limit field does not update its visibility automatically, because the After Update event only fires when the checkbox changes.
If you are unfamiliar with After Update events, I have a tutorial covering how to use these events to trigger changes based on edits to fields. I recommend reviewing that for a background understanding.
Back to the current problem: we want the credit limit field's visibility to reflect the active status both when making changes and when switching between records. First, you can use the After Update event of the "Is Active" checkbox to make the credit limit visible or hidden, depending on the checkbox state. In newer versions of Access, if your label is attached to the text box, you do not need to write separate code to hide the label, as it will be handled automatically. In older versions, you would need to set the visibility for the label separately.
The main issue is that the After Update event only works when the checkbox is changed. If you move to another record, for instance, or start with a record where the active status is already set to "No," the code will not execute. As a result, the credit limit might appear when it should be hidden, or vice versa.
To solve this, you also need to use the On Current event for the form. The On Current event triggers whenever you move from one record to another, or when the form opens and the first record loads. By putting similar code into the On Current event, you make sure the credit limit field's visibility updates correctly whether a user is interacting with the checkbox or simply navigating through the records.
At this stage, you might notice code duplication between the After Update and On Current events. While that works, it is not ideal. A better approach is to place the code in a custom subroutine or function, which you then call from each event. This means future changes only need to be made in one place, ensuring consistency across your form.
For those interested in more advanced techniques, I have produced a members-only video where I demonstrate creating a single subroutine for the visibility logic, and I show additional tricks such as changing the background color of the form based on the active status, using toggle buttons instead of checkboxes, updating captions and caption colors, and even prompting the user if they want to reset the credit limit to zero when a customer is marked inactive. All of these enhancements can really take your forms to the next level.
Becoming a member gives you access to these extended tutorials, along with additional perks. Silver members and above can view the extended TechHelp videos, which provide deeper and more detailed instruction. But if you prefer not to join, do not worry – I will continue to post these free TechHelp videos on my channel. Subscribing to my channel and signing up for email notifications ensures you do not miss any new content.
If you need more help with Access, my free Access Level 1 course is available on my website and YouTube. It covers everything you need to get started, and if you enjoy that, there is a Level 2 course available for just a dollar.
Thank you for watching. If you need a comprehensive, step-by-step video tutorial covering everything discussed here, you can find it on my website at the link below. Live long and prosper, my friends.Topic List Using After Update event to show or hide fields
Setting up visibility based on checkbox values
Understanding the Boolean value of a checkbox
Writing VBA code for conditional visibility
Testing visibility changes with After Update
Differences in handling labels in newer Access versions
Identifying the need for the On Current event
Adding code to the Form's On Current event
Ensuring field visibility updates on record navigation
Duplicating code in multiple event procedures
Explanation of why code does not fire on record change
Recommendation for subroutines to avoid code duplication |
