IS201 – 3 /10/2014

1. IS201 – 3/10/2014 What to do before class starts: Download the sample database from the k: drive to the u: drive or to your flash drive. The database is named “FormBelmont.accdb” It is located in the directory called: k:IS201\IS201-Hilfer\LabClass

2. MS Access Forms: Lab Outline • Purpose of using forms • Data entry vs. data view • Structure of data that underlies forms • Methods of creating forms • Structure of forms • Issues in developing forms • Forms can be complex and time-consuming to create because there are many options available to the computer systems professional.

3. Examine the Belmont database • Go to database tools and select relationships to see the structure of the database. • Not a great database design. There is significant data redundancy in the Invoice table. • This database includes some of the work that you will complete by the end of tutorial #6. • Open and examine some tables. • Open the tblCustomer. Examine the contents of the table in datasheet view. Is this easy to read for a casual user of computers? • Open the tblInvoice. Add a new invoice to the table. Is it easy to add a new invoice for a casual user of computers? • Forms are created by computer system professionals for use by people who are not computer system professionals. • If forms don’t make data easier to enter, then they aren’t useful. • If forms don’t provide more information than can be obtained from a datasheet view, then they aren’t useful.

4. Using forms to enter data • Close the open tables. • Open the form called “frmCustomerDataEntry” • Try out the Select Customer combo box, the add customer button, the delete customer button, and the next record navigation button. • The goal of these navigation methods (buttons and combo boxes) is to provide an “easy to use” method of data entry. • Let’s build the same thing. • Click on tblCustomer in navigation pane – don’t double click. • Select Form Wizard. • Select tblcustomer. All available fields. • Columnar layout. • Call the form “frmCustomerStuff”. • This is the form automatically generated from MS Access. • Navigate for different records on the form. • Add a new record. • Delete a record.

5. Modifying a form built by Access • Understand the form modes. • Form view vs. layout view vs. design view. • Understand the property sheet. • Available in layout and design views. • Explains the overall properties of the form as well as detailed information about each object on a form. • Understand the structure of a form. • Go to design view. • All forms are composed of: Form header, detail, and form footer. • Some things are very easy to change on a form. • Go to layout view to modify the labels and title. • Click in the Form header and change the title of the form to say: Update Customer Data. • Highlight all the labels. Make them right justified instead of left justified. Scoot them all over a little to the left.

6. Continue modifying easy stuff • Always save your form after doing modifications that you like. • Move between design, layout, and form view to modify and test your form. • Go to design view. • Extend the form footer line down to give you more room in the detail part of the form. • Highlight all boxes. • Move all the existing parts of the form down. • Make the form a little wider on the right side of the form. • Change the color of the labels. Make it whatever color you want to use for the buttons. I used very dark blue on the sample form, but you can select whatever color you want. • Make the labels bold. • Add a line at the top of the data entry fields. This is a little harder because you have to go to the controls, select a line control, place it where you want to go, and drag it across the screen. If you press the <shift> key while doing this, the line will be straight.

7. Doing harder modifications • Save your form (if you haven’t done it recently). • Go to design view. • Let’s add a combo box to navigate to a customer. • Make sure that the “Use Control Wizards” option is highlighted in the controls area. Need to click on the down arrow in the controls area to see if it is highlighted. • Check out all the controls you can put on a form. • Click on the combo box control. • Put it in the Form Header area of the form. Click on it. The Combo Box Wizard should appear. If it doesn’t, then either you clicked too much, or you don’t have the “Use Control Wizards” option highlighted. Try it again. • You should see three options. • I want the combo box to get the values from another table or query. • I will type in the values that I want. • Find a record on my form based on the value I selected in my combo box. • Select the last one “find a record…” and press <next>. • This should remind you a bit of the lookup wizard. They are very similar. Go to the next slide or listen and watch in class.

8. Continue putting in the combo box • Combo box wizard continued… • Select the customerID, FirstName, LastName, and Company in that order. Press <next>. • Unclick the “hide key column”. Microsoft recommends that you hide it, but I don’t. • Size the fields appropriately. This is what the user will see when they use the combo box. Press <next>. • Type in a label you think would help a user understand what to do. I think that “Select Customer” is helpful. Press <finish>. • Test it. Never invest time in making anything pretty until you test it! • Test your combo box by going into Form view. • If it works, save it. • Now make it pretty. • Drag around the box to get it where you want it to be. You can move a little bit at a time with the arrow keys. • Change the colors, make it bold, whatever. Make it look good.

9. Create a record operation button • Save. Always save before doing something new. • Go to design view. • Find the button control and click on it. • Decide where you want to put the button on your form. This is going to be the button to add customer records. • The Command Button Wizard should appear on the screen. • Select the category “Record Operations”. Select the action “Add New Record”. Press <next>. • Select the radio button “Text”. Type in the text you want to sit on top of the button. Mine says “Add Customer”. • Type in a meaningful name for your button. Don’t put spaces in the name!!! Mine says “AddCustomer”. Press <finish>. • You have a button! Probably ugly, but a button! Go to form view and test it. If it works, save the form. If it doesn’t, ask for help. • Go back to design view and do the same thing, but add a button to delete a record. Go to form view and test it. Save the form.

