{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

AccessTutorial - Microsoft-Access Tutorial Soren Lauesen...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Microsoft-Access Tutorial Soren Lauesen E-mail: [email protected] Version 2.4a: August 2007 Contents 1. The hotel system ................................................... 4 2. Creating a database ............................................. 6 2.1 Create a database in Access ............................. 6 2.2 Create more tables ......................................... 10 2.3 Create relationships ....................................... 12 2.4 Look-up fields, enumeration type .................. 14 2.5 Dealing with trees and networks .................... 16 3. Access-based user interfaces ............................. 18 3.1 Forms and simple controls ............................. 18 3.1.1 Text box, label and command button ...... 18 3.1.2 Adjusting the controls ............................. 20 3.1.3 Cleaning up the form .............................. 20 3.1.4 Shortcut keys for the user ....................... 22 3.1.5 Lines, checkbox, calendar ....................... 22 3.1.6 Combo box - enumeration type .............. 24 3.1.7 Combo box - table look up ..................... 26 3.1.8 Control properties - text box ................... 28 3.2 Subforms ........................................................ 30 3.2.1 Subform in Datasheet view ..................... 31 3.2.2 Adjust the subform ................................. 34 3.2.3 Mockup subform ..................................... 36 3.2.4 Subform in Form view ............................ 36 3.2.5 Summary of subforms ............................. 38 3.2.6 Prefixes ................................................... 38 3.3 Bound, unbound and computed controls ........ 40 3.3.1 Showing subform fields in the main form42 3.3.2 Variable colors - conditional formatting. 42 3.4 Tab controls and option groups ...................... 44 3.5 Menus ............................................................ 46 3.5.1 Create a new menu bar ............................ 46 3.5.2 Add commands to the menu list ............. 48 3.5.3 Attach the toolbar to a form .................... 48 3.5.4 Startup settings - hiding developer stuff . 48 3.6 Control tips, messages, mockup prints .......... 50 4. Queries - computed tables ................................. 52 4.1 Query: join two tables .................................... 52 4.2 SQL and how it works ................................... 54 4.3 Outer join ....................................................... 56 4.4 Aggregate query - Group By .......................... 58 4.5 Query a query, handling null values .............. 62 4.6 Query with user criteria ................................. 64 4.7 Bound main form and subform ...................... 66 4.7.1 Editing a GROUP BY query ................... 67 5. Access through Visual Basic ............................. 68 5.1 The objects in Access .................................... 68 5.2 Event procedures (for text box) ..................... 72 5.2.1 More text box properties ......................... 72 5.2.2 Computed SQL and live search ............... 74 5.2.3 Composite search criteria ........................ 76 5.2.4 Event sequence for text box .................... 78 5.3 Visual Basic tools ........................................... 80 5.4 Command buttons .......................................... 84 5.5 Forms ............................................................. 86 5.5.1 Open, close, and events ........................... 86 5.5.2 CRUD control in Forms .......................... 87 5.5.3 The OpenForm parameters ...................... 89 5.5.4 Multi-purpose forms (hotel system) ........ 90 5.5.5 Dialog boxes (modal dialog) ................... 92 5.5.6 Controlling record selection .................... 93 5.5.7 Column order, column hidden, etc .......... 94 5.5.8 Area selection, SelTop, etc ...................... 94 5.5.9 Key preview ............................................ 97 5.5.10 Error preview ........................................ 97 5.5.11 Timer and loop breaking ....................... 98 5.5.12 Multiple form instances ......................... 99 5.5.13 Resize .................................................. 100 5.6 Record sets (DAO) ....................................... 102 5.6.1 Programmed record updates .................. 102 5.6.2 How the record set works ...................... 104 5.6.3 The bound record set in a Form ............ 106 5.6.4 Record set properties, survey ................ 108 5.7 Modules and menu functions ....................... 110 5.7.1 Create a menu function ......................... 110 5.7.2 Define the menu item ............................ 112 5.7.3 Managing modules and class modules..112 5.7.4 Global variables .................................... 114 6. Visual Basic reference ...................................... 116 6.1 Statements .................................................... 116 6.2 Declarations ................................................. 120 6.3 Constants and addresses ............................... 122 6.4 Operators and conversion functions ............. 124 6.5 Other functions ............................................. 128 6.6 Display formats and regional settings .......... 132 7. Access and SQL ................................................ 134 7.1 Action queries - CRUD with SQL ............... 134 7.1.1 Temporary table for editing .................. 134 7.2 UNION query ............................................... 136 7.3 Subqueries (EXISTS, IN, ANY, ALL . . .) ..138 7.4 Multiple join and matrix presentation .......... 140 7.5 Dynamic matrix presentation ....................... 142 7.6 Crosstab and matrix presentation ................. 144 8. References ......................................................... 148 Index ...................................................................... 149
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon