Access 2000

Microsoft-Access Tutorial
Soren Lauesen
E-mail: slauesen@itu.dk
Version 2.4b: July 2011

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 Createrelationships ....................................... 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 thecontrols............................. 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.2Subforms........................................................ 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 VisualBasic 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...