Create form to search for the title of a video- Database Management System
Part 1 (Videos Form)
2116_figure 1.jpg
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. 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.
Part 2: Create the Customer Rental Form shown below. Example 1
1927_figure 2.jpg
Example 2
Not all the subform fields are showing and they are in a different order than the one above.
2150_figure 3.jpg
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 inthe 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.
1649_figure 4.jpg
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.
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 acalculated 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.
1817_figure 5.jpg
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.
1542_figure 6.jpg
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:
26_figure 7.jpg
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 fromthe wizard. See image below:
2118_figure 8.jpg
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.
121_figure 9.jpg
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
Above assignment with description how to do and my database access
