 Hi, welcome to Microsoft Office Access 2013 Part 3. My name is Sandra Butekis, and I'll be here to take you through this course so that we can see some of the great things that Access has in store for us. Now I go way back to Access Version 1, very quickly followed up by Access Version 2, where I get a chance to not only see what it was to do some data entry, but to start designing this new relational database functionality that everyone seemed to be excited about. Well, I am here to say that 20 years later we're still working with Access. It's still a phenomenal tool, and I look forward to sharing some new information and some experiences with you. In this lesson, we'll be implementing advanced form design. We'll cover how to add some controls to a form, how to create some sub forms. We'll organize the information into different tabbed pages. We'll also get a chance to enhance the navigation of a form, and we'll learn what conditional formatting is all about. Topic A, add controls to a form. In this topic, we'll learn what an original form looks like on its original layout, and then we'll get a chance to take a new target layout and configure it. We're going to learn how to put a control on a form. Now, in order to put a control on the form is fairly easy. You click on the button and you place it on the form. But the more complicated part of it is what really is a control and what do they do on a form? So the first thing to understand is a control actually controls or does actions on a form, and there are quite a few different types. So we're going to start off with text box. All a text box is, is something that you could use to display data or to accept text input. So an example, we might want to link it to a field. Please enter name, please enter this, please enter that. So a text box is just simply that. Well, what if you've had a form for data entry and you want to actually give it a label or name or title? Some static text somewhere. So here we can create a label which is just very simply descriptive text. It doesn't accept text, it just is text. We also have a list box. A list box simply consists of rows of data that can be selected. So if you think about any menu system that you've ever been in, any dialog box that you've ever used, we've all seen the list box where you click on it and you choose which one of the items in there you need. A command button does any action on demand. So an example, if I was to write a macro and that macro was to open a form, an example, I would want to put a command button at the top of that form. So that as I'm doing some data entry and I realize I need to look something up, I can click on that button and it'll open up that second form for me. We have a combo box which combines the functionality of a text box and a list box. So what I mean by that is if I have a text box, I can type anything in it. So if it says last name, I can enter in any last name that comes to mind, whatever it is I'm doing data entry from. But if I have a list box, I am confined to only the last names in that list. So one is more free form and one of them is giving you some choices. When you choose a combo box, it means you can either select it from the list or you can type it in. We have a check box, an option button, and a toggle button. All three of those are very simply a yes, no value. So an example, if you have a click and there's nothing in it, then that means no, you click in the box, and that means yes. Or you have a radio dial or an option button or a toggle button, which means one of them is yes and one of them is no. We can only pick one of the other. So we can use any of these three to display that type of data. We also have an option group and this is just simply a group of check boxes. Option buttons, toggle buttons that provide a limited number of options for the form user. We have tab controls and this is when you have several pages that are looking as a single set. Hyperlinks are exactly that, they contain a URL to somewhere. We can insert page breaks so you can separate out the pages into something more readable, more organized. You can use an image and that's gonna hold the link to an actual image. And if you wanna make it pretty, we do have a line, a line of course allows you to draw the vertical, horizontal or diagonal lines. So sometimes when you have some forms, it's nice to just simply separate data so visually it's easier to navigate. Last but not least, we have a few more. Yes, as I'd mentioned, there are quite a few different types of controls. So one of the controls is a rectangle, kinda like the line, it just allows you to dress it up. Sometimes what I do is I draw a rectangle around other controls. So if this whole section on a form contains address information, then sometimes I'll draw a rectangle around it just to make it stand out and visually a little easier to navigate. When you get into sub forms and sub reports, it allows you to insert, of course, a sub form or a sub report. And what's great about that is let's say we have a report or a form and it talks about, let's say product categories. And maybe this particular category is hardware. Well, that's a simple form, but I might actually want to find out what types of items are in that category called hardware. So I can create a sub form or a sub report or I can expand on this and actually see the products that are part of that category. We also have unbound object frames and an object is just anything you can put on there like a graphic. That's the one that we use the most. Unbound simply means it's not bound to data in the database. So it might be that you want to use it for a company logo or some sort of design. That makes it a little different than a bound object frame. Because a bound object frame means it's actually bound to data. So in the example of using a picture, you might have an employee database and part of what you want in the employee database is a picture of each employee. So this is kind of an object, meaning a picture. That's inserted, but it's bound to a piece of data. We have an attachment, which means, of course, you can add an attachment. So an example might be a Word document or an Excel spreadsheet. That's part of that record. We have a web browser control so you can actually display a web page on a form. And we have navigation controls which allow you to switch between the different forms and reports with a creation of what we call a navigation form. I just wanted to pick out a few other elements that we do like to use in the databases. We have command buttons, calendar, date picker controls, and our form layouts. So we know that we can add controls to any layout, whether it be a form or a report. But to talk about some of these other elements, sometimes they do function a little bit differently than the others. An example, a command button isn't something that simply pulls the data. The command button actually allows you to automate something. So let's say I'm going to put up a look up reference on a form. So if someone needs to go look something up before they complete the data entry of a form, we can do all of that with a command button. When you draw a command button, it's completely empty. And then you get to program the back end of it to say, this means open form. Or maybe a command button is print a list or print a report. So if you're looking at a table or some form of data entry form, maybe on the top of that data entry form, you have print report. So it does a query on the form, or it does better yet a query on the table that you're looking at the data for, and it sends it right to the printer. We can also have an element like a calendar or a date picker control. A calendar or a date picker control just means that we've said it's going to be a date and time function. And when it has that control embedded, it means when you're doing data entry and you move there, when you click a little calendar drops down. So you can type in a date, but you can also navigate through it graphically. And of course, we also have form layouts. As a reminder, a form contains zero data. It's just a way to view data. We can create a form with every single field and every single record, or we can create a form that's based on a query that only shows you a couple of different fields. So in there, we have all these different form layouts that we can use to view the data for different purposes. So if we look here at the original inventory form view, this means that we literally clicked on, let's say a table. We clicked on create a form and no questions asked, we got all fields. So in this particular case, I just want to point out we have a form header. We have the detail section. So this is going to always appear at the top of the form. If I wanted a form footer, I would put anything down here. And then the detail section is where we view things typically one record at a time. So here's the product code, the department it comes from, the supplier that supplies us with that product, the description, how many units I have in stock. So we're in design view, which is where we can add and remove elements and move things around. But we are looking at what I would consider a default form if we were to just say, throw all the fields on a form and let access do its work. Now at any given time, I can double click on any area of the screen. So an example, if I was to double click right here, I would go to a property sheet that I would allow me to further define what this background to the form is all about, not the fields, cuz I'm not on the field yet. Now this background right now is light gray, so I could double click and put me into the property sheet. Maybe then I could pick something like a red or a blue or a green or whatever it is I needed to do. Same thing with all these areas. If you were to go ahead and double click in the form header, again, you'd get a property sheet of the header, what kind of color you want, kind of other properties, design properties, form field properties, graphical properties. So here we see a different version of our inventory target layout. And notice here I've got the actual name of the form that we've been working on. And the target layout says, look, the department is over here and the rack is here and the inventory and reorder level is here. So it is a difference from the original layout where we just asked for an auto form and all the information was in a row which made it very long. And sometimes probably not very easy to navigate on the data entry basis. But to physically pick up and move the fields. And I like this layout a lot better because to me it just makes more sense. Because you'll see for data entry we're usually typing in the same type of data. So we can kind of visually go left to right, here's what we've got, here's what we've got, here's what we need, here's the last time we ordered. So I think that not only visually it's easier but I think for data entry the form works more like we would work as we enter in different types of related data. There are a lot of different styling elements on forms. We have quick styles, tab order, changing the tab order is available and an anchoring tool. So with the styling elements of quick styles, this basically takes on a form or report less than two clicks in order to quickly format it. It might be colors, it might be fonts, it could be any look and feel that you're looking for. But they build all of these different style elements into quick styles so that we can literally click on an element, it might be the entire form or a piece of that form and then choose one of the quick styles to quickly format it. We also have something called tab order. Tab order is when you're doing data entry in a form, when you hit tab, it should advance to the actual next field. Well, what happens with tab order is as you add and remove elements, the tab order changes. So the tab order is actually going to be in the order that the elements have hit the screen. Well, sometimes that doesn't work very well because we change your minds pretty often and we move things around. If you think about where I'd gone already, where here's our original form and here's what you can turn it into. Well, I moved everything everywhere. But by picking up and moving a field, it doesn't change the tab order. So now it might be that you type in a product code and you hit tab and it brings to the bottom of the page to type in something else. You hit tab, it brings you over to the right. You hit tab, it moves you to the left. So it doesn't make sense for efficient data entry and of course, this is how mistakes are made. So tab order can be changed. It can be changed by omitting fields altogether or just by resetting it from top to bottom and left to right. We also have an anchoring tool and an anchoring tool has nine different spots in which we can anchor something. So as you're adding again elements to a form or to a report, we can anchor them to certain parts of the screen so that they don't move.