New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Start learning with our library of video tutorials taught by experts. Get started

Access 2010: Queries in Depth
Illustration by Neil Webb

Obtaining parameters from forms


From:

Access 2010: Queries in Depth

with Adam Wilbert

Video: Obtaining parameters from forms

I want to start this movie by passing on a fundamental concept that can change the way you think about Access. Every objects, every form, every button or checkbox or label or text box, everything in Access has a value associated within the database. And every name or value can be used as an input somewhere else. Really understanding this concept will set you on the path to becoming a true Access rockstar. And here's why. Access objects, that is the tables, forms, queries, reports, they are all presented by the interface as individual components of your database, but in reality it's the interaction and the interconnections between objects that allow your database to do some pretty amazing things.
Expand all | Collapse all
  1. 9m 9s
    1. Welcome
      1m 10s
    2. Using the exercise files
      41s
    3. Introducing the database
      4m 29s
    4. Previewing the course
      2m 49s
  2. 17m 17s
    1. Understanding queries
      3m 31s
    2. Following naming conventions and best practices
      2m 56s
    3. Using the Query Wizard
      5m 21s
    4. Exploring the design interface
      5m 29s
  3. 26m 39s
    1. Defining criteria
      5m 40s
    2. Understanding comparison operators
      3m 19s
    3. Defining the column headers
      2m 49s
    4. Exploring the property sheet
      7m 32s
    5. Printing query results
      2m 41s
    6. Working with joins
      4m 38s
  4. 14m 14s
    1. Understanding parameter queries
      4m 27s
    2. Obtaining parameters from forms
      5m 17s
    3. Creating a combo box
      4m 30s
  5. 23m 24s
    1. Understanding the Totals field
      5m 31s
    2. Creating aggregate calculations
      3m 31s
    3. Exploring the Expression Builder interface
      4m 28s
    4. Using mathematical operators
      5m 46s
    5. Applying text functions
      4m 8s
  6. 24m 23s
    1. Understanding dates as serial numbers
      2m 42s
    2. Specifying a range of dates or times
      3m 47s
    3. Formatting dates
      4m 31s
    4. Using other Date/Time functions
      3m 47s
    5. Defining today's date
      2m 41s
    6. Calculating time intervals
      6m 55s
  7. 20m 9s
    1. Introducing the conditional IIf function
      2m 57s
    2. Creating an IIf function
      7m 31s
    3. Nesting IIf functions
      4m 57s
    4. Using the Switch function
      4m 44s
  8. 20m 41s
    1. Understanding the reporting tool
      2m 13s
    2. Building the form
      6m 57s
    3. Building the query
      5m 4s
    4. Building the report
      3m 30s
    5. Finalizing the reporting tool
      2m 57s
  9. 25m 37s
    1. Finding duplicate records
      2m 17s
    2. Identifying unmatched records
      2m 29s
    3. Creating crosstab results
      2m 57s
    4. Creating backups
      1m 29s
    5. Creating update queries
      3m 22s
    6. Making, deleting, and appending records
      5m 36s
    7. Uniting tables
      3m 16s
    8. Embedding SQL code in queries
      4m 11s
  10. 1m 0s
    1. Next Steps
      1m 0s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
Access 2010: Queries in Depth
3h 2m Intermediate Jun 16, 2011

Viewers: in countries Watching now:

In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.

Topics include:
  • Naming conventions and best practices
  • Working with joins
  • Using comparison operators
  • Defining criteria for select queries
  • Creating parameter queries
  • Creating calculated fields
  • Working with dates and times
  • Using the Expression Builder
  • Creating conditional statements
  • Making, deleting and appending records
  • Building reports
Subjects:
Business Databases
Software:
Access Office
Author:
Adam Wilbert

Obtaining parameters from forms

I want to start this movie by passing on a fundamental concept that can change the way you think about Access. Every objects, every form, every button or checkbox or label or text box, everything in Access has a value associated within the database. And every name or value can be used as an input somewhere else. Really understanding this concept will set you on the path to becoming a true Access rockstar. And here's why. Access objects, that is the tables, forms, queries, reports, they are all presented by the interface as individual components of your database, but in reality it's the interaction and the interconnections between objects that allow your database to do some pretty amazing things.

