AccessTutorial

EXISTS (SQL), 138 - form view versus datasheet - in subform, 66 view, 38F Hfield (in table). See also data type - Forms object, 69 HAVING (aggregate query), 54,- combo box, 14 - GotFocus (event), 86- enumeration type, 10 - grid, 18 60field list (for adding controls), 34 - KeyPreview, 97 Height (size property), 28, 100file formats (Access versions), 8 - Load (event), 86 help (for field types), 7filter properties (form), 87 - main form, 30 help (in Visual Basic code), 82,financial functions (VBA), 130 - menu connection, 48FindFirst, FindNext . . . - MinMax buttons, 21 83 - mockup, 36 help (pop-up), 50 (recordset), 108 - moving and sizing (VBA), 100 Hex function (VBA), 130FIRST/LAST (query), 60 - multiple instances, 99 hotel system, 4focus - navigation buttons, 21 hotel system (room grid), 4, 94,- dialog box, 89 - New (create open form), 99- GotFocus (form event), 86 - open, 86 140- SetFocus, 86 - Open (event), 86font (datasheet), 34, 42 - OpenArgs, 89 IFor Next statements (VBA), 118 - OpenForm parameters, 89 If Then (VBA), 116foreign key, 11, 12 - record selection (current IIf function (VBA), 128- combo box, 26 immediate window (debug), 50,form, 86–101 record), 93- Activate (event), 86 - record selector, 21 69- ActiveForm (current), 110 - Recordset, 106 IN (operator), 124- AfterUpdate (event), 86 - RecordSetClone, 106 IN (SQL), 138- AllowAdditions, 87 - Resize (event), 86, 100 initial values (VBA), 120- AllowDeletions, 87 - saving, 22 Input Mask (text box), 28- AllowEdits, 87 - Scroll Bars, 21 INSERT (record, SQL), 134- area selection (VBA), 94 - SelTop, SelHeight, etc. (VBA), integer field (in table), 11- autoformat, 21 IRR (internal-rate-of-return,- BeforeUpdate (event), 86 94- Border Style, 21 - size, 100 VBA), 130- bound, 32 - sorting datasheet rows, 34 Is operator (VBA), 124- bound main and subform, 66 - style (AutoFormat), 21 IsArray, IsDate . . . (VBA), 130- caption, 21 - subform, 30–39- close, 86 - Timer, 98 J- Close (event), 87 - Unload (event), 86 Jet engine (database), 68- column sequence, 34 Form reference (in subform), 71, JOIN (multiple joins), 140- column sequence (VBA), 94 JOIN (query), 52, 54, 56- column width, 34 122- column width (VBA), 94 format K- continuous form, 30 - columns, 136 key field (foreign key), 11, 12- Control Box, 21 - date in controls, 126 key field (primary key), 7- create single record (VBA), 88 - Format function (VBA), 126 keyboard handling- creation, 18 - input mask, 28, 132 - function keys, 97- CRUD (multi-purpose, VBA), - regional settings, 132 - shortcut keys on controls, 22 - text box property, 28 - shortcuts (built in), 8, 22 90 Forms object, 69 - tab order, 28- CRUD control, 87 front/back (on forms), 22 KeyCode (VBA), 97- Current (event), 78, 86 function (in menu), 112 KeyDown (text box event), 78- DataEntry property, 88 function (procedure), 110, 120 KeyPress (text box event), 78- datasheet view for subform, 31 function keys (F2, F3 . . .VBA), KeyPreview (form), 97- Deactivate (event), 87 KeyUp (text box event), 78- design view (shortcut keys), 22 97- Dialog box, 92 L- Dirty (event), 86 G label (Caption), 28- edit single record (VBA), 88 GetRows (recordset), 96, 108 label (delete/add), 20- error preview, 97 global variables (in modules), 114 label (for text box), 18- event sequence, 86 GotFocus (form event), 86 LastUpdated (recordset), 108- Filter, 87 GotFocus (text box event), 78 LBound (array bound, VBA), 128- form view for subform, 36 grid (for queries), 52 LCase function (VBA), 128 grid (on forms), 18 Left (position property), 28, 100 GROUP BY (aggregate query), Left function . . . (VBA), 128 Len function (VBA), 128 54, 58 Like (text comparison), 64, 124 - editing the data, 67, 134 151 Index

