I have the previous database that this assignment asks to be continued off of. I will send after handshake. This week you will use the database from last time in which you created the relationships. The relationships are necessary in order to create a form/subform. All the hard work was already done last week when you related the tables. The form wizard will do the rest for you. The above image is what your relationship window would look like. The point of the referential integrity, is so the user cannot enter an item into the orders table unless it appears in the inventory table and a customer ID cannot be entered into the orders table unless it exists in the customer table. Also, when a customer record is deleted, it cascades meaning, the related records in the orders table will be deleted as well. To really see the power of what you did, you need to make a special kind of form known as a Form/Subform. The Access wizard will build this for you very quickly. Start by selecting the Create on the Ribbon and then choosing Form Wizard. Earlier version users just select More Forms, then choose Form wizard (or something close to that). You will be presented with the screen below: If the Customer table is not already selected, select it. The rule is to choose the table you want to appear at the top of the form. If you are not sure what this means, scroll down to the last graphic of the completed form. Use the arrows to send all the fields to the right side and be sure not to click Next as yet. Now select the orders table or the middle table and only send over what is not already there, so skip the ID#. This is a good rule to skip what is already there. Howevr the real reason to skip the ID# is that you dont want to have to mess with the ID# when you place the order, it will already be entered for you. Dont click Next as yet. We have one more table we need for the form. Now select the last table, inventory and send over only what you need which is the price. Again, you already have the Item field but the real reason is that you dont want the user to have access to this Item field or they will change the items you sell. The item field you want, is the Item field from the Orders table and you already sent that over. This is the field for the item the cutomer wants to order. Now you can clik Next. The image above shows the layout you are about to create. Notice the customer table fields are grouped at the top and the rest are grouped in the subform at the bottom. Once you see this layout you can click Finish unless you want to choose style and colors and such. Click Finish. You can quickly fix the layout of the subform by selecting the item column and dragging it to the first position. Now enter an item in your inventory and see the price pop-up. In a later Access class you will learn how to make a drop-down box for you items as well as lock the price field etc. Also you would want to add a price * qty field and so on. Enter several items for several customers and then look at each table. Pretty cool, no? Before submitting, arrange the layout so the data shows is a neat and professional manner. You will need to explore this a bit in the desing view of the form. Experiment with dragging the fields around and the sizing the columns. Also notice that the subform exists as a separate form object on its own. You can open this part in design view separate from the main form, for easier control of the fields. Submit it for grading. Change the name of the database to your name before uploading and submitting. Submit your work by using the Assignment Drop-Box tool.
