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

Obtaining parameters from forms

From: Access 2010: Queries in Depth

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.

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.

Show transcript

This video is part of

Image for Access 2010: Queries in Depth
Access 2010: Queries in Depth

46 video lessons · 13390 viewers

Adam Wilbert
Author

 
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

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

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.