Day 20: Access Chapter 4

Day 20:Access Chapter 4 RAHUL KAVIRahul.Kavi@mail.wvu.edu October 22, 2013

Last class • Calculated fields • In queries • In tables • Functions • DateDiff, Now • Aggregate(Summary) queries • Lookup fields

Today’s class • IFF function (calculated fields in Queries) • Forms • Creating Forms • Form Wizard • Blank Forms, Split Forms, Multiple item Forms, etc. • Designing and editing Forms.

Iff function • Syntax of IFF function: Iff(<variable1> condition <variable2>, true_part, else_part) • Example 1:= IIF([ShippingDate<Date(), “Shipped”, “Not Shipped”) • Example 2:=IIF([ShippingDate]<Date(), “Shipped”, IFF([ShippingDate]=Date(), “Shipping today”, “Unshipped”))

Forms • A form is a database object used to add or edit data in a table • There are three reasons for using forms to add or edit data: • You are less likely to edit the wrong record by mistake • You can create a form that shows data from more than one table simultaneously • You can create Access forms to match paper forms

Forms • Too much data in one form will confuse users. • Use one form for each task.

Creating Basic forms • Create->Forms • >Form • >Form Design • >Blank Form • >Form Wizard

Create->Forms->Form • Creates a form for the currently selected table • It will also include a subform for any table that has a foreign key that is related to the main table

Create->Forms->Form Design • Creates a new, blank form in design view • Design view allows you to add labels and fields and place them how you would like

Create->Forms->Blank Form • Creates a new, blank form in layout view • Layout view allows you to add labels and fields, but it automatically places them based on the style • Form Layout Tools->Arrange->Table • Stacked (default) • Tabular

Create->Forms->Form Wizard • Create a form using any combination of fields from any number of tables • Allows you to select how to group things for subforms • Example: Songs grouped by Album

Split forms • Split forms combine a form with a datasheet view of the table • Create->Forms->More Forms->Split Form • Creates a split form for the selected table • You can navigate to different records with the normal navigation buttons, or selecting the record from the datasheet view

Multiple Items forms • Multiple items forms are similar to datasheet view, but allow you to customize the fields and layout • Create->Forms->More Forms->Multiple Items

Datasheet form • Datasheet forms are even more like datasheet views than multiple item forms • While you can still edit the fields and their order, the layout is exactly like datasheet views on tables • Like all forms, allows you to include fields from multiple tables

Editing forms • Add fields • Delete fields • Layout Control • Remove layout • Add layout • Adjust width • Add a theme

Sorting Records • From form view, do one of the following: • Right click, select sorting order • Select field (be careful with yes/no fields as selecting them will change the value), then Home->Sort & Filter->Ascending or Descending

Form sections • Forms have 3 sections… header, footer, and detail • These sections can be seen and edited in Design View

Important Control types • Text box • Label • Button • Tab • Toggle button • List box • Check box • Option button • Subform

Next Class • Reports • Create, modify • Sorting • Report sections • Control Types

Day 20: Access Chapter 4