All Powder Board and Ski Microsoft Access Workbook
All Powder Board and Ski Microsoft Access Workbook Chapter 6: Forms and Reports Jerry Post Copyright © 2007 1
Form Types Ski Board Style Description Customer Category Grid Last Name First Name Phone Address City Sale Customer Main Salesperson Item. ID Description Price Quantity Value Main and Subform 2
Customer Main Form Text box Label Combo box Record navigation 3
Action Start the form wizard Select the Customer table Transfer all columns to the right window Use the Columnar layout Select a style Enter a name for the form 4
Main Form Wizard Select table Select/transfer fields 5
Form Design View Switch to design or display view Field List Box Toolbox to add controls Right click to open form properties Right click to open control properties Properties to control objects 6
Data Source Properties Record Source = Customer table Form properties Control Source = Customer. ID column Control properties 7
Action Switch Customer form to design view Add a label as a form title Add a command button Select Record Operation/Delete Record Add a command button to insert records Run the form and test the buttons View/Tab Order and verify sequence Save the form 8
Adding Command Buttons 9
Initial Grid Form 10
Action Create a new form with the wizard Table: Ski. Board. Style Columns: All Select the Datasheet layout Test the form 11
Grid Form Wizard Tabular style has more design options Datasheet style is simpler than Tabular 12
Action Switch to form Design view Delete the box for Category Add a combo box Select the first lookup option Choose the Product. Category table Choose the Category and Category. Description columns Set column widths Select the Category column Store value into the Category column Rename the combo box Set the tab order Run the form and test the combo box 13
Add a Combo Box Add a combo box Delete the text box 14
Combo Box Role Display Product. Category Board Boots Clothes Electronic Glasses Ski. Board. Style Desc Category Downhill Ski Select 15
Combo Box Runtime Click the arrow Select the item Chosen value is transferred to form 16
Action Create a new form with the wizard Sale table, all columns Customer table use name, phone, and e-mail columns Employee table, use the name Sale. Item table, all columns except Sale. ID Item. Model table, use Category, Model. ID, and List. Price Finish the wizard and test the form 17
Main/Subform by Sale 18
Initial Sale Form Fix layout and sizing Need combo box Multiply price by quantity Calculate subtotal 19
Action Switch to Design view Replace Customer. ID, Employee. ID, and Payment. Method with combo boxes Use the Row Source property of the combo boxes to set a sort order Set tab order and run the form 20
Initial Sale Form Improvements 21
Most of the Sale Form Display the subtotal 22
Action Open the sub form in Design view Insert a combo box for the SKU Add a text box to compute Value=Quantity. Sold*Sale. Price Add a text box to the footer to compute the subtotal: =Sum(Quantity. Sold*Sale. Price) Set tab order Test the sub form calculations Open the main form in Design view Add subtotal text box and use the expression builder to copy the subtotal value from the subfrom subtotal Test the form 23
Expression Builder to Copy Subtotal Formula to copy the subtotal value Open the Sale form Select the subform Double click the subtotal control 24
Final Sale Form 25
Action Create a new report with the wizard Customer table, use all columns Sale. Item table, use all columns Verify the group breaks Click the Summary Options button Compute totals for the Quantity Finish the wizard and switch to design view 26
Customizing Form Properties Form properties Text box 27
Action Make a copy of the Customer form. In Design View, add a header. Drag the Customer title onto the header. Set the background color of the header to yellow. 28
Create the Customer Sales Report Select all columns from the Customer, Sale, and Sale. Item tables Customer break Sale. Item detail 29
Action Create a new report with the wizard 30
Report Summary Options Select this button to calculate subtotals easily 31
Summary Options 32
Initial Report Customer break Sale. Item detail 33
Report Design Customer break Sale. Item detail Value textbox properties 34
Action Delete some unnecessary columns Move items around to improve the look Add a text box in the Detail section called Value to compute [Quantity. Sold]*[Sale. Price] Set currency format Edit the Sum calculations to be Sum([Quantity. Sold]*[Sale. Price] Run the form and clean up the layout 35
Final Design Page break Correct total 36
Final Report (One Page) 37
Action Create a new query to total sales by customer Save the query Create a new report based on the query For the Customer group, set the Keep Together property to Whole Group Run the report 38
Customer Totals Without Items SELECT Customer. ID, Customer. Last. Name, Customer. First. Name, Customer. Phone, Customer. EMail, Sale. ID, Sale. Date, Sale. Ship. City, Sale. Ship. State, Sum([Sale. Price]*[Quantity. Sold]) AS [Value] FROM (Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID GROUP BY Customer. ID, Customer. Last. Name, Customer. First. Name, Customer. Phone, Customer. EMail, Sale. ID, Sale. Date, Sale. Ship. City, Sale. Ship. State ORDER BY Customer. Last. Name, Customer. First. Name; 39
Sales Total Design 40
Action Create a simple report based on the Customer table Create a Sale sub-report from the Sale and Sale. Item tables, grouping it by Sale. Item. Create a Rental sub-report from the Rental and Rental. Item tables Open the Customer report in design view Drag and drop the two sub-reports onto the Detail section of the Customer report Use properties of the sub-reports to set the Link Child and Link Master Fields to Customer. ID Run the report 41
Subreport: Sales and Rentals Subreport properties Link main customer report (master) to subreport (child) 42
Subreport Display 43
Action Create a blank form in Design view Set the properties to remove the scroll bars and navigation buttons Add command buttons to open other forms (Form Operations/Open Form) and reports Use colors and graphics to make the form appealing Run the form and test all buttons 44
Creating a Switchboard Form Start in design view Add command buttons Open forms or reports 45
Action Choose View/Toolbars/Customize On the Toolbars tab create a new toolbar Click the Commands tab Drag the main form icon onto the toolbar Add a Customers menu option and drag the customer forms and reports onto it Add the Help icon Test the toolbar 46
Creating Toolbars and Menus Create a new toolbar 47
Toolbars and Menus Drag commands onto the toolbar 48
Action Create at least three HTML help files for the All Powder forms using an HTML editor or Wordpad If necessary, download and install the HTML Help workshop Create a new project in the workshop Add the HTML files Edit the HTML files to add keywords Create the mapping file with a text editor and add it to the project Set project options to build the TOC and index files Compile and test the help file Edit the database forms and add the help file name and Content. ID Run the form and press the F 1 key to test the help files 49
HTML Help File compile All. Powder. chm Form 1 Topic A 2 Topic B 3 Topic C 4 Topic D 5 Topic E 6 Topic F HTML Topic HTML Topic Properties: Help File: All. Powder. chm Help Context ID: 1 50
HTML Help Files <Object type="application/x-oleobject" classid="clsid: 1 e 2 a 7 bd 0 -dab 9 -11 d 0 -b 93 a-00 c 04 fc 99 f 9 e"> <PARAM name="Keyword" value="Contents"> <PARAM name="Keyword" value="Introduction"> <PARAM name="Keyword" value="Start"> <PARAM name="Keyword" value="Management"> </OBJECT><HTML><HEAD> <TITLE>All Powder Board and Ski Shop</TITLE> <LINK rel="stylesheet" type="text/css" href="Styles. css"> </HEAD><BODY> <H 1>Introduction to the All Powder Board and Ski Shop</H 1> <TABLE><TR> <TD><IMG SRC='Board. Logo 1. gif' border='0'></TD> <TD>All Powder Board and Ski Shop sells and rents snowboards and skis for all levels of riders and skiers. </TD> </TR></TABLE> <H 2>The Board and Ski Shop</H 2> <UL><LI><A HREF=’Customers. html'>Customers</A></LI> <LI><A HREF=’Sales. html'>Sales </A></LI> </UL></BODY></HTML> 51
Topics Map File #define All. Powder 100 #define Customers 10000 #define Sales 20000 52
Help Compiler and File 53
Help File on Access Form File name Topic number 54
- Slides: 54