access

V475/I519 Assignment 10  
(30 points)

Conditional Macros, DLOOKUP, & Binding Values:

Download MoviesDB_with_Awards.accdb from Canvas. This is the Movies Database we have been using modified to include Award and Movie_Award tables. 

You will create three sub-menu forms in this assignment that will be accessed from a Main Menu.  Section 2 contains an option for bonus points. 

Objectives:

1. Create a form that rates a Star’s productivity

2. Allow user to select a Director and produce a list of Stars who have been directed by that director

3. Let’s see what you can do--Using the Awards data provided, you will create your own form where the user selects something and displays a result. 

Methods Involved:

· Conditional Macros and “Set Value” action

· DLOOKUP function

· Control pathname (e.g. forms!specific_form_name!control_name_on_form)

· Combo-Box (drop-down list on a Form) and “binding” a value 

· Autoexec macro that opens the Main Menu when Access starts

Create a simple Main Menu that opens when Access starts and links to all sub-menus. 

1. Create a form called Star_Productivity_frm. In this form, you will select a Movie Star and determine whether that Star had a productive career or not.
Your form might look like this:

Perform the following steps:
 

Create a query that COUNTs the number of movies the Stars have starred in. The results of this may be two columns: Starnum and MovieCount. Name the query “movstar_count_qry.”
 

Start with a blank form, add a Combo-Box . Bind its source to the Star table (show Starname, but bind the combo-box to Starnum).
 

Add a textbox control next to the form; call it “Productivity”.
 

Program a macro called Productivity_Rating. When the user picks a Starname from the combo-box , the macro will execute (select After Update event). The Macro will have two actions:
(1) (using DLOOKUP with IF statement) use the movstar_count_qry query that has the COUNT of the number of movies the chosen Star has starred in.
(2) set the value of the “Productivity” control as follows (less than 3= “Not Very Productive”, BETWEEN 3 and 6= “Moderately Productive”, and greater than 6=”Very productive”)
 

Close the movstar_count query window. Name the form “Star_Productivity_frm”  and be sure to test.  
 

2. Create a form called Director_Star_frm. In this exercise, you will select a Director and produce a list of Stars who have been directed by this Director.
 

On a blank form, add a Combo-Box.  Bind its source to the Director table (show DIRNAME, but bind the combo-box to DIRNUM).
 

Create a query called “Director_Star_qry” using control pathname in the query criteria that points to the combo-box you created above. This query will have two columns: Dirnum and Starname. Note: Use DISTINCT after the SELECT statement to remove duplicate results. {ie., SELECT DISTINCT …}
 

Place a Click-Button next to the Combo-box. When the button is clicked, a query is to run that will produce a list of all STARNAMES that the selected director has directed. Use a macro to open the query results in datasheet view. Name the form “Director_Star_frm.”
 

Your form might look like:

3. Let’s see what you can do…Using the Awards data provided in this file, create a form that displays Awards in any format and method of your choosing.  You should use some of the methods you have learned in the A & Ps and with this assignment to complete this task to receive full credit. Be creative in ways you might show awards information. 

BONUS POINTS Option: For Bonus Points, instead of using a button, add a sub-form to this form that displays the Stars who were directed by that Director. Your output could look like this:

Don’t worry about auto refresh here. I have not been able to get this to work in Access. Instead, you can create a button that does the Refresh, but the user must press that. You will need to add the term “Refresh” to a VBA script:
Ask me if you are not sure.

 

Be sure all of your buttons work from the Main Menu that link to the three sub-menus that you created. Add buttons on all your sub-menus that link back to the Main Menu. Your Main Menu should open when Access starts. (Reminder: create an autoexec macro)

Submit the completed ACCESS database on Canvas with this Assignment.