Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Discover how to manage data entry and reporting tasks more efficiently using Access 2010. Author Adam Wilbert presents lessons on designing forms, organizing and displaying data with form controls, creating flexible queries, and building a form-based navigation system. The course also shows how to build reports from wizards and queries, highlight important data with conditional formatting, and automate reporting processes with macros.
So far we've taken a look at creating navigation forms to move around our database, we've created a reference form in our employee directory and we've created a form that looks up orders that include a particular product. Let's now create a form that can be used to import records into our data tables. We'll create a form that can be used to capture information about new customers. I'm going to close our Main Menu window here and I'm going to click once on my DirectCustomers table and go to the Create tab and by clicking on Form we'll build a quick form that I can then edit. So, there's my form here, it's showing me data at the top and if I scroll down I can see that Access automatically put in a subform.
Let's go into Design View. I'll change my View to Design View and we'll clean this up. First, I'm going to scroll down and I'm going to get rid of that subreport. I'll click on it and press Delete. I'm going to scroll back up. Next, I want to get rid of these headers. So, I'll click on the Detail section and then right-click and turn off Form Header/Footer here. Since, there's content here, I've got an icon and this label. Access is going to confirm that I want to delete those. So Yes, get rid of that. Next, I want to realign some of these objects. They're pretty big for the data that's inside. Now, by default Access groups everything together so it's easier to edit in Layout View.
I think it's even still easier to edit things in Design View. In order to do that though, I need to ungroup these objects. So, I'm going to press Ctrl+A to select everything. I'll go the Arrange tab and I'll say Remove Layout. Now, these elements are all individual. I can deselect them by clicking off in the space above. I'm going to resize this Customer box here, make it a little shorter. Now, I'll select everything again, and I can go, Size/Space>To Shortest, to make them all the same height. Then I can go to Size/Space and say Decrease Vertical to make them all evenly spaced and decrease the size a little.
Now, I'll do that a couple of times to bring them all together. Size/Space>Decrease Vertical. We'll do it a few more times to nudge them all together. Next, I'm going to resize my form. So, I'm going to deselect all of these objects and then I'm just going to select the ones on the right. I'll scroll over, I'll grab the right edge and I'll bring it in to maybe about 3 inches. Then I'll grab the right edge of my form and drag it into about 3.5. I'll scroll over. It's looking good. I need to bring the bottom up now. So, I'll scroll down. So, I'll scroll down and drag the bottom up, maybe about 4 inches.
That leaves me enough room at the bottom to add some command buttons. Now, let's take a look at some of the properties for our form. I'll select out form selector here between the rulers. I'll go to the Other tab here and I'm going to make this a pop-up window. So, instead of No, I'm going to choose Yes. I also want to make sure that the user interacts with this form or closes it before they can do anything else in the database. So, I'm going to choose Modal to Yes as well. In the Format tab I can find the Caption property. The caption is what's going to appear at the top of the window. Here, I'm going to say Add a new customer. I'll press Enter and then finally in the Data section in the Data Entry property here, it's currently set to No, I'm going to change that to Yes.
What that will do, is it'll force this form to only enter in records into the customers table. It's not going to allow me to view records that are in the customers table, it's just for data entry. Let's go ahead and press Enter to finalize and we'll view our form here. I'll switch to Home and view into Form View. So, there's my form. I might want to get rid of these record selectors and this bar over here on the right as well. So, let's go ahead and go back into Design View. I'll right click, Design View. I can find those into the Format tab. We'll scroll down till we get to Record Selectors, change that No and Navigation Buttons, No as well.
Now, we need to add a couple of buttons, so the user can interact with our form. We'll go to the Button wizard by clicking the Button control and pressing the button there. Now, let's take a moment here and think about the workflow here. When you enter in records into Access, Access is automatically saving those records straight into the data table. So, if the end-user comes to this form and they type in some values and then they close the form, what's actually happening is that Access is taking the data and saving it to the table before the form is closing. So in essence, closing the form is the same as saving the data and closing the form.
If I want to have the ability to cancel out, so for instance, you might have typed something in and then decide you don't want to do that, before I can cancel, what I need to do is make sure that these fields are cleared out, that they're empty; they don't contain any data before closing the form. If they contain anything at all, that's going to get saved into the data table. Now, I want to make sure that I have an easy workflow for my end-users and they don't really have to worry about that. So, I'm going to create two buttons here. One is going to be Save Customer and the other one's going to be Cancel Entry, but I need to think about these macros a little bit in order to make the functionality work as intended.
So, this first one is going to be the Save Customer one and I can go to Form Operations and Close Form, and the act of closing the form will automatically save those records into the table. Let's go ahead and say Next. The Text button, instead of Close Form, we're going to say Save Customer, and we'll say Finish. So, there's my first button. The second one is going to be a little bit more complicated. We'll add this button control; we'll add that down below. This one we need to clear out the records first. So, we're going to go to Record Operations and then Delete Record, that will clear out everything within this form.
Let's go ahead and say Next. The Text for this button is going to be CancelEntry, we will press Next and Finish. Now, when I click this button CancelEntry, it's just going to clear out the form. I'm going to actually edit this macro, so that it clears out the form and then closes it as well. So, again, for the button, we'll go to the Event tab, the On Click event is this Embedded Macro that we just built with the wizard. I'll click build to edit it, and you can see all of the error controls that Access added into this macro. All I need to do is scroll to the bottom, we'll add a new action and we'll say CloseWindow.
We can leave the Object Type and Object Name blank. It'll close the active window, which is going to be that Add Customer form. I'll close my macro and save that change. Let's go ahead and format our buttons, I'll Shift+Click to select both of them. Go to Format, we'll choose our green style, so we're consistent throughout the database. We'll go to the Arrange tab; we'll Size/Space them To Widest, and then we'll Align them to the Right. Now, they're aligned inside the same, I can use my arrow keys to nudge them into position, and I'll just put them over here on the right. So, they align up with the edge of my text boxes. All right! Let's go to the Home tab and view our form here.
So, there's my Add a New Customer form, it's a pop-up form. I can type in some values here. I can Save the Customer or I can Cancel Entry. Right now I'm going to go ahead and cancel this entry. It's going to ask me if I want to save this and I'll go ahead and say Yes, I'll save that form and it'll ask me for a name. I'm going to create this as Add a new customer, that's fine. Go ahead and say OK. Now the final step of our workflow is to add this into our Main Menu structure. I'll double click to open up our Main Menu and then I'll right click on it to go into Design View. We'll add a button here that says Form Operations, Open the Form, Next.
We want to open the Add a new customer form, Next and Finish. Now, we've got a button that'll edit that. It's got an icon on it, I can change that. If I go to the Format tab where it says no Picture Caption, I can change that to Right. We'll add text to the right side. The caption here will be Add a customer. I'll make my button a little wider, we'll format its color, and we'll move it into position. Let's go ahead and close our menu. We'll save our changes and we'll look at our workflow now.
I'll scroll down, double click on Splash Screen. We'll see the Splash Screen works. It opens up the Main Menu. Now, I can click Add a new customer. I get my new customer window, I can type in the values and Save, or I can cancel the process. So, data entry tasks don't really have to be a chore, with a little thought into the layout of a data entry form, your end-users will be able to quickly and accurately capture the information that you need to store in the database.
There are currently no FAQs about Access 2010: Forms and Reports in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.