Hiding a Field in a Continuous Form in Microsoft Access. You Can't Use Visible Property!

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross 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 so let me show you a technique in this video how to get around that okay so here's my basic customer template you can download this from my website there's a link down below the video for my blank customer template and let's add an order table like bruce has in his database so let's 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 now this will link to a different table we don't have to make the whole table for the purposes of this video but let's just say in here that one equals an online order and 2 is going to be shipped all right so we'll say that if this order type is 1 then it's an online order we'll hide the ship date field okay if it's a shipped order then we need to see it okay then we'll of course have an order date which is a date time and then our ship date which is also a date time and this right here will be the field that we're hiding if this order type is one all right let's save this i'll save this as my order t okay and if you're not familiar with how all this stuff works i cover all 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 alright so i have in the blank template i have a continuous form already set up this continuous f open that up okay design view and again 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 okay it's a continuous form again i've got other videos on how to make continuous forms i'll put a link down below watch that first then come back here but the first thing i'm going to do in my continuous form is open it up here actually let me copy this guy let's copy this copy paste we'll call this my order f all right 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 so the id for example would be our order id all right put it in the control source and the name and of course up here in the label if you want to i'm going to skip all the rest of stuff like the customer id really all we care about in here is the order type id right the order type and this guy here will be the order type id copy paste that and then we need two more fields copy paste slide that over here this one is going to be the order date order date like that copy paste order date and we'll copy and paste this one more time copy paste and this will be the ship date ship date copy and paste okay now let's save that close it all right open it up so some orders in here of course you'd have customers a lot of stuff so let's say this is an online order and it's i just hit the control semicolon to put today's date in there okay another online order control semicolon whatever all right let's put a 2 in there 2 is going to be shipped so this one we do want to see the ship date all right same thing and then another two okay and the order date's really kind of meaningless and then another one all right and of course you'd have a combo box here for the you know to pick the order type and this would open up a bigger order form with all the details but you get the point so for my shipped dates right over here all right these will actually have ship values the online orders don't get shipped it's it's downloadable like i used to ship cds and i used to have to keep track of when i shipped them okay 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 right okay now bruce wants to hide this field completely now here's how you would normally do this on a single form okay you're going to go to design view and and to hide any particular field in here all right based on some value we're going to go in the on current event okay the on current event is right here under events the on current event fires when you move from record to record all right let me show you let me show you first in a regular like the customer form here okay 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 but here's how it works design view open it up right go to events on current hit the dot dot button that opens up your visual basic builder okay i just shrank 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 gonna always use so in here i'm gonna say if state equals florida that's a field on my form then zip dot visible equals false otherwise because you have to take into consideration what happens if it's not florida zip dot visible equals true right so we're going to hide the zip code for people in florida why i don't know that's just what i picked okay so now when i open this up notice zip code is missing because this person's from florida go to the next record and now it shows up okay 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 say we have let's actually just go to our order form this guy let's say we have a situation where if order type equals 1 then i want to hide the ship date so again design view come into the on current event dot dot dot right there if order type id equals one then it's an online order and in fact something like this i have to put a little note here because what is order type id one right online order all right then it's going to be ship date dot visible equals false else ship date dot visible equals true and if you have more than two conditions you can put in there you know else if two else if three and so on all right right else right here ship okay save it close it close this down let's take a look and see what we got here okay 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 okay 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 all right so let's go in here let's remove the on current event just get rid of the whole thing delete goodbye all right we don't need you we're going to use conditional formatting because conditional formatting will work between the records all right so go to format click on ship date all right that's the field we want to format conditional formatting all right new rule now we want to use a different field to perform the conditional formatting so we have to change this to expression is okay i have another video that covers this in detail i'll put a link down below it's conditional formatting on a different field all right the value that we're looking for is in order type id so put in here now here you gotta be careful if you just put in order type id then axis will convert that to a string so you have to make sure you put the brackets around it here all right this is one of those places where you have to make sure you use them okay so if order type id equals one then you have to set it to some color with conditional formatting so 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 alright 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 so if you want to make them look like they're not there the best thing to do is just match the foreground color with the background color all right right here so design view come into here go into the properties for your details section now i like to use standard colors all right 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 all right i want these standard colors down here and i think the one we have is that guy right yeah all right these are hex colors and i don't use the alternating back colors i'll i think those look dumb all right they're okay on print outs when you print stuff out but on the screen no i don't like them all right so bottom line is make sure these both match copy one of those all right now come into your conditional formatting again format conditional formatting all right open this guy up change the background color to go to custom and paste that in right there okay there it is now make the foreground color the same thing it'll show down show up down here on recent colors see now it looks like there's nothing there okay hit okay apply okay save it close it up and then reopen it and now look it looks like it's gone yeah there's still a border there you can kind of see that there's nothing you can do about that unless you want to turn off all the borders all right but i think that's that's fine it looks like it's not there now what you can do because you can still technically type in here what you should do also is you don't want them putting some value in there because right now i'm actually typing okay and nothing's happening let's see one let's see if i get something in here yeah yeah so you can actually type values in there okay but what you could do is you can lock that field using your on current event so they can't type values in there all right see i entered a value because i don't i couldn't see what i was typing okay and if you highlight it now see sometimes you can still see it depending on your color scheme so 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 one then we gotta make those locked so ship date dot locked equals true else ship date dot locked equals false yes this will lock all of them but it's not a visible change so it doesn't matter okay this is locked right here and i can't type anything in there it looks like i still got a value stuck in that field let me go let me go see what i did here hold on uh yeah i typed that in when i wasn't just when i was just goofing around okay come back into here all right now i can't type in there but if i click down here i can see it doesn't matter if you use properties like that because when you move from record to record it gets reset but colors visib visible property all those kinds of things will show up in here okay 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 gonna have to live with it but at least this visibly looks better for your users okay they get the point you could do the same thing if you wanted to make this gray you didn't have you don't have to necessarily match that background and foreground color like i did or you could just gray these out that indicates that they can't type there but the bottom line is that's how you change the values like that on a can on a continuous form is using conditional formatting you can't use things like visible all right foreground color background color all those things because it'll apply to all of them so 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're not all they don't all have extended cuts but most of them do and if you want to get access to the extended cuts then you can become a member and silver members and up 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 tech help 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 tech help videos are going to keep coming as long as you keep watching them i'll keep making more and they'll always be free if you enjoyed this video please give me a thumbs up and feel free to share it wherever you think it might help people who are interested in access make sure you subscribe to my channel which is completely free and click on the bell icon to select all to be notified every time i post a new video youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link below to join my mailing list click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more now if you have not yet tried my free access level one course check it out now it covers all the basics of microsoft access it's over three hours long and you can find it on my website or on my youtube channel i'll include a link below you can click on and if you like level one level two is just one dollar and that is 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 tech help page and you can send me your question there if you have a specific problem you need help with or you'd like to discuss having a database built for your needs i do offer one-on-one consulting be sure to follow my blog and find me on facebook twitter and of course youtube once again my name is richard rosst thank you for watching this tech help video brought to you by accesslearningzone.com i hope you enjoyed this video and you learned something today i'll see you again soon you

Channel: Computer Learning Zone

Views: 5,790

Keywords: microsoft access, ms access, #msaccess, #microsoftaccess, hide field, continuous forms, visible property, oncurrent event, all fields, single field

Id: LOhbnARvhLw

Length: 16min 20sec (980 seconds)

Published: Mon Dec 07 2020