All Powder Board and Ski Microsoft Access Workbook
All Powder Board and Ski Microsoft Access Workbook Chapter 6: Forms, Reports, and Applications Jerry Post Copyright © 2003 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
Main Form Wizard Select table Select/transfer fields 4
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 5
Data Source Properties Record Source = Customer table Form properties Control Source = Customer. ID column Control properties 6
Adding Command Buttons 7
Initial Grid Form 8
Grid Form Wizard Tabular style has more design options Datasheet style is simpler than Tabular 9
Add a Combo Box Add a combo box Delete the text box 10
Combo Box Role Display Product. Category Board Boots Clothes Electronic Glasses Ski. Board. Style Desc Category Downhill Ski Select 11
Combo Box Runtime Click the arrow Select the item Chosen value is transferred to form 12
Main/Subform by Sale 13
Initial Sale Form Fix layout and sizing Need combo box Multiply price by quantity Calculate subtotal 14
Initial Sale Form Improvements 15
Most of the Sale Form Display the subtotal 16
Expression Builder to Copy Subtotal Formula to copy the subtotal value Open the Sale form Select the subform Double click the subtotal control 17
Final Sale Form 18
Create the Customer Sales Report Select all columns from the Customer, Sale, and Sale. Item tables Customer break Sale. Item detail 19
Report Summary Options Select this button to calculate subtotals easily 20
Summary Options 21
Initial Report Customer break Sale. Item detail 22
Report Design Customer break Sale. Item detail Value textbox properties 23
Final Design Page break Correct total 24
Final Report (One Page) 25
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; 26
Sales Total Design 27
Subreport: Sales and Rentals Subreport properties Link main customer report (master) to subreport (child) 28
Subreport Display 29
Creating a Switchboard Form Start in design view Add command buttons Open forms or reports 30
Creating Toolbars and Menus Create a new toolbar 31
Toolbars and Menus Drag commands onto the toolbar 32
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 33
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> 34
Topics Map File #define All. Powder 100 #define Customers 10000 #define Sales 20000 35
Help Compiler and File 36
Help File on Access Form File name Topic number 37
- Slides: 37