In the previous movie we looked at how we can provide our end users with a pop-up dialog box to define some query parameters. Let's take that a step further and see how we could define those same parameters using form controls. I've currently got the frm_SalesRange form opened up from my Chapter 3 custom group. I've had two boxes here that I can enter values in. I'm looking at the total lifetime sales to each customer and I have the option here to enter in two parameters. Let's say I am interested in all of our customers that have spent between $200 and $300 with us.

I could types those values here, 200 and 300, and run our query. The query returns only the customers that we are interested in. So let's see how this is working. Let's go back into our form and we'll switch into Design view. Let's open up the Property sheet by clicking Property sheet on the Tools section of the Design ribbon. We'll switch over to the other tab and if we click on each of these boxes-- I will click on the first one here-- we will see that this box has a name of low. If I click on the second box it has a name of high.

So in our query we can reference these two boxes, the low and the high box on the form of frm_SalesRange. Let's go into our query and use those as inputs for our parameter. We will switch to our query, we will change into Design view, and we will look at the criteria for our lifetime sales field. Now I am going to right-click here and go into the Zoom box. We can see the whole field at once. In the Zoom box we can see that we are referencing the low field of the Sales Range form and the high field of the Sales Range form.

We also have some comparison operators to define the range. So we are looking at greater than or equal to whatever's in the low field in less than or equal to whatever's in the high field. Let's go ahead and say OK to this. Let's take a look at another form. I will go ahead and open up frm_EmployeeLookup. This form has a drop-down menu that asks to show all the employees in the state of blank. Qe could select the state, run the query, and Access will show us all the employees that live in that state. Let's go ahead and build this query from scratch. I will close the one that open the qry_EmployeesLookup and we will duplicate this query.

We will create a new query in Design view. Now we are interested in our employees, so add our Employees table. Let's go ahead and say Close and we will open this us. We are interested in the FirstName, the LastName, and the State. In the State field the criteria for state is going to be whatever the value that's currently selected in our form. So we need to know the name of the specific object. Back into our form we will go into Design view. With our Property Sheet on the other tab we will select this object.

it currently has a name of state. So we are going to reference the State field on the frm_EmployeeLookup. Let's go back in to our query and then put the criteria. I will right-click in the Criteria field under the State and I will select Zoom to give us a little more room to write and we will write up a hook into the form. It looks something like this. First we need to tell Access what type of object we are looking for. We are looking for one of our forms so I will write forms. Each step needs to be separated by an exclamation mark. So I will go ahead and write Forms! The next thing we want to know is what we are looking for.

We are looking for frm_EmployeeLookup, followed by another exclamation mark, and then what field on this form are we wanting. We want the State field. So our entire code read Forms! frm_EmployeeLookup! State. Let's go ahead and say OK and we will notice that Access has cleaned up our code a little bit. It added some square brackets they are each of the elements. And if we go ahead and say View, we are currently getting no results.

That's because our form is currently in Design view. The value for this field is currently unset. Let's switch back into Datasheet view and we will select a value. I will select Hawaii. Go back into our query, switch to Design view, and then switch back to Datasheet view to rewrite it. We will notice that is now picking up the value and we are only displaying the states of Hawaii. We can go back to our form, pick a new state, how about Kentucky. In our query if we refresh it, we will get the new state showing Kentucky. Now this wasn't meant to be an all- inclusive look at creating amazing forms, but I hope these examples have given you a little taste of the types of user interface tools that are at your disposal with Access.

By combining what we already know about creating robust queries with some additional insights into the larger framework of Microsoft Access databases, my goal is to get you thinking about your own projects and hopefully get you excited about applying these tools to your own database needs.

There are currently no FAQs about Access 2010: Queries in Depth.

 
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.
Upgrade now


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 Upgrade now

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: Queries 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

Notes cannot be added for locked videos.

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.