10. Make the buttons pretty • Go to design view. • Select both buttons. • Change the background color of the buttons. • Select the Format tab. Change the shape fill. Mine is dark blue. • Change the shape outline. Mine is very dark blue. • Change the shape effects. I like shadows and bevels for buttons, but you can do whatever you want. • You might notice that you can’t read the words on the buttons any more. Time to fix that. • Click on the home tab. Change the font color to white. Make the font size a little smaller, maybe 10 point. Make the font bold. • Make the buttons fit a bit better on the form. Change the box size, move them around, align them, it is up to you. • Test the buttons in the Form View. • Save your form!!!

11. Create two more buttons – this time for navigation • Go to design view. • Find the button control and click on it. • Decide where you want to put the button on your form. This is going to be the button to go to the next customer. • The Command Button Wizard should appear on the screen. • Select the category “Record Navigation”. Select the action “Go to Next Record”. Press <next>. • Select the radio button “Text”. Type in the text you want to sit on top of the button. Mine says “Next Customer”. • Type in a meaningful name for your button. Don’t put spaces in the name!!! Mine says “NextRecord”. Press <finish>. • Make another button that will go to the previous record. Call it the “Previous Customer” button. • Test the buttons and save your form. • Go back into design view and make the buttons pretty. • Test and save your form!

12. Understanding a more complex form • Go to navigation pane and select the frmContractsAndInvoices. • This form is created in tutorial #6. • Major concepts to note: • Main form vs. subform. • Main form vs. subform structure follows the database. Main form is the parent table, and subform is the child table. • Calculations in the main form. Where did they come from? Why are they in the subform? • Make sure to review the property sheet. The property sheet is always the key to understanding the contents of a form. • Can all the data be modified on the whole form? What data can’t be modified? • What is the purpose of the customerID combo box? • Should a form like this be used to enter and update data? • Close the form.

13. Modifying the tutorial #6 form • Go to navigation pane and select the frmContractInvoiceClass. • This is a copy of the form frmContractsAndInvoices. • Delete the customerID label and the customerID combo box. • We are going to replace the combo box with the name of the customer. • Create a textbox control on the form where the customerID combo box used to be. • I want this box to contain the firstname and the lastname of the customer. • Type: =firstname + lastname in the combo box. • Test the form. What happens? • The firstname and lastname fields are not on this form, so they can’t be referenced within the form. We must first add the fields from the table before they can be used on the form. • Click on the Add Existing Fields button within the design tab. • Notice that the customer table is not visible. Click on Show all Tables. • Expand the tblCustomer at the bottom of the screen. • Double-click on firstname and lastname.

14. Creating the name of the customer • The fields are ugly. • This is OK because we are going to delete them from the form after we use them. • Test the form. • Yes, it is really ugly because the name fields are just there in the screen. Ignore them. Honest. • Look at the textbox where it used to have an error. It now contains the name. This means that the test worked. • Now we make it pretty. • Go back into design view. • Delete the firstname and lastname boxes you added. Delete the labels, too. • Test the form again. Notice that the customer name is still there. Save the form and go back into design view. • Modify the combo box so it says: =[FirstName] + “ “ + [LastName] • Modify the label so it says: Customer Name in 11 point font. Maybe make it dark blue or bold. • Test and save the form.

15. Using a form for information or input? • Do you want a user to be able to change everything on this form? • Modify the editability of a form. • Go back to design view. I hope you are getting comfortable with design view. • Click on the ContractNum data field. Not the label, the textbox that has the ContractNum data. • Click on the property sheet, if it isn’t already visible. • Click on the data tab. • Make the locked selection “yes” instead of no. • Do the same thing for the ContractType data field. • Go to form view. Try and enter data in those two fields.

16. Creating Grouped Reports • Create a report lists all Invoice Items and Amounts by Contract Type. • First create a summary query to base the report off of, the query should include the ContractType, sum of the ContractAmount, InvoiceItem and the Sum of Invoices • Save the query as qryContractInvoiceSummary • Create a report based off of the query and add grouping based on ContractType.

17. Grouped Reports Cont. • Move the ContractType and SumOfContractAmt fields to the ContractType Header • Add a ContractType Footer • Sum the InvoiceAmt for each ContractType • Add a custom title for the new sum that will say “Total for: “ and the description of the ContractType. • To accomplish this use a TextBox control instead of a label and use string concatenation with the field name. • Name the report rptContractInvoiceSummary

18. Create a Crosstab Report • Create a report that shows the number of contracts of each type signed on each day of the week (Monday, Tuesday, etc.) • First create a new query to find the days of the week for each signing date. • Use the Weekday function to find the number that corresponds to the day of the week of the signing date: • Weekday([SigningDate]) • Include the ContractType and a count of the Contracts in the query • Save the query as qryContractDayOfWeek

19. Create Crosstab query • Create a crosstab query based on qryContractDayOfWeek that will sum the number of events by the day of the week. • Use the Query Wizard and choose Crosstab Query Wizard • The ContractType should be the Row Headings • The Day of the Week should be the Column Headings • The Calculation should Sum the Count of the Contracts. • Use the WeekdayName function to turn the number for the day of the week into the name (1 = Sunday, 2 = Monday, etc.) • Use the “Column Headings” field in the property sheet to change the order of the headings for the days of the week.

20. Use the remainder of class to: • Review for midterm exam • Take quiz 2 on WebCampus • MUST BE COMPLETED BY MIDNIGHT AND YOU WILL NEED THE PASSWORD PROVIDED IN CLASS! • Work on Access Project Part 3