limit to list (in Combo Box), 24 name of control, 28 primary key, 7line (on form), 22 Name? (error in computed value), printing (mockup screens), 50list separator (regional settings), procedure (in menu), 112 41 procedure (public function), 110, 132 named query (SQL), 140, 142list with detail area, 42 national settings, 132 120live search, 74 navigation (current record), 93 procedure (shared), 76Load (form event), 86 navigation (shortcut keys), 8 program break, 98Locked (text box property), 28 navigation buttons (on form), 21 project explorer (VBA tool), 80logging (of events), 81 network structure (in database), propertieslogical operators (And, Or), 124 - AllowAdditions (form), 87long integer field (in table), 11 16 - AllowDeletions (form), 87lookup field New (create new object), 99 - AllowEdits (form), 87- combo box, 26 new-line character Chr(10), 92 - Border Style (form), 21- enumeration type, 14 NoMatch (recordset), 109 - Cancel (command button), 84- hiding the code (Column Nothing (VBA), 122 - Caption (label), 28 NPV (net-present-value, VBA), - Caption (on form), 21 Width), 34 - ColumnHidden (text box, etc.),lookup Wizard, 14, 24, 26 130loop (forced break), 98 Null 94Loop statements (VBA), 118 - blank fields at outer join, 56 - ColumnOrder (text box, etc.), - comparing with, 62, 77, 122M - in computations, 62, 124 94main form, 30 number field (in table), 10 - ColumnWidth (text box, etc.),math functions (VBA), 130 number format (regionalmatrix presentation (with SQL), 94 settings), 132 - command button, 84 140, 142 - Control Box (form), 21Me (as parameter, VBA), 121 O - Control Source (text box), 28Me (this object/form, VBA), 72, object browser (Visual Basic), 83 - ControlTip, 28 objects - DataEntry (form), 88 122 - addressing, 69 - Default (command button), 84memo field (in table), 10 - controls, 70 - Enabled (text box), 28menus (toolbars), 46–49 - database, 68 - Filter (form), 87- commands, 48 - Forms, 69 - Format (text box), 28- commands (VBA), 112 - in Access, 68–71 - KeyPreview (form), 97- connect to form, 48 - recordset, 68 - label, 28- creation, 46 - screen, 110 - Left, 28, 100- pop-up, 46 - through VBA, 69 - Locked (text box), 28- show all on list, 20 Oct function (VBA), 130 - MinMax buttons (form), 21- startup settings, 48 OldValue (text box), 72 - Name (programmer's), 28messages (MsgBox), 50, 92 On Error (VBA), 117 - OldValue (text box), 72MIN/MAX (query), 60 Open (form event), 86 - record set, 104, 108MinMax buttons (on form), 21 OpenArgs (form parameter), 89 - Scroll Bars (on form), 21mockup OpenForm (parameters), 89 - Scroll Bars (text box), 28- datasheet, 36 OpenRecordSet, 109 - SelHeight (on form), 94- screen prints, 50 operators (VBA), 124 - SelLeft (on form), 94Modal dialog (form), 92 Option Compare (VBA), 128 - SelTop (on form), 94module (class), 80, 110, 122 option group (radio buttons), 44 - SelWidth (on form), 94- creating and naming, 112 ORDER BY (query), 54, 62 - size and position, 28, 100- global variables, 114, 122 ordering - subform, 38, 70MouseDown, MouseUp (events), - controls (front/back), 22 - Tab Index, 28 - controls (tab order), 28 - Text (text box), 72 78 - datasheet rows, 34 - Text Align (text box), 28Move, MoveFirst . . . (recordset), - records in query, 62 - text box, 28, 72 OUTER JOIN (query), 56 - through VBA, 69, 70 108 - TimerInterval, 98moving and sizing P - Top, 28, 100- controls, 20 Parent reference (from subform), - Value (text box), 72- forms, 100 - Width, 28, 100MsgBox, 50, 92 97, 122 property list (Visual Basic help),multiple connectors (in database), partial referential integrity, 12 Partition (operator), 124 82 16 pop-up help, 50 public (function), 110multiple form instances, 99 pop-up menu, 46 prefixes (for controls, etc.), 38NName (recordset), 109 Index152

Q - bound to form, 106 - datasheet rows, 34queries, 52–67 - clone, 104, 106 - records in query, 55, 62- action, 134 - CRUD control, 102 Space function (VBA), 128- aggregate, 58 - DAO 3.6, 102 SQL. See also queries - properties, 104, 108 - computed, 74, 76, 102 - in subform, 66 rectangle (on form), 22 - how it works, 54- alias (AS), 58 reference card (VBA), 116 SQL engine (database), 55, 68- ALL, 138 referential integrity, 12 square brackets [name- all fields (*), 52 regional settings, 132- ANY, 138 relationships, 12 parenthesis], 40, 54, 91- average (AVG), 60 - referential integrity, 12 startup settings (menus etc.), 48- Cartesian product, 54 repair database, 15 statements (VBA), 116- computed fields, 58 Requery (recordset), 109 Static (declaration, VBA), 122- computed SQL, 74, 76, 102 Resize (form event), 86, 100 StDev (query), 60- correlated, 138 role stop program, 98- COUNT, 60 - developer, 6, 132 StrComp (VBA), 128- Crosstab, 144 - user, 6, 132 string functions (VBA), 128- CRUD, 134 room grid (hotel system), 4, 94, strings \", 122- DELETE, 134 strings (nested), 74- DISTINCT, 138 140 strings, multi-line, Chr(10), 92- dynaset, 52 row source (in Combo Box), 26 style (on form), 21 subform, 30–39 - group by, 61, 134 S - columnar, 31- editing the data, 53, 134 save - datasheet versus form view, 38- EXISTS, 138 - form, 22 - datasheet view, 31- FIRST/LAST, 60 - module (class), 110 - Form property, 71, 122- grid, 52 - record, 8 - form view, 36, 38- GROUP BY, 54, 58 - record (VBA), 88 - Parent property, 97, 122 - Visual Basic code, 81 - properties, 38, 70 - editing the data, 67, 134 Screen (VBA object), 110 - subform control, 32 - in subform, 66 Scroll Bars (on form), 21 subquery (SQL), 138- HAVING, 54, 60 Scroll Bars (on text box), 28 subroutine (procedure, VBA),- IN, 138 searching- INSERT, 134 - live search, 74 120- JOIN, 52, 54 - many criteria, 76 SUM (query), 60- JOIN (multiple), 140 - user criteria in grid, 64- live search, 74 security (when opening a T- MIN/MAX, 60 Tab Index (cursor movement), 28- named (stored), 140, 142 database), 8 tab sheet (control), 44- ORDER BY, 54, 62 SELECT (query), 54, 55 table- OUTER JOIN, 56 Select Case (VBA), 116 - as subform, 36- SELECT, 54, 55 self-reference (in computed - creation, 7- SQL, 54 - data entry, 8- SQL-engine, 55, 68 expressions), 40 - shadow copy, 16- StDev, 60 self-reference (in database), 16 Text (text box property), 72- subquery, 138 SelTop, SelHeight, SelWidth, text box, 18- SUM, 60 - AfterUpdate (event), 72, 78- UNION, 136 SelLeft (datasheet, VBA), 94 - BeforeUpdate (event), 78- UPDATE, 134 SendKeys, 98 - Change (event), 73, 78- user criteria in grid, 64 Set statement (VBA), 116 - Control Source, 28- Var, 60 SetFocus (VBA), 86 - ControlTip, 28- WHERE, 54 shadow table, 16 - Enabled, 28QueryDefs (VBA), 142 shared procedure, 76 - Enter (event), 78quick info (Visual Basic help), 82 Shift (KeyCode, VBA), 97 - event sequence, 78quotes (nested \"), 74 shortcut keys - events, 72 - data entry, 8 - Format, 28R - for change of view, 22 - GotFocus (event), 78radio buttons (option group), 44 - navigation, 8 - KeyDown (event), 78record - underlined letter on control, 22 - KeyPress (event), 78- saving, 8 shortcut menu (pop-up menu), 46 - KeyUp (event), 78- saving (VBA), 88 size unit (twips), 100 - Locked, 28record selector (on form), 21 sizing and moving - MouseDown, Click, etc., 78RecordCount (recordset), 109 - controls, 20 - OldValue, 72recordset, 68, 102–9 - forms, 100 sorting 153 Index

- properties, 28, 72 undo drawing, 20 - debug command, 81- Scroll Bars, 28 undo lookup-Wizard, 14 - help, 83- Text (property), 72 undo update (VBA), 79, 84 - object browser, 83- Text Align, 28 UNION (SQL), 136, 138 - objects in Access, 68–71- Value, 72 Unload (form event), 86 - pop-up help, 82text comparison (Like), 64, 124 unsafe expressions (when - project explorer, 80text field (in table), 10 - reference card, 116time/date field (in table), 11 opening a database), 8 - saving the code, 81time/date functions (VBA), 130 update (record), 8 - tools, 80–83Timer event, 98 UPDATE (record, SQL), 134TimerInterval, 98 update (record, VBA), 88 Wtoolbars. See menus Update (recordset), 109 wait for event, 98toolbox (for drawing), 18 user role, 6, 132 week number (Format function),tools (Visual Basic), 80–83 user windows (forms), 18Top (position property), 28, 100 126tree structure (in database), 16 V WHERE (query), 54trim functions (VBA), 128 Value (text box), 72 While statements (VBA), 118twips (size unit), 100 Var (query), 60 Width (size property), 28, 100type check functions (VBA), 130 variables (declarations, VBA), wildcarding (text comparison),type conversion (VBA), 125type declaration (VBA), 120 120 64, 124types of data. See data type variables (initial values), 120 windows (forms), 18 variant data type (VBA), 120 With-End (VBA), 123U VarType (VBA), 130 WizardUBound (array bound, VBA), vbKey . . . (VBA), 97 - form, 31 view (of form) - lookup, 14 128 - datasheet advantages, 38Ucase function (VBA), 128 - form view advantages, 38 Yunbound controls, 20, 40 - shortcut keys for changing, 22 yes/no field, 11undo data entry, 8 Visual Basic, 68–115, 116–34 - breakpoints, 82154 Index