Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Linking controls

From: Access 2010: Forms and Reports in Depth

Video: Linking controls

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.

Linking controls

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.

Show transcript

This video is part of

Image for Access 2010: Forms and Reports in Depth
Access 2010: Forms and Reports in Depth

38 video lessons · 14658 viewers

Adam Wilbert
Author

 
Expand all | Collapse all
  1. 1m 27s
    1. Welcome
      1m 2s
    2. Using the exercise files
      25s
  2. 15m 53s
    1. Introducing forms
      2m 41s
    2. Designing for the end user
      45s
    3. Exploring the database
      1m 49s
    4. Creating a form with the Form Wizard
      6m 43s
    5. Refining the form in Layout view
      3m 55s
  3. 24m 33s
    1. Organizing the form elements
      7m 14s
    2. Formatting
      4m 48s
    3. Modifying the form through its properties
      6m 56s
    4. Adding a header and some polish
      5m 35s
  4. 1h 2m
    1. Introducing form controls
      3m 48s
    2. Using lines and rectangles
      2m 48s
    3. Organizing screen space with tabs
      4m 47s
    4. Adding buttons
      5m 3s
    5. Linking to external content
      4m 15s
    6. Entering and selecting data
      5m 8s
    7. Controlling input with option groups
      6m 0s
    8. Attaching documents
      6m 49s
    9. Attaching images
      5m 8s
    10. Understanding the subform control
      4m 13s
    11. Adding charts
      7m 9s
    12. Linking controls
      7m 41s
  5. 21m 42s
    1. Creating the main menu
      8m 49s
    2. Adding a splash screen with startup options
      5m 35s
    3. Creating a customer form
      7m 18s
  6. 45m 20s
    1. Grouping and sorting data
      4m 36s
    2. Understanding report structure
      6m 12s
    3. Building reports from wizards
      5m 0s
    4. Building reports from queries
      6m 34s
    5. Formatting conditionally
      6m 59s
    6. Calculating fields
      4m 35s
    7. Adding the finishing touches
      4m 49s
    8. Populating pre-printed documents
      6m 35s
  7. 15m 8s
    1. Printing reports
      3m 6s
    2. Tweaking the design
      7m 10s
    3. Automating the workflow with macros
      4m 52s
  8. 58s
    1. Next steps
      58s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Access 2010: Forms and Reports in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.