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.
You can have a basic form up and running and as little as three clicks of a mouse. For instance in the Two Trees database here I can go to the Navigation Pane, click on the Orders table, on the Create tab of the Ribbon I can click on Form in the Forms group. Access will create a form here based off of that Orders table. Right now I am in Layout View which is one of the two editing modes. In the View menu here I am going to switch to Form View so I can work with the data. The data in this form is coming straight out of the Orders table. Right now I'm looking at record number one of 2200. I can see that it's OrderID 10000 that was placed on the 10/01/2005 and then it was for customer at 2461.
Let's go ahead and see this data in the Orders table. If I double-click on the Orders table, I can see in the EDS that record number one is order 10000 on the 10th of January for this particular customer. Now the data in this form is a live link to the data in the table. For instance if I click on the OrderDate field here and get the Calendar Picker and I can change that date, click on the calendar and change it to maybe the sixth. I will click the Pencil icon here to finalize that change and then I can go to look at the table again to see the updated value in the table. Now this change goes both ways, I can make a change in the table and we will see that update in the form.
I will click on OrderDate again, click on the calendar and change it back to the 10th. Once again I'll click on the Pencil icon or I could press Enter and move to a different record, we will go back to the form and we will see that the data is changed here. I'd like to think of these squares as little windows into the table cells that they represent. So the data here is the exact same data that you're seeing here. Okay let's go ahead and close the Orders table. This form while it is technically correct it's showing me data straight from that data table, it leaves a lot to be desired. For instance it's showing me this customer ID, but I don't know who this customer is, it's also showing me this ProductID, but I am not really sure exactly which product this means.
I would like to have a better text description of this customer's name and the ProductID. Because our Access database is built around the idea of related tables you can get that information by incorporating a few more data sources into our form. Let's use the Form Wizard that will walk us through the process and get a more useful form than the one we have now. I will go ahead and close this form out and I am not going to save it, I will say No to save. Now we can to up to the Create tab on the Ribbon and click on Form Wizard, that will start up the wizard where we could choose the tables that we want to incorporate fields from. For instance let's go ahead and build a form similar to the one we just had except it has those other pieces that we wanted, the name and the ProductID, we will go to the Orders table and I will choose OrderID, OrderDate and then we will switch to a different table here.
We will pull in information from our direct customers table. I will click on that and we will add FirstName and LastName, and finally I will choose my Products table, I'll go back up to the dropdown, choose tbl_Products here and we'll get the ProductName and I'll add that from the available fields to the selected fields section. We will go ahead and say Next. The Next screen asks us how we want to view our data, if I choose by tbl_Orders it's going to show me one page of the form for every record. If I choose by tbl_Products you will show me a product name in all of the orders that are associated that included that product, likewise if I click on DirectCustomers they will show me a customer name and then all of the orders that that customer has placed.
We will look at these options here in just a moment. So for now just click on tbl_orders and we will click Next. We will go ahead and choose the default format for the layout, say Next here and now we can give our form a title if you'd like. I am just going to accept the default title here and I will choose to open the form to view or enter information, we will say Finish. So already we've got a form that's better than the one that we created just a moment ago. I have got the same OrderID and OrderDate, but now I know who placed the order and what exactly they ordered. Let's take a look at a couple of other options that we had in the wizard. I am going to go ahead and close this form here, go back to the Create tab and choose Form Wizard again.
Let's build the same form and we will choose a different option later on. So I will go for the Orders table, I want OrderID and OrderDate, go back up here to the DirectCustomers table where I got first name and last name, once again I will go to the top and this time I will choose tbl_Products to get the product name. We will say Next. And this in this screen we are going to choose by tbl_Products. Now Access gives us two different options, I could either have the Form with subforms or Linked forms. Let's go ahead and choose Form with subforms now and we will look at Linked forms in just a moment. Go ahead and say Next, we will accept the Default layout, Next.
We will accept the default names, go ahead and say Finish. Here is a form with the subform attached to it. Now I could use a little bit of help in the layout, but what it's showing us right now is that I have got 90 different products. For those 90 products these are all of the OrderIDs and OrderDates and customers that have placed orders for that product. I could scroll through my products here, for instance, I will go to product number 8, the Extra Virgin Oil, 32 ounce size and I guess all of the customers that have ordered that product. And I've had a total of 21 different orders. Let's go ahead and close this and we will look at the other option.
Once again to the Create tab Form Wizard, we will build the same table. So I will take OrderID and OrderDate, we will go to the Customers table, we will add FirstName and LastName and we will go to the Products table and choose ProductName. Okay, once we get those five fields we will say Next. This time we are going to choose by tbl_Products again, but we will choose the Linked forms option. This will show us the same data in two separate forms that we got on the last one with a subform, go ahead and say Next. This time I am going to change the names, we are going to go ahead and use these two files in next exercise.
So for the first form we are going to call it Product Selection. In the second form we are going to call Orders By Product. Go ahead and say Finish here and Access will update those forms. Now we've got a little bit of a problem here the default selections create this button that appears behind this label. So if I try and click on the button, I can't, the label is in front of it. In order to fix this I actually need to jump into Layout View for just a moment, we are going to look at Layout View in the next movie. But for now just go ahead to the View menu, choose Layout View, we will click on the title here and I am going to click-and-drag to side it over.
Then I'll click on the button to click-and-drag and side it over and it will move the label back. Okay, now that they are separated we will go back into Form View and I will test out the functionality. Just like the last form I can cycle through my products here, so I'll go to record number 6 here, the Extra Virgin Oil, and the 8 ounce size, and I will press the Orders By Product button. When I press that I get a second form that opens. You might have noticed in the Navigation Pane here, when it opens up it's showing me all of the records for each customer that have placed orders for that product.
You can see I have a total of 27 records or 27 orders for that specific oil. So using the Wizard we were able to create three slightly different user experiences and using data from multiple-related tables we were able to get details on order activity at the Two Trees Olive Oil Company. Now as is typical with the objects created with a wizard we have a good starting point but we need to clean this up a bit before it's presentable. We will take a look at the Layout View in the next movie to finish this form.
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.