|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Hide Field Continuous Form By Richard Rost 
6 years ago
Hiding a Field in a Continuous Form. You Can't Use Visible Property! In this video I will show you how to hide fields on a continuous form based on the value of another field. Hint: you can't use the visible property. Bruce from Plano TX (a Gold Member) asks: I have an order list form which is a continuous form showing all of my recent orders. On there is a ship date. Is there any way to hide the date field completely if the order type is "online" instead of "shipped?" I tried setting the visible property to NO in the OnCurrent event, but that hides ALL of them. MembersThere is no Extended Cut for this video. LinksRelationships: https://599cd.com/relationships
IntroIn this video, I will show you how to temporarily hide a field in a continuous form in Microsoft Access based on the value of another field, such as hiding the ship date for online orders but displaying it for shipped orders. We'll talk about why setting the Visible property does not work in continuous forms, and I'll demonstrate how to use conditional formatting to visually hide a field while preventing data entry for those records. This is a great way to improve your forms while working within the limitations of Microsoft Access continuous forms.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to hide a field in a continuous form. Today's question comes from Bruce from Plano, Texas, one of my Gold members. Bruce says, "I have an order list form, which is a continuous form, showing all of my recent orders. On there is a ship date. Is there any way to hide the date field completely if the order type is online instead of shipped? I tried setting the visible property to no in the on current event, but that hides all of them." Yes, Bruce, unfortunately, if you change properties like the visible property or the background or foreground color using your VB code in a continuous form, it changes all of the controls on that form. So, all the form controls that you see are going to go invisible, whether they've got that value or not. Let me show you a technique in this video to get around that. Here is my basic customer template. You can download this from my website. There is a link down below the video for my blank customer template. Let's add an order table like Bruce has in his database. Go to Create and then Table Design. We'll have an Order ID - that's our auto number. We'll have a Customer ID linking it back to the customer. We'll have an Order Type ID - that'll also be a number. This will link to a different table. We don't have to make the whole table for the purposes of this video. Let's just say in here that 1 equals an online order and 2 is going to be shipped. So, if this order type is 1, then it's an online order and we will hide the ship date field. If it's a shipped order, then we need to see it. Of course, we'll have an order date, which is a date/time, and then our ship date, which is also a date/time. This right here will be the field that we're hiding if this order type is 1. Let's save this. I'll save this as my order T. If you're not familiar with how all this stuff works, I cover all of this in my previous videos on how to make relationships and all that stuff. Again, I'll put links to other videos down below. Now, let's make a continuous form to see all of our orders. In the blank template, I have a continuous form already set up - this continuous F. Open that up in design view. If you don't know what a continuous form is, it's where you see a whole list of different records on the same screen. It's a continuous form. I have other videos on how to make continuous forms. I'll put a link down below. Watch that first and come back here. The first thing I'm going to do in my continuous form is open it up. Actually, I'm going to copy this guy. Let's copy this and paste. We'll call this my order F. Now, design view. Come into here. Set the record source first. We're going to make this our order table, so it's bound to our orders. Make sure it's a continuous form right here under Default View. Then, we can set our properties in here. The ID, for example, would be our Order ID. Put it in the Control Source and the Name, and of course, appear in the label if you want to. I'm going to skip all the rest of the stuff like the Custom ID. Really, all we care about in here is the Order Type ID. The Order Type - and this guy here will be the Order Type ID. Copy and paste that. Then, we need two more fields. Copy and paste. Slide it over here. This one is going to be the Order Date. Copy and paste - Order Date. We'll copy and paste this one more time. Copy and paste. This will be the Ship Date. Copy and paste. Now, let's save that, and close it. Open it up. Some orders in here - of course, you'd have customers, all this stuff. Let's say this is an online order. I'll just hit the Control+Semicolon to put today's date in there. Online order, Control+Semicolon, whatever. Let's put a 2 in there. Two is going to be shipped, so this is when we do want to see the ship date. Same thing. And then another two. And the order date is really kind of meaningless. And then another one. Of course, you'd have a combo box here to pick the order type, and this would open up a bigger order form with the details, but you get the point. For my shipped dates right over here, these will actually have ship values. The online orders don't get shipped; it's downloadable. Like I used to ship CDs, and I used to have to keep track of when I shipped them. But online orders that are downloaded - you don't have to have that. So, we want to hide these fields completely. But in here, you'll have a ship date. Bruce wants to hide this field completely. Here's how you would normally do this on a single form. Go to Design View. To hide any particular field in here based on some value, we're going to go in the On Current event. The On Current event is right here under Events. The On Current event fires when you move from record to record. Let me show you first in a regular, like the customer form here. Let's say, and this is hypothetical, let's say if the state is Florida, I want to hide the zip code. I'm just picking something out of thin air. Here's how it works: Design View. Open it up. Go to Events - On Current, hit the dot-dot-dot button that opens up your Visual Basic Builder. Just shrink it down to fit on the screen. You might get a window up asking you what builder you want; pick the Code Builder. That's what we're always using. So, in here, I'm going to say: if state equals Florida (that's a field on my form), then zip.visible equals false; otherwise, because you have to take into consideration what happens if it's not Florida, zip.visible equals true. So, we're going to hide the zip code for people in Florida. That's just what I picked. Now, when I open this up, notice zip code is missing because this person is from Florida. Go to the next record, and now it shows up. That's how you can hide fields on a single form, moving from record to record. However, if you try doing that here - let's actually go to our order form - let's say we have a situation where if Order Type equals 1, then I want to hide the ship date. Again, Design View, come into the On Current event - dot-dot-dot - right there. If Order Type ID equals 1, then it's an online order. In fact, something like this, I'd have to put a little note here, because what is Order Type ID 1? Right, online order. Then it's going to be shipdate.visible equals false else shipdate.visible equals true. If you have more than two conditions, you can put in there "else if 2," "else if 3," and so on. Save it. Close it. Close this down. Let's take a look and see what we got here. Now they're all missing. Let's click on one of these with the two - they all show up. What's going on? As I move from record to record, my On Current event is firing. But when you change the property of a control on a continuous form, it changes all of them. It's technically one control; you're just seeing multiple copies of it. So, you can't change things like the visible property, the background color, the foreground color, the font, the font bold - all that stuff. It'll change all of them. The only thing you can do to get away with this is to use conditional formatting. Let's go in here and remove the On Current event. Just get rid of the whole thing. Delete. Goodbye. We don't need you. We're going to use conditional formatting, because conditional formatting will work between the records. Go to Format. Click on Ship Date - that's the field we want to format. Conditional formatting; new rule. Now, we want to use a different field to perform the conditional formatting, so we have to change this to Expression Is. I have another video that covers this in detail. I'll put a link down below; it's conditional formatting in a different field. The value that we're looking for is in Order Type ID. Put in here - now here you've got to be careful. If you just put in Order Type ID, then Access will convert that to a string. You have to make sure you put the brackets around it here. This is one of those places where you have to make sure you use them. So, if Order Type ID equals 1, then you have to set it to some color with conditional formatting. Just pick a color down here; let's just set it equal to yellow. We'll come back in here in a minute and make it better. Hit OK. Hit OK. Close that. Save, yes. Open it up. And there you go. Look at that. The fields that are online orders, where Order Type ID is 1, are now yellow. If you want to make it look like they're not there, the best thing to do is just match the foreground color with the background color. Design View. Come into here. Go into the properties for your details section. I like to use standard colors. These standard colors down here - I don't use these colors up top because you get Accent 2, Lighter 60. No, I don't want that. I want these standard colors down here. I think the one we have is that guy. These are hex colors. I don't use the alternating back colors. I think those look dumb. They're OK on printouts when you print stuff out, but on the screen, no, I don't like them. So, bottom line is make sure these both match. Copy one of those. Now come into your conditional formatting again: Format - Conditional Formatting. Open this guy up. Change the background color to Custom and paste that in right there. There it is. Now make the foreground color the same thing. It'll show up down here on recent colors. See? Now it looks like there's nothing in there. Hit OK. Apply. OK. Save it. Close it up. Then reopen it. Now look. It looks like it's gone. There's still a border there; you can kind of see that. There's nothing you can do about that unless you turn off all the borders. But I think that's fine; it looks like it's not there. Now what you can do, because you can still technically type in here (which you should do also; you don't want them putting some value in there), is lock that field using your On Current event so they can't type values in there. Right now I'm actually typing, and nothing's happening. Let's see. One. Let's see if I can get something in here. Yeah, see, you can actually type values in there. What you could do is you can lock that field using your On Current event. So, I entered a value because I couldn't see it. I was typing. If you highlight it, sometimes you can still see it depending on your color scheme. In this case, what we can also do is go into Design View, go back to our On Current event, and now we'll say in here, if Order Type ID equals 1, then we've got to make those locked. So shipdate.locked equals true; else shipdate.locked equals false. Yes, this will lock all of them, but it's not a visible change, so it doesn't matter. This is locked right here, and I can't type anything in there. It looks like I've still got a value stuck in that field. Let me go see what I did here. Yeah, I typed that in when I was just goofing around. Come back into here. Now I can't type in there, but if I click down here, I can. It doesn't matter if you use properties like that, because when you move from record to record, it gets reset. But colors and visible property, all those kinds of things, will show up in here. So that's how you do it. Unfortunately, conditional formatting doesn't support changing the border property, so there's no real way around that. You're just going to have to live with it. But at least this visibly looks better for your users. They get the point. You could do the same thing if you wanted to make this gray. You don't have to necessarily match that background or foreground color like I did. Or you could just gray these out, and that indicates that they can't type there. But the bottom line is that's how you change the values like that on a continuous form, using conditional formatting. You can't use things like visible, foreground color, background color - all those things, because it'll apply to all of them. That's pretty much it. I don't have an extended cut for this one. This is only supposed to be a quick, simple video, and I really can't think of anything extra to add for an extended cut. So, they don't all have extended cuts, but most of them do. If you want to get access to the extended cuts, then you can become a member. Become a member to get access to all of my extended cut videos. How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What is the main problem Bruce is trying to solve in his continuous form?A. Hiding the ship date field only for online orders B. Automatically updating the order date when shipping C. Creating a combo box for order types D. Linking orders to customers Q2. When you set the Visible property to No for a control in a continuous form, what happens? A. Only the selected record's field is hidden B. All instances of that control are hidden across all records C. Only empty fields disappear D. Only fields with null values are hidden Q3. In a continuous form, why does changing properties like visible, background color, or font affect all records? A. Each record has its own unique control B. Properties are set globally for all records in the continuous form C. Access uses a unique control for each record D. Conditional formatting overrides these properties Q4. What feature should you use to alter the appearance of a control based on data in another field in a continuous form? A. Use the On Current event and change the visible property B. Use conditional formatting with an appropriate expression C. Hide the control manually for each record D. Use macros to change the background color Q5. When setting up conditional formatting based on a different field, what is important to remember about referencing the field? A. Use single quotes around the field name B. Use square brackets around the field name C. Use parentheses around the field name D. Field references are not allowed in conditional formatting Q6. How can you make it look like a field is hidden in a continuous form, even though you cannot set Visible to No? A. Set the font size to zero B. Use conditional formatting to match the foreground and background colors C. Remove the control from the form in design view D. Make the control disabled Q7. What limitation still remains even after making the foreground and background colors match for the field you want to "hide"? A. The font remains visible B. Users can still change the border color C. The control border is still visible D. The control becomes read-only for all records Q8. What additional step can you take to prevent users from typing into a "hidden" field on a continuous form? A. Set the control's Visible property to No in the On Current event B. Lock the control using the Locked property in the On Current event C. Remove the control from the Record Source D. Hide the entire form section Q9. Why is it acceptable to lock a control on all records in a continuous form via the On Current event? A. Because locking only affects one record at a time B. Because locking does not impact the appearance across records C. Because it lets you change the visible property too D. Because the Locked property is ignored on continuous forms Q10. What is the main takeaway from this video for handling field visibility in continuous forms? A. Use macros for every control that needs to be hidden B. Use conditional formatting and the Locked property instead of Visible or color properties C. Change the tab order to hide fields D. Only use datasheet view to prevent this problem Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-B; 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. SummaryToday's video from Access Learning Zone covers how to hide a field in a continuous form in Microsoft Access. The question comes from one of my Gold members, Bruce, who has an order list displayed in a continuous form. His form shows all his recent orders, and within it, there is a ship date field. Bruce would like that ship date field to be completely hidden if the order type is "online" rather than "shipped." He attempted to use VBA to set the visible property of the field to no in the On Current event, but found it hides the ship date for all orders, not just the online ones.This is actually a fundamental characteristic of Access continuous forms. When you try to change properties such as Visible, BackColor, ForeColor, Font, or other display properties of a control using VBA, that change applies to every instance of that control on every row of the form, not just the current record. That means you cannot selectively make the ship date visible or hidden for just a single record in a continuous form using VBA properties. Let me walk you through a workaround so you can effectively achieve the desired result. To start, I use my standard customer template, which is available for download from my website. In our example, I walk through the process of adding an order table that includes an Order ID, Customer ID to link to the customers, and an Order Type ID, which is a number. For simplicity, I set it so that 1 represents an online order and 2 represents a shipped order. We add both an order date and a ship date field. The goal is to hide the ship date field when Order Type ID is 1 (an online order). I save this new table as "OrderT." If you're unfamiliar with setting up relationships between tables and similar topics, I recommend checking out my earlier instructional videos, which can be found on my website. Next, I create a continuous form to display all the orders. In my blank template, there is already a continuous form ready to go. I copy this form and adjust it so its record source points to our new order table. I make sure to display the relevant fields, specifically the order type, order date, and ship date. For demonstration purposes, I manually enter a few orders, setting the order type appropriately for online and shipped orders. In a production database, you'd typically use a combo box for the order type and have a fully-featured order entry form, but for this example, it's enough to demonstrate the question at hand. Returning to Bruce's question, he wants the ship date field to be invisible for online orders in the continuous form. On a single (not continuous) form, you can easily use the On Current event in VBA to check the record's value and set the control's Visible property accordingly. For example, you can hide the zip code if the state is Florida. In that case, as you move from record to record, the On Current event updates the field visibility based on the value of each record. However, on continuous forms, changing the Visible property for any field hides or shows it for every record on the form. The same applies to background color, foreground color, and similar properties. What you can use instead is Conditional Formatting, which works on a per-record basis. Instead of using VBA to try to hide the field, you apply conditional formatting to the control you want to alter. In this case, apply conditional formatting to the ship date field and define a rule based on the value of Order Type ID. In Conditional Formatting, you select "Expression Is" and create a condition like: [OrderTypeID]=1. With this, you can set changes to the ship date field's appearance just for those records that meet the condition. Initially, you can set a color (for example, yellow) so you can see it's working, but the best effect for hiding is to set both the background and foreground colors to match the form's background—usually white or whatever standard color your form uses. This effectively makes the ship date field appear invisible for online orders. There will still be a border around the field, but that's just an Access limitation and there isn't a practical way around it short of removing all borders. One caveat to be aware of is that users could still type in the field even though they can't see anything. To handle this, you can use the On Current event in the form's VBA to set the Locked property of the ship date control. Make the ship date locked when the order is online, and unlocked otherwise. While the Locked property still affects all records on a continuous form, it's a non-visual property and won't confuse the viewer. Combining Conditional Formatting for the visual effect and the Locked property in VBA for usability, you can get as close as possible to hiding a field on a per-record basis in a continuous form. Unfortunately, Conditional Formatting doesn't support changing the border property, so the field border will remain, but this limitation is generally acceptable to users. It is worth noting that there is no Extended Cut for this video since the solution is pretty straightforward and covers all the necessary points here. 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 ListCreating and structuring an order table in AccessSetting up order type logic for online and shipped orders Adding ship date and order type fields to the form Designing a continuous form for displaying orders Explaining the limitations of the Visible property in continuous forms Using the On Current event to hide fields in single forms Demonstrating why hiding fields by Visible property fails in continuous forms Removing On Current event logic for continuous forms Applying conditional formatting based on another field's value Configuring conditional formatting to simulate hidden fields Matching foreground and background colors for hidden effect Setting form detail section background color Locking a field in continuous forms using the On Current event Combining conditional formatting with field locking to prevent input Discussing limitations of conditional formatting for borders |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access hide field, continuous forms, visible property, oncurrent event, all fields, single field conditional formatting PermaLink Hide Field Continuous Form in Microsoft Access |
