Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Now that we have seen all of the controls that we have at our disposal when creating forms and reports. Let's bring this full circle and piece a couple of them together to control the chart that we created in last movie. So in the last movie we ended up with this chart here, I am going to double-click on it to open it, and we see that's a graph of Orders by Month. And right now it can see all of the data for my entire database, all the way from January 05 through July 2010. That data is coming from this query here; I'll double-click on the query to run it. This is the data that is getting fed into the chart. If I switch this query in to Design View using the button up here, Design View, I can see how the query was created.
Now what we can do in order to modify, with the data that gets put into the chart, is I can add a criteria here, for instance, I can select a date range. For instance, in this criteria under OrderDate, if I type in >1/1/2009, Enter. Now rerun the query, it gives me a different dataset. Now I only have 598 records instead of 2200. If I go back to my chart and refresh it, actually I need to save the query, so I will go back to query, press Ctrl+S to save it, then go to the chart and refresh it, I will see a different chart.
It's only reporting the data now based off of that new criteria. But what we can do is create a form that will update this criteria in the query here, if I go back to Design View. It will update this criteria dynamically based off user selections that they can type in themselves. So let's go ahead and create that form to capture the user intent. I will go to the Create tab and we will create a new Form in Design View. We first need a place to capture data, we could use a text box here and have the end-user type in the values, but that really doesn't give them much direction, for instance, the end-user is not going to know what data is available, they are not going to know what the beginning date is, what the end date is for our database.
So Combo Box is a much better choice. The Combo Box will allow them to choose a date from a list of possible values that we definitely know up here in the database. The Combo Box is this one right here, I will click on it once and I will add one into my form. The Combo box Wizard starts up and I am going to tell you upfront that we are actually not going to use the results of this wizard, but I want to go through the wizard a little bit, so I can't show you why this isn't going to work for us. We will go ahead accept the first selection here to get the values from another table or query, we will say Next. The dates in our database are coming from the Orders table.
So I will select that here and say Next, and here's the Date field, the OrderDate, and I will add that over to my selected fields, go ahead and say Next, we will leave the default sorting, go ahead and say Next. Now we can see why this isn't going to work for us. In our Orders table we have multiple orders on the same day, for instance I have two orders on the 10th of January 2005, and three orders on the 12th of January 2005. If I leave it this way, Access is going to display a list with all of these duplicate values in it. I just want each day to show up one time. So let's go ahead and say Cancel to back out of the wizard and not use any of this.
And now we will set the data source for ourselves. In the Property Sheet -- and I can open that with the F4 key or Alt+Enter or this button here, I'll take a look at the data tab. So the row source here is currently blank, the row source is what we will populate in the list in our Combo Box. I can build a query manually using the Build button here and I will click on that and the Query session opens. Now we could choose to pull data from our Orders Table and I will Add and then Close. And I will add our OrderDates here. Now we will turn on our Totals row to group to like values together. So when I click on Totals up here in the ribbon, that adds this Group By section here, so all the dates that are same will collapse into each other.
I will say Run, and now I get only each date represented one time in my table. So let's go ahead and close this, we will save our changes and then I will take a look at my form to make sure that I am getting the results that I expect. I will make it a little wider first and then we will switch our view to Form View. Now I have got a Combo Box here and when I select the down arrow, I get all the dates that are present in the orders table. Okay, let's go back to Design View. Now what I would like to do is create a selection box where the end user is going to type in a start date and an end date for the graph that they would like to see. So I am going to need two of these Combo Boxes.
What I could do is highlight both of these the label and the combo box, I will press Ctrl+C on my keyboard and then Ctrl+V to paste, to copy. Next let's go ahead and name these, I am going to double click on the first label here and type in Start Date and then press Enter. And I am going to double-click on the second one and type in End Date, and press Enter. I can move these over a little bit I'll use the handle in the upper left hand corner to drag this one over and I can align both of them together to make sure they're aligned by highlighting both of them and going to the Arrange tab, Align to Right.
Now I want to right align the text to make sure that's flush against this edge. We will go to the Format tab and press the right align button. Next I need to name both of these boxes here where the users are going to make their selection. Once they're named I can reference those names over here in our query. Let me click on the first box here and I am going to go to the Other tab. The name right now is Combo0, and I am going to change that to startdate, all one word, and press Enter. Then I am going to go to second box here, and I am going to changes its name to enddate all one word and then press Enter. Now my form is almost complete, I do need to add a button to open up the chart.
I will switch to the Design tab; I will click on my Button button, and I will add a button down here. The wizard will start, I am going to go Form operations, open a form and say Next. The form that I want to open; that's the chart form, go ahead and say Next. Where we want to display on the button? I can either have the picture or a text, instead of Open Form, it's a little bit confusing for my end users, I think View Graph is a better option, and we will go ahead and say Next. We well accept the default name and say Finish and now I have got my button here and I will just go ahead and move it in a position. Okay, let's switch in the Form View, I can test my functionality, okay, I could see all the dates there, and I can see dates there.
Go ahead and save our form here, so I will press Ctrl+S to save it, and I am going to name this GraphDateRange and then press OK. Now we just need to hook to our query into the selections that the end-users will make here. I will go to my query and in the criteria I have currently got this >1/1/2009 typed in, I am going to highlight all of that and press the Delete key and then I will right-click to open up my Expression builder, I will go to the Build option. Now I just need to write in the expression that I want. And for my Date range, I can use the between and syntax, so I will write between, press the Spacebar, then I can find the reference that's in my form.
I will drill into the Database folder here, by using the plus button, I will go to Forms. Right now I have got the form open, so I can find it in Loaded Forms, the GraphDateRange, and I will double-click on startdate. Next, I will type the word and, another space, and I will double-click on enddate, that will add the references to the enddate field and the startdate field that are on my GraphDateRange form. Go ahead and say OK. That adds the criteria down here into my query. Now I can go ahead and save my query, Ctrl+S to save it, and I will close it out. Now I can go ahead and choose dates in my form here on the GraphDateRange form.
For instance, I can choose, I will scroll off through this list, may be March 1st, 2007 and I will go to the enddate, and I will scroll down, and may be I will choose a date in 2008, how about February 1st 2008? Now when I press View Graph, it switches the chart object, because it was already open, I actually need to refresh it, so I'll press Refresh to get a new look at the data. If the chart was closed, for instance, I will close it now, and press View Graph, it would show me the new data automatically. So for my end-users the chart will closed, they choose their date range, let me change it up a little, let me change to December 1st 2009 and go ahead and say View Graph.
There we go; I have got a new date range here. So using a few simple objects, I was able to create a couple of selection Combo Boxes to choose options from, and once I press the button I instantly get a chart that meets my specifications. I can change my selection as often as I want and get an endless number of charts that highlight exactly what I want to take a look at. Once you get comfortable with each of the control object available to you with in Access, it's going to be up to you to be creative and figure out effective ways to hook each of these pieces together to create your database application.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64717 Viewers
80 Video lessons · 124327 Viewers
52 Video lessons · 60259 Viewers
59 Video lessons · 46095 Viewers