Details of this Paper

Microsoft Access Final




Actual instructions with screenshots attached to this question.;Final Project;Part 1 (Videos Form);Create this form to search for the title of a video. Create the necessary;Video table (with about 5 or 6 records in the interest of time) with the fields;shown. Use any images you can find. Make the form look professional and;not be the default design. You can use themes and colors for quick elegant;designs.;The rest of the final project is detailed below. You will need the Video table;you made for the form above, to complete the project as well as two;additional tables (a customer table and a rentals table). You only need to;enter a few records to demonstrate that it is working.;Part2;Create the Customer Rental Form shown below. Example 1;Example 2;Not all the subform fields are showing and they are in a different order than the one above.Here is a guide to the steps;Three tables are created, and related ? Customers, Rentals, Videos(similar;to the past exercises).;The Phone # is the ID# for the customers table, and the Video table of;course has its own ID#. In the example 1 above, the Video ID# is simple;whereas in Example 2, the Video ID# is more realistic like V-765 or SD-351.;(The images are screen shots from different students.);When creating the form/subform above, by using Create, More;Forms?, Form Wizard, be sure to select the customers table first and send;ALL the fields over to the right, then select the Rentals table and send all;the fields over EXCEPT the customer ID field, then select the videos table;and send over only the fields you need and be sure NOT to send the Video ID;(you don?t want the user typing into this field by mistake! Remember there;is another Video ID field in the rentals table that was already sent to the;form wizard);Once the form/subform is created, you can customize it, and there is quite;a bit of this to do. Some of the features, you haven?t done before.;In design view, first add the search control for the customer. Be sure to;show the Phone # along with the name as in the graphic for Example 2;above. Then create the combo box for the Video ID. Start by deleting the;Video ID field in the subform, and replace it with the Combo Box form control. When prompted, select to display the Video ID as well as the Video;Title as shown below. It is crucial, when prompted, to select the choice that;stores the data (Video ID), in the Video ID field of the rentals table.;Also add the calculated fields for # of Days and total. You can add these;fields anywhere in the subform you like. As you must have noticed by now;the layout of the subform in design view, has no bearing whatsoever on the;actual layout when you view the form. This is because the default view for;the subform is set to datasheet view and a grid style or table, is forced on;the subform. But sometimes we want the subform to appear the way it is;arranged in the design view.;Now for the fun part! You are now going to make the subform appear as it;does in the design view.;Select the subform by clicking the square in the top left corner where the;ruler guides meet. Show the property sheet. It appears on the right side of;the screen.;Select the ALL tab on the property sheet, and locate the Default View, and;change it to Continuous Form. If you view the form now, it will reflect the;actual layout in design view. View the form so you know what we are talking;about.;The reason we are changing the view is so you can add a column total to the;subform. The rules for column totals (subtotal for videos rented for this;customer) in a form are very strict. They must be in the form footer to work;correctly and there is no form footer in datasheet view! This was the same;situation we encountered in reports.;The next rule for subtotals is, you cannot specify the name of a calculated;control in the SUM function. Only real field names are allowed. So if you;have a column like we had previously for a line total such as Price * Qty, and;you name it Total, you cannot specify =SUM([Total]) as Total is a calculated;field, not a real field from a table! However you can specify =SUM([Price] *;[Qty]) so it?s not so bad. Keep in mind that Form footers only show in form;or continuous form, view. Not in datasheet view!;The last part to the design is to move the fields around so they look like;mine (in the graphic below) in a straight row without the text labels next to;them. We are doing this because we like the display of the datasheet view;which shows rows of related records all at once. We like seeing all the orders a customer placed, but we also need a footer! So we will be using;continuous form view, and re-arranging the fields to look like datasheet;view!;Some of you are using an older version of Access (2003) and so you will have;to manually delete each field label (select the top right corner of the field;label before you press delete, so that you do not delete the whole object -;field and label), then you have to drag the fields around to appear in a;straight line, then remake the labels in the header section using the Label;tool.;In Access 2007 and later, there is a tool on the Arrange tab called Tabular.;If you simply select all the fields (click the top right corner where there is a;cross icon to select all the fields, or use shift+click to do a multiple;selection). Then click the tabular tool now, all the fields selected will;appear in a straight line and their corresponding labels will be placed in the;header section! Very cool! See the graphic below for the tabular tool and;the select all fields icon.Now all you have to do is size the fields and drag them over to the left;margin so they fit better on the screen.;Now add your Total to the footer by using the =SUM() function. You can;peek at mine in the graphic.;Use the property sheet to set the currency formats. Make sure the Date;Picker (calendar control) is working. Also be sure to spend time;considerable time I?m sure, making the form look very similar to mine in;that it is easy to read and use. Everything is positioned, sized correctly and;neatly on the screen.While you?re on the property sheet, let?s make sure you have experience;with this feature. You should prevent accidental typing into the video title;and price fields. This could change the data in your inventory table. So;protect these fields. Lock them. Disable them. See if you find the property;to do this. Those words are hints. You may want to experiment with the two;properties that can do the job for you, and choose the one you like the best.;It is easier to find them on the Data tab of the property sheet.;One last fancy feature. Use conditional formatting to set a color if the Days;are greater than 4. So be sure to have some records with days that are 5 or;more. Below is a graphic to start you out on using the conditional formatting;which you will find on the tool ribbon in design view:Once you click the conditional formatting tool on the Format tab, the dialog;box shown will popup. Click New Rule and the rest is self-explanatory.;Explore.;Part 3;Now for some Macros as promised;1. On the Videos Form from Part 1, add a command button (the button;tool with the XXXX on it) to open the Customer Rental Form created in;Part 2, for ordering/renting a video. The idea is that the store clerk;could browse or search videos for information and then click this new;button to jump directly to the Customer Rental Form to place a rental;order. This type of macro using the button tool is very simple and;completed by just making selections from the wizard. See image;below:Choose your macro task from the categories. Then choose the specific task;form the pane on the right. Click Next to answer questions about which form;to open if that?s what you?re doing, then choose a picture or text for the;button.;2. On the Customer Rental Form used for ordering/renting, add a macro;that will pop-up a message box reminding the clerk to check for ID if;the Video attempting to be ordered/rented, has a rating of ?R?. This;one is similar to the one you did for adding 10% to the Discount field;when the Last Name equaled a specified name. You will use the same;IF statement, but this time you will use the messagebox action instead;of the setvalue action. The user only chooses the video for the popup;message to appear. They do not type into the rating field!;Part 4;Create a Main Form with a menu of buttons to;1. Search Videos - i.e. open you beautiful Videos form;2. Place an Order - i.e. open your Customer Rental Form;3. Open a report organized (grouped) by Videos with the customers;who ordered it under the video name;4. Quit Access;All these macros are simple button macros that are created when you use;the button tool in the form with the wizard turned on. Just look through all;the choices for the macros to open forms, open reports and quit Access.;The form should look like this, but hopefully prettier.To make this menu form of buttons show up automatically when Access;opens your database;1. Click the Microsoft Office Button (2007), File for 2010 version, and;then click Access Options.;2. Click Current Database, and then in the Display Form list, select the;form that you want to display when the database starts (the form;shown above.);3. Click OK, and then close and reopen the database and the form above;should automatically display. Cool, right?;Here is one from a student who took this to the next level.That's it! Good luck and have some fun with this. You have two full weeks to;complete and submit.


Paper#63606 | Written in 18-Jul-2015

Price : $27