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.
When it comes to displaying data in your forms there are three objects which are used most often, they are the Text Box, the Combo Box and a List Box. For this exercise we're going to create a new form based off this Reviews Table. Let me go ahead and open up the Reviews Table and you can see that this a place where you can collect information about product reviews from our customer. I've got a review identification number, product ID that's being reviewed, the rating given, any comments and whether we can use this review publicly on our website. We're going to build a form that'll add records to this table, let's go ahead and close this table, let's go to the Create tab and we're going to Create a new Form and Design view.
Now the first thing I want to do when creating a new form is connect it to the table where the data is going to go. In the Property sheet for the Form, I'll go to the Data tab and I can set this Record Source here by using the drop down menu to the table I want to connect it to, in this case Table Reviews. Now I can start adding the fields that'll interact with the table, there is a couple of way I can do that if I go to Add Existing Fields here, it'll show me all the fields within that table. Go ahead and grab Comments and drag-and-drop it over, this adds a large text box to my form; I am going to move this over a little bit.
Now we can do this in other way too, we can add a text box manually and connect it to our table. The text box control is up here on the ribbon it's got the ab icon. If I click that and add one to my form, it's currently unbound; it's not connected to the table. I can bind it by going back to the Property Sheet and the Control Source for this text box now, I can use the drop- down menu, and it'll ask me what field in the table to connect to. For this one I'm going to connect to the ProductID, let's go ahead and view this form in Form view. Now I can scroll through the records that are already in my Reviews Table, so for instance, if I am on record number 1 which is for this particular oil and this is the comment.
I can scroll through them; there is review 2, review 3, review 4, and if I go to review 5, it's blank; this will be a new review, So if I wanted to add a new review to this Reviews Table, I will just type in the values here, but the problem with the text box is it doesn't give you much guidance. So for instance, I'm not really sure which ProductID, I know the name of it, but I don't know what its ID numbers is. So the text isn't giving me any help. We can make this a little bit better by using a List Box or a Combo Box to provide the values that I can just choose from the list. Let's switch back to Design View. I'll go to the View menu and choose Design View, let's go ahead and get rid of this text box.
I'll select both it and the label and I'll press Delete, and a List Box and the Combo Box function almost identically. This right here is the Combo Box. The List Box, if I scroll down, is right here. The main difference between a Combo Box and a List Box is that a Combo Box displays one line and you can expand the box to show the list. The List Box is always expanded and has a scrolling bar on the right side to move up and down, let's go ahead and add a Combo Box. Let's scroll back up here and choose the Combo Box icon, and I'll add it into my form. When I add a Combo Box the wizard starts and it asks me a couple of questions.
The first screen asks where the value is going to come from that will populate the list. We can choose to get them from another table or query, we can type in a list manually, or we can use a Combo Box actually jump to a record based off something I select. I am going to have the list populated from a table or query. Let's go ahead and say Next, the next screen is going to ask me, well, which table or query? Now let's pause here for a second. What I want to do is enter in product reviews into my Reviews Table. You might think, okay, so we need to connect this to my reviews table, right? But if I do that it's going to generate a list of products that are already in the Reviews Table in this case I only have four reviews, so I only should be able to choose from those four products.
In this case I actually want a full list of all of our products to choose from, so I'm actually going to pull this from the Table Products, let's go ahead and say Next. Now which field from the products table do I want to use? I don't want to use the ID number, that was the problem we had in the first place. I want to use the Product Name, let's go ahead and select that to move that to the selected field section, and I'll press Next. We can sort this ascending or descending, I'm just going to accept the default and say Next and here is the list that's going to show up in the Combo Box, let's go ahead and say Next. The last window says, do we want to remember the values for later use or do I want to store this value in a field? We want to store the value back into the Reviews Table here.
So we're going to store the values in the field and the field that I want to store it in is ProductID, go ahead and say Next, and I'll set the default name and say Finish. Okay, let's make this a little bit wider and I'm going to move it over to a line with my Comments box. Now let's go ahead and take a look at in the Form view. Now I've got a drop down list where I can select the oil from, so again, I'll go to record number 5, which is the new record, and now I can select from the list the oil that I want to review. Let's say the Light Oil and the 8oz size, I can type in my comment, this is a good size product. I'll press Enter and I'll move to a different record just to finalize those changes or I can press this Pencil icon over here to commit the change to the table.
Now if I review my Table Reviews, I'll double click on it to open it. I'll see that new review right down here, I'll review the Light Oil and the 8oz size and this is the ProductID for that and this is the comment that I left. We're going to take a look at how we can use the check box and a rating system in the next movie. So that's how you would use a Combo Box. To populate a list of values that user can select from, it provides additional context over just a standard text box that allows them to type in whenever they'd like, without any sort of instruction and what to do. The Combo Box and List Box are much friendlier controls for your end users.
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.