5
© 2007 The Smart Method Ltd
Contents
Who Is This Book For.......................................................................................................1
Use of this book as courseware.....................................................................................................................1
Contents .............................................................................................................................5
How to Use This Book .....................................................................................................11
Feedback....................................................................................................................................................... 11
Downloading the sample files..................................................................................................................... 11
Only one file is essential .............................................................................................................................. 11
Session and lesson files................................................................................................................................ 11
Problem resolution....................................................................................................................................... 11
Typographical Conventions Used In This Guide ..........................................................12
Use of American English ............................................................................................................................. 13
Putting the Smart Method to Work.................................................................................14
Access version and service pack ................................................................................................................. 14
Sessions and lessons..................................................................................................................................... 14
First page of a session.................................................................................................................................. 15
Every lesson is presented on two facing pages.......................................................................................... 16
Learning by participation............................................................................................................................ 17
Session One: The Sample Database..............................................................................19
Session Objectives........................................................................................................................................ 19
Lesson 1-1: Understand the sample database.................................................................................................. 20
Lesson 1-2: Create lookup fields....................................................................................................................... 22
Lesson 1-3: Create a concatenated lookup field............................................................................................... 24
Lesson 1-4: Create one-to-many table relationships........................................................................................ 26
Lesson 1-5: Create a many-to-many table relationship................................................................................... 28
Lesson 1-6: Set default and required values.................................................................................................... 30
Lesson 1-7: Create a general-purpose query.................................................................................................... 32
Lesson 1-8: Create the prototype main form using a wizard ......................................................................... 34
Lesson 1-9: Improve the prototype form for keyboard input......................................................................... 36
Lesson 1-10: Create a simple report using a wizard........................................................................................ 38
Session 1: Exercise........................................................................................................................................ 41
Session 1: Exercise answers......................................................................................................................... 43
Session Two: Adding Advanced Features Using Wizards ...........................................45
Session Objectives........................................................................................................................................ 45
Lesson 2-1: Add a switchboard to the application using the Switchboard Wizard ...................................... 46
Lesson 2-2: Add a command button to a form using a wizard ...................................................................... 48
pg_0002
6
Lesson 2-3: Add a combo box lookup feature to a form using a wizard....................................................... 50
Session 2: Exercise........................................................................................................................................ 53
Session 2: Exercise answers......................................................................................................................... 55
Session Three: The Object-Orientated Paradigm......................................................... 57
Session Objectives........................................................................................................................................ 57
Lesson 3-1: Understand properties .................................................................................................................. 58
What are properties. ................................................................................................................................... 58
Objects contain objects................................................................................................................................. 59
How properties are set for controls ............................................................................................................ 59
Lesson 3-2: Understand methods..................................................................................................................... 60
What are methods....................................................................................................................................... 60
Methods may be modified by arguments.................................................................................................. 60
Examples of methods available in Access objects...................................................................................... 61
Lesson 3-3: Understand events......................................................................................................................... 62
What are events........................................................................................................................................... 62
Lesson 3-4: Understand the Access object model............................................................................................ 64
About object hierarchies.............................................................................................................................. 64
The Car object model................................................................................................................................... 64
The Access object model.............................................................................................................................. 65
Microsoft’s object model diagram .............................................................................................................. 65
Session 3: Exercise........................................................................................................................................ 67
Session 3: Exercise answers......................................................................................................................... 69
Session Four: An Introduction To VBA ......................................................................... 71
Session Objectives........................................................................................................................................ 71
Lesson 4-1: Understand subs............................................................................................................................ 72
Defining procedures in VBA code.............................................................................................................. 72
Calling procedures in VBA code................................................................................................................. 72
Lesson 4-2: Step through code.......................................................................................................................... 74
Lesson 4-3: Understand step over and step out .............................................................................................. 76
Lesson 4-4: Understand variables.................................................................................................................... 78
What are variables....................................................................................................................................... 78
Lesson 4-5: Use the immediate window to view and change variable contents........................................... 80
Lesson 4-6: Use the locals window to view and change variable contents ................................................... 82
Session 4: Exercise........................................................................................................................................ 85
Session 4: Exercise answers......................................................................................................................... 87
Session Five: Professional Grade VBA ......................................................................... 89
Session Objectives........................................................................................................................................ 89
Lesson 5-1: Understand data types.................................................................................................................. 90
String ............................................................................................................................................................ 91
pg_0003
7
Currency....................................................................................................................................................... 91
Boolean ......................................................................................................................................................... 91
Date............................................................................................................................................................... 91
Double........................................................................................................................................................... 91
Integer/Long Integer.................................................................................................................................... 91
Variant .......................................................................................................................................................... 91
Lesson 5-2: Understand and implement strong typing and explicit variable declaration............................ 92
Lesson 5-3: Understand arguments.................................................................................................................. 94
Lesson 5-4: Understand functions.................................................................................................................... 96
Lesson 5-5: Understand ByRef and ByVal argument types............................................................................ 98
Lesson 5-6: Set and retrieve form control values from within VBA............................................................. 100
Lesson 5-7: Understand scope ........................................................................................................................ 102
Lesson 5-8: Implement error handling........................................................................................................... 104
Lesson 5-9: Use the help system and the object browser .............................................................................. 106
Getting help using the Visual Basic help system ..................................................................................... 106
Getting help using the Object Browser..................................................................................................... 106
Session 5: Exercise...................................................................................................................................... 109
Session 5: Exercise answers....................................................................................................................... 111
Session Six: Improving Wizard-Generated Code........................................................113
Session Objectives...................................................................................................................................... 113
Lesson 6-1: Understand and improve the command button wizard’s error handling code ...................... 114
Lesson 6-2: Understand the DoCmd object and it’s DoMenuItem method................................................. 116
Lesson 6-3: Implement the DoCmd object’s RunCommand method........................................................... 118
Lesson 6-4: Replace standard warnings and error messages with custom dialogs..................................... 120
Lesson 6-5: Understand the combo box wizard code.................................................................................... 122
Lesson 6-6: Improve the combo box wizard code......................................................................................... 124
Lesson 6-7: Correctly name controls .............................................................................................................. 126
Lesson 6-8: Program an add record button.................................................................................................... 128
Lesson 6-9: Handle the Form object’s Current event .................................................................................... 130
Lesson 6-10: The Access object model revisited ............................................................................................ 132
DAO (Data Access Objects)....................................................................................................................... 132
Session 6: Exercise...................................................................................................................................... 133
Session 6: Exercise answers....................................................................................................................... 135
Session Seven: Speeding up Data Entry .....................................................................137
Session Objectives...................................................................................................................................... 137
Lesson 7-1: Filter using Filter by Form........................................................................................................... 138
Lesson 7-2: Add an option group control ...................................................................................................... 140
Lesson 7-3: Implement the option group control’s functionality................................................................. 142
Lesson 7-4: Add interactive help.................................................................................................................... 144
pg_0004
8
Lesson 7-5: Add a shortcut hotkey to a control............................................................................................. 146
Session 7: Exercise...................................................................................................................................... 149
Session 7: Exercise answers....................................................................................................................... 151
Session Eight: Implementing A Popup Browse Form................................................ 153
Session Objectives...................................................................................................................................... 153
Lesson 8-1: Create a browse form using a listbox control ............................................................................ 154
Lesson 8-2: Convert a form to a dialog box................................................................................................... 156
Lesson 8-3: Use the recordset object’s FindFirst and Clone methods and Bookmark property................. 158
Lesson 8-4: Launch the popup form from the main form ............................................................................ 160
Session 8: Exercise...................................................................................................................................... 163
Session 8: Exercise answers....................................................................................................................... 165
Session Nine: Maintaining Static Data ........................................................................ 167
Session Objectives...................................................................................................................................... 167
Lesson 9-1: Create a self updating bound combo box .................................................................................. 168
Lesson 9-2: Make the combo box accept apostrophes................................................................................... 170
Lesson 9-3: Create and use a global constant ................................................................................................ 172
Lesson 9-4: Create a popup dialog to update related data........................................................................... 174
Lesson 9-5: Add a command button to invoke the Add Director form....................................................... 176
Lesson 9-6: Improve the code to auto-select the contents of the combo box............................................... 178
Lesson 9-7: Add simple forms to maintain static data.................................................................................. 180
Session 9: Exercise...................................................................................................................................... 183
Session 9: Exercise answers....................................................................................................................... 185
Session Ten: Validations and Sub-Forms................................................................... 187
Session Objectives...................................................................................................................................... 187
Lesson 10-1: Add an advisory validation ...................................................................................................... 188
Lesson 10-2: Add a VBA business rule to a form.......................................................................................... 190
Lesson 10-3: Design a query for the many-to-many subform ...................................................................... 192
Lesson 10-4: Create the many-to-many subform .......................................................................................... 194
Lesson 10-5: Convert the combo box to self-updating.................................................................................. 196
Lesson 10-6: Add a subform........................................................................................................................... 198
Session 10: Exercise.................................................................................................................................... 201
Session 10: Exercise answers..................................................................................................................... 203
Session Eleven: Creating a Dialog-Driven Report...................................................... 205
Session Objectives...................................................................................................................................... 205
Lesson 11-1: Design a query for the report.................................................................................................... 206
Lesson 11-2: Create the prototype report....................................................................................................... 208
Lesson 11-3: Format the prototype report – first fix...................................................................................... 210
Lesson 11-4: Format the prototype report – second fix................................................................................. 212
pg_0005
9
Sample report Design View....................................................................................................................... 214
Sample report Print Preview view............................................................................................................ 215
Lesson 11-5: Design the tab based user interface .......................................................................................... 216
Lesson 11-6: Complete the user interface....................................................................................................... 218
Lesson 11-7: Implement Print Preview functionality.................................................................................... 220
Lesson 11-8: Implement Print functionality................................................................................................... 222
Session 11: Exercise.................................................................................................................................... 225
Session 11: Exercise answers ..................................................................................................................... 227
Session Twelve: Using Word with Access ..................................................................229
Session Objectives...................................................................................................................................... 229
Lesson 12-1: Create a Word document template........................................................................................... 230
Lesson 12-2: Create a command button that will open Word ...................................................................... 232
Lesson 12-3: Transfer data from Access to Word .......................................................................................... 234
Session 12: Exercise.................................................................................................................................... 237
Session 12: Exercise answers ..................................................................................................................... 239
Session Thirteen: Adding Switchboards, Menus and Toolbars ................................241
Session Objectives...................................................................................................................................... 241
Lesson 13-1: Create the user interface ............................................................................................................ 242
Lesson 13-2: Create a custom global menu.................................................................................................... 244
Lesson 13-3: Add standard top-level menus and items................................................................................ 246
Lesson 13-4: Add a custom top-level menu and items ................................................................................. 248
Lesson 13-5: Create a custom toolbar............................................................................................................. 250
Session 13: Exercise.................................................................................................................................... 253
Session 13: Exercise Answers .................................................................................................................... 255
Session Fourteen: Finalizing The Application For Deployment ................................257
Session Objectives...................................................................................................................................... 257
Lesson 14-1: Implement universal error handling ........................................................................................ 258
Lesson 14-2: Explicitly declare and destroy objects ...................................................................................... 260
Lesson 14-3: Improve form appearance......................................................................................................... 262
Lesson 14-4: Further improve form appearance............................................................................................ 264
Lesson 14-5: Setting startup options............................................................................................................... 266
Lesson 14-6: Test the application.................................................................................................................... 268
Session 14: Exercise.................................................................................................................................... 271
Session 14: Exercise answers ..................................................................................................................... 273
Appendix A: The Rules..................................................................................................275
1: General rules................................................................................................................................................ 276
2: Table and Field naming............................................................................................................................... 280
3: Field properties............................................................................................................................................ 281
pg_0006
10
4: Access object naming .................................................................................................................................. 282
5: Form control naming................................................................................................................................... 282
6: Variable naming.......................................................................................................................................... 283
7: Constants ..................................................................................................................................................... 284
8: Variable declaration .................................................................................................................................... 284
9: Error handling............................................................................................................................................. 285
10: Object destruction...................................................................................................................................... 285
Appendix B: Useful Links............................................................................................. 287
Useful web site links....................................................................................................................................... 288
Index............................................................................................................................... 289