|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Show/Hide Buttons By Richard Rost 
5 years ago
Show/Hide Buttons in a Continuous Form In this video, I will show you how to show or hide a button depending on the value of a field in a continuous form. You can't use the Visible property, so we will use a trick involving Conditional Formatting and a Transparent Button. Micah from Titusville, Florida (a Gold Member) asks: I have a continuous form showing my customers. I have a particular form that needs to be filled out but ONLY if the customer is from Florida. Is there any way to show that button for just those customers? I can do it on a Single Form using the visible property, but if I try the same thing on a Continuous form, it changes ALL of the buttons. MembersMembers will learn how to make it so that the open form code does not run if the button is not visible using VBA and an OnClick event.
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
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, show, hide, buttons, continuous forms, visible, conditional formatting, expression is, transparent button
IntroIn this video, I will show you how to use conditional formatting and a transparent button overlay in Microsoft Access to display or hide buttons for each record in a continuous form based on a field value, such as showing an "Open" button only for customers from a specific state. You'll learn how to create the effect using text boxes styled as buttons and control their visibility so that each row displays the button only when needed. This solution answers a common question about handling buttons in continuous forms in Microsoft Access.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to hide or show buttons in continuous forms based on some value in each record. For example, in the screenshot that you can see there, I will only show the open buttons for customers from Florida. The rest of them will be hidden. This question comes from Micah in Titusville, Florida, one of my Gold members. Micah says, I have a continuous form showing my customers. I have a particular form that needs to be filled out, but only if the customer is from Florida. Is there a way to show that button for just those customers? I can do it in a single form using the Visible property, but if I try the same thing on a continuous form, it changes all of the buttons. Yes, Micah, you are correct. If you know a little bit of programming, you can make fields or buttons visible or not visible using their Visible property and the On Current event. In fact, I have other videos to show you how to do that. But that trick does not work with continuous forms. For that, we have to do a little trick using conditional formatting, a transparent button, and a text box that you can kind of make look like a button. Let me show you. Here I am in my TechHelp free template. This is a free template you can grab from my website if you want to. You will find a link down in the description below. Now, in one of my other videos, the On Current event, I show you how you can use a little bit of code to show or hide a field based on its value. For example, if the customer is not active, you can hide the credit limit field. You will find a link to that video down below in the links section. However, that trick does not work if you have a continuous form like this. If you hide a field or a button, or let's say you want to hide the first name field, if you hide that field, all of those will go away. So let's say you want to have this open customer button next to only customers from Florida. If you show or hide it using the technique I show in that video, if you show the button, all of them will show. If you hide the button, all of them will hide. So you need some way of controlling each record and whether or not that button shows up for each record. The only way you can do something like that is with conditional formatting. That is the only way. Unfortunately, conditional formatting does not work with buttons. It only works with text boxes. So we are going to make a little text box that kind of looks like a button. We will put it right over here. Then we will use conditional formatting to show or hide it. Let's go into design view, close that, and make this a little bit bigger. All right, let's put another text box right here. Just grab a text box, drop it here, and delete the label that comes in with it. I am going to open up the properties and set the name to open button. The control source is going to be whatever you want it to be, "open" or whatever you want the label caption to be for our fake button. We are going to open just a form for the customers from Florida, for example. Take the tab stop. Where is the tab stop format? No, it is under Other, I think. Yeah, tab stop. Set that to No so you do not accidentally tab over to that guy. Now, I am going to make this smaller. Let me just shrink it down a little bit like that. Maybe change the font size, so it is, I do not know, nine point, and center it. Let's make it blue with that color background so it kind of looks like a button. Now, unfortunately, you have to set the border style, the outline, to transparent. You cannot have a border around it. I know there are some effects, like if you come into here and you go to Format, you can do a special effect here like Raised and then actually make it look like a button, but the conditional formatting will only change the foreground and background colors. If you leave a border around it, you will still see the empty border when you do this trick. Unfortunately, that is about as good as it is going to look. Let me show you what it is going to look like here. Close that, do that, let us save it, and close it. Open it back up again. It will look like that. That is not too bad. Back to design view. Now, let's put a conditional format on for this guy. Before we do that, what are we going to do? We are going to say if the state field is not Florida, then we are going to hide this guy. How are we going to hide it? Conditional formatting does not support that. We are going to set the foreground and background colors to whatever this detail section is. Go into the detail section's properties, find your background color. You have to have the same, you cannot use alternating background colors, by the way. Just copy that. That is the color code. Now we are going to go into the conditional formatting for this guy. Format - Conditional Formatting. Add a rule. If you have never done conditional formatting before, I have tutorials on that. I will put links down below. Down here, change this to "Where Expression Is." What is the expression? If the state inside square brackets, this is one of those places where it will put state inside quotes if you do not put the square brackets on there yourself, even if you do not have spaces in your name. If the state is not equal to "Florida," then we are going to set a format down here. We are going to set the format, the background color, and the foreground color, the same as the detail section. Come down to More Colors and paste that color code in here that you picked before. Hit OK. Do the same thing for the foreground color: More Colors, then paste it in, and hit OK. So it looks just like that. All you get is bold, italics, underline, foreground color, background color, and enabled or not, which just makes it look like a big gray block. You cannot control the border, which kind of stinks, but oh well. Hit Apply and OK. Shrink that back up again. Save it. Close it. Now open up the customer list, and look at that. There are a couple of exceptions. Notice the Florida ones show up with "Open," the non-Floridians do not. One thing we also have to take into consideration is null values. We have to say if the state is not "Florida" or is null state. Let's add that as a condition too. Click on that, go back into conditional formatting, and edit this rule. Say the state is not Florida or is null state, like that. If it is null or if it is not Florida, make it purple. Close it, save changes, open it back up again. Now we only see that button for the Floridians or whatever. If you have a special form, whatever your criteria happens to be, you can put whatever kind of conditional formatting rule in there that you want. You can do multiple rules too if you have four different forms, one for each state. That is fine too. Now, how do we actually go about opening up the form we want to open? We just put a button over the top of that and make it transparent. Pick a command button right there, drop it in, and select Form Operations - Open Form. Whatever the form is that you want to open, the CustomerF. You can find specific data and open up this particular customer. I cover this in a couple of different lessons too. This would be like the special Florida resident form you want to open. The text is meaningless because we are going to make it transparent in a second. Next, and then Finish. There is my button. We are going to double click on this guy to open up its properties, find the Transparent property and set that to Yes. Notice now all you see is the button outline. Make it surround that guy, like so. You can match it up directly if you want to. I like to leave it overhanging just a little bit so I can kind of see it is still there. Let's shrink this stuff back up again. Save it, close it, open it back up again, and look at that. Now, click on one of these guys. Click and it opens up whatever form you want to. See, close that one. Click on this guy, open it up. There you go. That button only appears where that particular customer is from Florida because of our conditional formatting. Here is the downside. The button still exists in these other spots. If the user clicks here, that form will open because there is a transparent button sitting there. It just does not show up because of our conditional formatting. How do you prevent that from opening the form? I will cover that in the Extended Cut for the members. In the Extended Cut, I am going to show you how to use VBA to cancel that event. So if the person tries to click where there is no button, it just does nothing. We need a couple of lines of VBA code to do that. Then, we are going to change the rules a little bit. Instead of hiding the button if the person is not from Florida, we are going to make a Customer Extended table and form. Basically, additional information, maybe some other form that you want to fill out for each of your customers. We are going to then have the customer list form only show the button if they are missing that data. That way, you know which customers you still have to contact to get this information. Whatever it happens to be, me, I am collecting the shoe size and their favorite band. It could be like Micah had, a certain form they have to fill out, whatever your requirements are. So we will make a second table. Then, we will make it so the customer list only shows that button if they are missing data in that form. So if that form has not been filled out yet, you will see the button. Otherwise, it goes away because you have got the data. We will make a modal extended customer form that you see right there. So once it is open, you cannot click off of it or go around it or do anything. You have to click on the close button. When you close that form, it requeries the form behind it and hides the button then because you have got the data. That is all covered in the Extended Cut. It is 20 minutes long. Silver members and up get access to all of my Extended Cut videos, and I have not checked, but I think I am getting close to 200 of them now. So there is lots and lots of stuff. If you are not a member yet and you want to sign up, there are hours and hours of material for you. Gold members can download all the databases that I build in these Extended Cut videos. How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar and it is also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. Why does using the Visible property in the On Current event not work to selectively show or hide buttons in continuous forms?A. It only works for hidden tables B. It applies the property to all records, not just one C. It causes an error message in Access D. It only works for reports, not forms Q2. What trick does Richard demonstrate to simulate showing or hiding a button for individual records in a continuous form? A. Using a macro to refresh the form B. Customizing the navigation buttons C. Using a text box styled as a button with conditional formatting D. Placing a shape control over each record Q3. Why is a text box used instead of a real button to achieve per-record visibility in continuous forms? A. Conditional formatting only works with text boxes, not real buttons B. Real buttons slow down the form C. Text boxes are easier to create D. Buttons automatically sort themselves Q4. How can you make the "button" appear for customers from Florida only? A. Filter the form to only show Florida records B. Use conditional formatting to match foreground and background colors based on state C. Set the field as required for Florida records only D. Hide the entire row for non-Florida customers Q5. What additional step is taken to make the "text box button" clickable? A. Insert a macro inside the text box B. Place a transparent actual button over the text box C. Link the text box to a macro D. Change the text box property to CommandButton Q6. What is the limitation of the technique using a transparent button over the fake button? A. It only works for single forms B. The button disappears when clicked C. Clicking anywhere with the transparent button will trigger the event even if the text is hidden D. It works only with certain background colors Q7. How can you further prevent actions when the transparent button is clicked for records that should not have the button? A. Remove the button completely B. Provide user training not to click it C. Use VBA code to cancel the event if certain criteria are not met D. Change the record source to exclude those records Q8. Which of the following CAN conditional formatting change in this scenario? A. The border style of the button B. The background and foreground color of the text box C. The visibility of the actual button control D. The font type only Q9. What should the background/foreground colors in the conditional formatting rule match to "hide" the fake button? A. Random colors to blend in B. The system highlight color C. The detail section's background color D. The gridline color Q10. For more advanced solutions, such as opening the form only for customers missing certain data, what does Richard suggest in the Extended Cut? A. Create a union query to merge tables B. Use a Customer Extended table and form to conditionally show the button C. Hide the customer completely from the form D. Use only macros instead of VBA Q11. What is required to access Richard's Extended Cut videos? A. Subscribing to the free newsletter B. Becoming a Silver member or higher C. Posting comments on the video D. Signing up for the mailing list Answers: 1-B; 2-C; 3-A; 4-B; 5-B; 6-C; 7-C; 8-B; 9-C; 10-B; 11-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. SummaryToday's video from Access Learning Zone focuses on how to hide or display buttons in a continuous form based on a value in each individual record, using Microsoft Access. This is a common issue if you have, for example, a list of customers and you want a button to appear only for customers who meet a certain condition, like living in Florida, while keeping it hidden for others.A student, Micah, asked about this specific scenario. He wanted to display a button on a continuous form only when a customer's state is Florida. He had already figured out how to do this in a single-form view by manipulating the button's Visible property in the On Current event. However, he noticed that this same method does not work in continuous forms because toggling the Visible property will show or hide that button in every record at once, not individually. The solution for continuous forms requires a workaround using conditional formatting and a combination of controls. Since conditional formatting in Access does not work on buttons, only on text boxes, I demonstrate how to make a text box appear and function like a button. Here is how I approached it: First, I used a sample database. For those who want to follow along, you can download my free TechHelp template from my website. In a previous lesson, I showed how to use the On Current event with code to show or hide fields based on their values. That approach works great in single form view but not in continuous forms, since setting a field's Visible property affects all records in the form. For continuous forms, the best method is to use conditional formatting. However, because you cannot apply conditional formatting to buttons, you need to mimic a button with a text box. I inserted a text box where I wanted the fake button to appear and set its name and control source, setting the caption to something suitable, like "Open." I then formatted it with appropriate colors and font size to resemble a button. The border was set to transparent, since conditional formatting does not change the border color; that limitation means you cannot get a perfect button look, but it will be close enough for most uses. Next, I set up conditional formatting on this text box so that it would blend into the detail section and effectively disappear when not needed. I did this by matching the text box's foreground and background colors to the detail section's background color whenever the customer was not from Florida. To do this, I created a conditional format rule with a "where expression," specifying that if the State is not "Florida" (or if State is null), the text box should have the same background and foreground colors as the section so the "button" is hidden. This way, only customers from Florida see the "Open" button. For extra realism, I also considered situations where the State field might be null, so I added that condition to the formatting rule. After setting up the text box, the next step was to allow users to interact with it. Since we cannot trigger actions directly from the text box, I placed a transparent command button over the top of it. This button is set to open the form you want when clicked. Even though users will not see the button directly, it still responds to clicks. One limitation is that the transparent button remains clickable even when the underlying text box is invisible, so a user can still click in that space and trigger the form to open, even if it does not appear that a button is present. How can you prevent this? In the Extended Cut, I show you how to use VBA code to cancel that event if the user should not be able to open the form, depending on the record's data. In the Extended Cut, I expand on this by demonstrating how to create an additional table and form for extended customer details. With this enhancement, the "Open" button appears only if certain extra information is missing for a customer. Once a user fills in the necessary details in the modal form, the list updates and hides the button for that customer. This is accomplished by requisitioning the customer list form after closing the modal form. These advanced tips, including how to dynamically disable the transparent button using VBA and how to track missing data with a secondary table, are covered in detail for members in the Extended Cut of this lesson. Silver members and above get access to my library of extended videos, and Gold members can download all the sample databases I build in these lessons, along with my code vault of reusable functions. Not a member yet? You can join easily on my website, and you'll get access to step-by-step extended lessons, chat sessions, and downloads. Platinum members even get full access to my beginner and expert courses covering topics beyond Access, including Excel, Word, and Visual Basic. For those who are just starting out, my TechHelp video series is always free, and you can subscribe to my YouTube channel to receive updates as soon as new content is available. Additional links and resources can be found on my website, including my free three-hour Access Level 1 course. If you enjoyed this lesson and want your own question answered in a video, just visit my TechHelp page and submit your query. 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 ListHiding and showing buttons in continuous formsLimitations of the Visible property in continuous forms Using conditional formatting in continuous forms Creating a textbox that looks like a button Setting up conditional formatting rules based on field values Matching textbox colors to the form background Handling null values in conditional formatting Placing a transparent command button over the textbox Configuring the command button to open a specific form Aligning the transparent button with the fake textbox button |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access show, hide, buttons, continuous forms, visible, conditional formatting, expression is, transparent button PermaLink Show/Hide Buttons in Microsoft Access |
