Index
database window, 10
• Symbols •                                 description of, 1
' (apostrophe), 52, 83                      11.0 Object Library, 31
! (bang character), 81, 155, 359            Help system, 354
: (colon), 290                              learning before using VBA, 351
:= (colon equal sign), 45                   Modules container, 10
, (comma), 38, 42, 43                       Query menu, 122
_ (continuation character), 156–158         references for learning about, 9
. (dot), 79, 81, 359                        standard module, 12–13
= (equal sign), 80                          switching between Visual Basic editor
! (exclamation point), 138                      and, 15–16
* (multiplication operator), 106           accessing object library, 317–319
( ) (parentheses)                          Action Arguments, 311–312, 315
checking, 275–276                        action query
for ending procedure, 37, 42, 353         hiding warning message, 125–126
| (pipe), 63                                on one record, 136
+ (plus sign), 44, 106, 157                 overview of, 121–123
# (pound character), 83                    Add Procedure dialog box, 36, 37
? (question mark), 79                      address, correcting case of with
" (quotation marks), 58, 83, 353                PCase() function, 248–252
[] (square brackets)                       ADO (ActiveX Data Objects), 141
in Access, 91                            AdOpenDynamic cursor type, 145
as representing argument, 40, 41         After Focus event, 89
as representing name, 154–155            After Insert Event form event, 98
typing, 353                              After Update event, 220
All tab (Properties sheet), 185, 203
AllForms collection, 149
•A•                                        AllTables collection, 209
Access 2003 All-in-One Desk Reference      Alt+F11 (switch between Access and
For Dummies (Alan Simpson,                  Visual Basic editor), 16, 354
Margaret Levine Young, Alison          Alt+Q (close Visual Basic editor), 15
Barrows), 9                            AND operator, 67
Access 2003 For Dummies                    apostrophe ('), 52, 83
(John Kaufeld), 9                      appearance of object, changing
Access (Microsoft)                          color, 99–103
class module, 12–15                        font or style of text, 103
client-server relationship and, 297–303    special effects, 103–104
With...End With statement, 104–105
append query, 122, 131–132                Before Update event, 89
appending record to table                 binding dialog box to table, 184
overview of, 131–132                     blank space, inserting in code, 157
with query, 133–134                      BOF property, 145
single, with SQL, 132–133                book, Web site for, 5
application modal, 178                    bookmark, 326, 327
Application object                        Boolean data type, 53, 62
description of, 319–320                  BorderColor property, 100
Outlook, 326                             BorderStyle property, 101
argument                                  Bound Column property, 230, 232–233
CursorType (recordset), 142–143, 145     bound control, adding to form, 187
definition of, 40                        breaking long line of code, 156–158
function and, 244–245                    breakpoint
Help page and, 43–45                      clearing, 284–285
keyword support for, 41                   setting in code, 280–284
LockType (recordset), 143                bug, definition of, 267
MsgBox() keyword, 43–45, 177–179         Build button (Design view), 241, 242
named, 45                                Building a List of Report Names
OpenForm method of DoCmd object,             listing, 194
107–108                               button
order of, 45                              default, for message box, 178–179
parameters and, 161                       On Click event and, 87
square brackets ([]) and, 40, 41         buttons argument of MsgBox()
Sub procedure, 56                            keyword, 43–44, 177–179
syntax for, 55                           Byte data type, 53, 62
title, 44
value for, 42
argument list, 55
•C•
array, storing data in, 58–60             calling procedure
ASCII number, 191                          from event, 170–172
asking question of user, 362–363           overview of, 18
Automation technology, 28, 317            changing. See also appearance of object,
changing
•B•                                        contents of control, 360
control type, 204
BackColor property, 100                    table record, 134–136
back-end database table, 298, 299, 300     value of property, 80
backing up data, 135                      checking
BackStyle property, 101                    parentheses, 275–276
bang character (!), 81, 155, 359           whether form is open, 358
Barrows, Alison, Access 2003 All-in-One   class, definition of, 14, 31, 76
Desk Reference For Dummies, 9         Class icon, 78
Beep method of DoCmd object, 81–82
Index     373
class module (Access)                      CodeContextObject, 155
description of, 12–13, 87                 collection
finding, 13–15                             AllForms, 149
Me term, 155                               AllTables, 209
Project Explorer and, 24                   Count property, 79, 147–148
Classes list (Object Browser), 31           CurrentProject.AllForms, 358
clearing breakpoint, 284–285                CurrentProject.AllReports, 190
client, definition of, 297                  enumerating, 147–153
Close method of DoCmd object, 109           Forms, 72, 73, 79, 149, 358–359
closing                                     looping through, 147–148
form, 109                                  object compared to, 72–74, 148
Object Browser, 33                        colon (:), 290
recordset, 146                            colon equal sign (:=), 45
table through VBA, 130–131                color
Visual Basic editor, 15–16                 for dialog box, 186
code. See also listings; syntax             of object, changing, 99–103
automating task and, 12                   Color Builder, 100–101
blank space, inserting in, 157            Column Count property, 232
breaking long line of with continuation   combo box
character (_), 156–158                  creating, 189–190
in class module, 14                        description of, 188–189, 201–202
commenting, 52–53                          drop-down menu, customizing, 195
conventions for, 2                         dynamic drop-down list for, building,
converting query to, 129                      190–194
copying, 355                               field names, listing, 204–206
creating in pieces, 265                    hidden values in, 229–233
definition of, 11                          linking lists, 217–219
encapsulation of, 218–219, 221             linking lists across forms, 223–229
linking to external data through,          properties, 203
305–307                                 as quick find tool, 233–236
modifying existing, 46–48, 57, 123–125,    self-referential, creating, 237–239
153–154                                 table/query field values, listing, 213–217
object and event for, choosing, 92–95      text options, listing, 207–213
PaymentMethod_AfterUpdate()                unbound control, adding to, 202–203
procedure, 93–95                        updating, 224–226, 361
recordset, creating, 139–140               updating from table, 222
sample, acquiring, 51                      updating report names in, 221–222
slowing down, 280–284                     Combo Box Wizard, 231–232, 234–235, 238
typing, 27–28                             comma (,), 38, 42, 43
Code window (Visual Basic editor)          Command Button Wizard, 288–289
Help methods in, 84                       commenting code, 52–53
overview of, 15, 16, 26–28                comparison operators, 66–67
shortcut keys, 367–369                    compilation or compile time, 269
viewing existing code in, 153–154
compile error                            showing or hiding, 95–96
cause of, 269                           spin box, creating, 195–198
description of, 270, 271–273            unbound, adding to form, 187, 202–203
Expected: end of statement              updating on separate form, 228–229
message, 274                          Visible property, 79–80
Expected: expression message,          Control Wizards
273–274                               adding control to dialog box and, 187
Expected: list separator or )           Combo Box Wizard, 231–232,
message, 274–275                       234–235, 238
message example, 28, 272                error-handling code and, 288–289
understanding message, 271             Control Wizards button
compiling line of code, 28               Design view, 17
concatenating strings, 157               Toolbox, 231
condition, description of, 63           converting
conditional expression, 66–67            linked table to local table, 300
connection                               macro to VBA code, 313, 314
closing, 146                            query to code, 129
defining, 140–141                      CopyFromRecordset method (Excel), 344
to external program, 321–322           copying
constant                                 code from Web, 46–47, 355
buttons argument of MsgBox()            data before querying, 135
keyword, 178–179                    data before testing, 251
color, 100                              SQL statement into VBA code, 123–125
definition of, 38, 43                   table or query results to Excel
list of, 39                                worksheet, 344–347
storing data in, 58                    CopyObject action, 314
Constant icon, 78                       Count property of collection, 79, 147–148
continuation character (_), 156–158     CREATE TABLE statement (SQL), 129–130
control. See also combo box; list box   Ctrl+C (Copy), 46
adding to dialog box, 186–188          Ctrl+F8 (Run to Cursor), 283
bound, adding to form, 187             Ctrl+Shift+F8 (Step Out), 283, 284
changing contents of, 360              Ctrl+V (Paste), 46
changing type of, 204                  Currency data type, 53, 62
description of, 73                     CurrentProject.AllForms
enabling or disabling, 90–91              collection, 358
event and, 89                          CurrentProject.AllReports
filling with data, 105–107                 collection, 190
locking and unlocking, 96              cursor
moving cursor to, 359–360               moving to control, 359–360
naming, 203                             positioning, 38, 91–92
on open form, referring to, 359        CursorType argument (recordset),
requerying with every new record,          142–143, 145
238–239                            Customer Lookup query, 230–231, 234
Index   375
Debug➪Step Into, 283
•D•                                      Debug➪Toggle Breakpoint, 281
DAO (Data Access Objects), 142           Decimal data type, 54
data                                     decision-making
backing up, 135                          comparison operators, 66–67
copying before testing, 251              If...End statement, 67–68
form control, filling with, 105–107      procedure and, 128
importing from external database,        Select Case block, 68–69
303–305                              declarations area of module, 60–61
linking to external through code,       declaring
305–307                               array, 59
passing to function, 244–245             object variable, 152, 321
passing to procedure, 55–57, 164         variable, 165
recordset, filling with, 142–143        Default Method icon, 78
storing in combo box, 232–233           Default Property icon, 77
Data Access Objects (DAO), 142           Default Value property (combo box
Data tab (Properties sheet), 189             and list box), 203
data types, 53–55, 62                    Delete Event form event, 98
database. See also recordset; SQL        Delete query, 122, 134–137
(Structured Query language); table   deleting
back-end and front-end, 298, 299, 300    table record, 134–136
importing data from external, 303–305    table through VBA, 130–131
objects and, 85                         delimiting literal text and dates, 83
splitting into two files, 297           dependency, 218
Database Splitter, 297–303               Design view
DataMode argument of OpenForm             Build button, 241, 242
method of DoCmd object, 108           combo box sample in, 190
Datasheet view of query, 138              Control Wizards button, 17
Date data type, 53                        Detail band, 186
Date() function, 56                       dialog box sample in, 187
date, literal, 83                         form events in, 96–97
Date/Time data type, 62                  designing message box, 177–180
Debug toolbar, 284                       Detail band (Design view), 186
debug window (Visual Basic editor),      Details pane (Object Browser), 31
25–26                                detecting right-click, 198–199
Debug➪Clear All Breakpoints, 284         dialog box
debugging                                 controls, adding, 186–188
compile errors, 271–276                  converting form to, 182–183
definition of, 267                       form properties, setting, 184–186
logical errors, 276–285                  overview of, 175
shortcut keys for, 370                   storing settings, 183–184
Debug.Print statement                    Dim statement, 152
checking variable with, 277–280         disabling form control, 90–91
seeing name of object with, 150
displaying                                 errors
control, 95–96                             compile, 271–276
custom message, 361–362                    logical, 276–285
Immediate window, 25                       runtime, 285–294
properties, 75–76                          types of, 270–271
toolbar, 21                               event
docking Code window, 27                     After Focus, 89
DoCmd (do command) object                   After Insert Event form event, 98
Beep method, 81–82                         After Update, 220
Close method, 109                          Before Update, 89
description of, 32                         calling procedure from, 170–172
methods for forms, 111–113, 364–365        for code, choosing, 92–95
OpenForm method, 82, 107–108,              Delete Event form event, 98
357–358                                 form, responding to, 96–99
TransferDatabase method, 304–307           Load Event form event, 98
DoCmd.RunSQL statement, 124, 125, 146       On Change, 89
Do...Loop block, 62–63                      On Click, 87, 89, 188, 198–199
dot (.), 79, 81, 359                        On Close form event, 98
DOT file, 327                               On Current form event, 98
Double data type, 54, 62                    On Insert, 226
drop-down menu                              On Load, 190, 208, 219
customizing, 195                           On Mouse Down, 89, 198
dynamic, building, 190–194                Event icon, 78
static versus dynamic, 190                Events tab (Properties sheet), 88, 96
Excel (Microsoft)
•E•                                         code to copy form data to worksheet,
338–347
element of array, 58                        interacting with, 336
e-mail, sending via Outlook, 322–326        macro, running from Access, 347–348
enabling form control, 90–91                query and form, creating, 337–338
encapsulation, 218–219, 221                 worksheet, creating, 336–337
Enter key and typing code, 27–28           exclamation point (!), 138
Enum icon, 78                              execute, definition of, 12
enumerating collection                     existing code, modifying
For Each loops, 149–151                    argument list and, 57
object, using shorter name for, 152–153    copying and pasting from Web, 46–47
overview of, 147–148                       For Each...Next loop and, 153
EOF property, 145                           importing standard module, 47–48
equal sign (=), 80                          overview of, 46, 48, 153–154
error handler                               SQL statement into VBA code, 123–125
creating, 287–290                         Expected: end of statement error
writing, 290–294                              message, 274
Index   377
Expected: = error message, 271–272              class module, opening, 14–15
Expected: expression error message,             class procedures, 87–89
273–274                                     closing, 109
Expected: list separator or )                   control, enabling or disabling, 90–96
message, 274–275                            converting to dialog box, 182–183
exporting                                       description of, 10, 14, 73
interactively, 311                             DoCmd object methods for, 111–113
with macro, 311–313                            opening, 107–108
exposing object, 28–29                          opening from VBA, 357–358
expression                                      properties to turn into dialog box,
conditional, 66–67                                184–186
testing in Immediate window, 26, 42–43         referring to open, 358–359
Expression Builder, 241                         working with, 87
form event, responding to, 96–99
•F•                                           form names, making list of, 211–213
Form view
F1 (Help), 39, 356                              combo box sample in, 190
F2 (open Object Browser), 30                    dialog box sample in, 187
F4 (Properties sheet), 74                     Form_Address Book Form listing, 111
F8 (Step Into), 283                           Format function, 260
Fancy SkipLabels Dialog Box                   Format tab (Properties sheet), 99
code for, 220                               formatting worksheet cells from VBA,
overview of, 217–218                             343, 347–348
field in recordset, referring to, 145–146     Form_Current() procedure, 97–98
field name                                    Form_Load() procedure, 219
listing in combo box or list box, 204–206   Forms collection
referring to, 154–155                         Count property, 79
File➪Close, 15                                  description of, 149
File➪Export, 311                                object model and, 72, 73
File➪Get External Data➪Import, 47               referring to open form and, 358–359
filling                                       For...Next block, 64–65
form control with data, 105–107             friction in computer, 268
recordset with data, 142–143, 166–170       front-end database, 298, 299, 300
FilterName argument of OpenForm               function. See also procedure
method of DoCmd object, 107               arguments and, 244–245
focus, giving control, 91                       built-in, listing, 242
font, changing, 103                             creating, 243–244
For Each...Next loop, 149–151, 153, 191         Date(), 56
ForeColor property, 100, 103                    Format, 260
form                                            GetObject(), 341
adding related record to another table,       Mid(), 278, 280
109–111                                   NumWord(), 253–265, 279–280
appearance of object, changing, 99–107        overview of, 241
function (continued)                   high-level programming language, 268–269
passing data to, 244–245             hyperlink field, 231
PCase(), 247–252
Public, 19, 37
returning value from, 245
•I•
role of, 241–243                     icons
SalesTax() example, 244–247            for items in Object Browser, 77–78
StrConv(), 247, 249                    for saved action queries, 138
testing, 246–247                     identifier
testing from Immediate window, 281     description of, 80
UCase(), 278                           square brackets and, 154–155
Function procedure                       syntax, 81
creating, 36–38                      If...Else...End If statement, 93
data returned by, 56                 If...End If block, 227
overview of, 19–20                   If...End statement, 67–68
If...Then...Else block, 106
•G•                                    If...Then...End If statement, 197
Immediate window (Visual Basic editor)
GetObject() function, 341                overview of, 25–26
getting value of property, 79–80         re-executing statement in, 79
Global icon, 78                          running procedure from, 281
global scope and standard module, 13     shortcut keys, 367–369
guessing, 354                            testing function in, 281
testing statement in, 42–43
•H•                                      viewing, 22
importing
heat of computer, 268                    data from external database, 303–305
Help system                              query from external database, 307–309
Access compared to VBA, 354             standard module, 47–48
argument and, 43–45                     table, 303
methods, 112                          inserting blank space in code, 157
opening, 39                           Insert➪Picture, 196
procedure and, 39–43                  Insert➪Procedure, 36, 150
properties and methods, 82–85         instance, 321
showing properties, 75–76             Integer data type, 54, 62
Help window (Object Browser), 33       IsLoaded property of form, 358
hiding                                 Item Data(x) property (combo box
control, 95–96                             and list box), 203
toolbar, 21                           item, undocking and re-docking, 22
warning message, 125–126
Index     379
linking
•K•                                         to external data through code, 305–307
Kaufeld, John, Access 2003                  to external tables, 308–309
For Dummies, 9                          table, 303
keyboard shortcuts                        linking lists
Alt+F11 (switch between Access and         across forms, 223–224
Visual Basic editor), 16, 354           getting forms in sync, 228–229
Alt+Q (close Visual Basic editor), 15      opening form to enter new record,
Code and Immediate window, 367–369            226–227
Ctrl+C (Copy), 46                          overview of, 217–219
Ctrl+F8 (Run to Cursor), 283               running code when form opens, 219–220
Ctrl+Shift+F8 (Step Out), 283, 284         running code when user makes a
Ctrl+V (Paste), 46                            choice, 220–223
for debugging, 370                         updating combo box or list box, 224–226
F1 (Help), 39, 356                       list box
F2 (open Object Browser), 30               description of, 201–202
F4 (Properties sheet), 74                  field names, listing, 204–206
F8 (Step Into), 283                        hidden values in, 229–233
Shift+F8 (Step Over), 283                  linking lists, 217–219
VBA editor, 369                            linking lists across forms, 223–229
keywords. See also MsgBox() keyword         properties, 203
arguments supported by, 41                 table/query field values, listing, 213–217
Help page for, 39–43, 153–154              text options, listing, 207–213
Me!, 204                                   unbound control, adding to, 202–203
New, 321                                   updating, 224–226, 361
List Rows property (combo box), 203
list separator, 274–275
•L•                                       List Width property (combo box), 203
label, 290                                listing built-in functions, 242
Label Wizard, 159                         listings
Levine Young, Margaret, Access 2003         building list of report names, 194
All-in-One Desk Reference              combo box of table and query names,
For Dummies, 9                            210–211
Limit to List property                      combo box of table names, 209–210
(combo box), 203                       combo box value list property,
line of code                                   filling, 208
breaking with continuation character,     combo box with form names, filling, 212
156–158                                copying form data to Excel
compiling, 28                                worksheet, 339
Linked Table Manager, 298, 299              copying recordset to Excel worksheet,
344–345
listings (continued)
distinguishing between left and right     •M•
mouse clicks, 199                      macro
Form_Address Book Form, 111                actions for, 313–315
Form_Current() procedure, 98               description of, 10
importing query from external              Excel, running from Access, 347–348
database, 307–308                       exporting with, 311–313
linking to external table, 309            macro conversion, 288–289
merge code, 330–331                       mailing labels, skipping over used
NumWord() function, 253–255                   (SkipLabels procedure)
PaymentMethod_AfterUpdate()                 calling procedure from event, 170–172
procedure, 94                            combo box, creating for, 189–195
PCase() function, 248                       controls, adding to dialog box, 186–188
Send button procedure, 323                 copying label report, 165
SkipLabels procedure, 162–164              declaring variables, 165
updating combo box from table, 222         getting report record source, 165–166
updating combo box of report               listing, 162–164
names, 221                              overview of, 158–161
updating control on separate form,         passing data to, 164
228–229                                 recordset, creating and filling, 166–170
literal string, 83, 157–158                  spin box control, creating for, 195–198
Load Event form event, 98                    table for storing dialog box settings for,
local scope and class module, 13                183–184
local table, 299, 300                       maintenance mode technology, 142
Locals window, 282–283                      Make-Table query, 122, 128–129
locking control, 96                         managing recordset, 144–145
LockType argument (recordset), 143          MDB file, 297
logical error                               Me! keyword, 204
description of, 270, 276–277              member of class, definition of, 31, 76
slowing down code, 280–284                Members list (Object Browser), 31
variables, checking with Debug.Print      menu. See drop-down menu
expression, 277–280                    message box. See also MsgBox() keyword
logical operators, 67                        as application modal, 178
Long data type, 54, 62                       code execution and, 177
loop                                         default buttons for, 178–179
description of, 62                         designing, 177–180
Do...Loop block, 62–63                     Help button, 180
For...Next block, 64–65                    overview of, 175
While...Wend block, 64                     as pop-up, 178
looping through collections                  question asking, 176–177
For Each...Next loop, 149–151,             responding to button click, 180–182
153, 191                                Select Case block and, 182
object, using shorter name for, 152–153   message, custom, showing, 361–362
overview of, 147–148
low-level machine language, 268
Index   381
method                                       Microsoft Word
Beep of DoCmd object, 81–82                  form, creating, 329–330
Close of DoCmd object, 109                   merge code, writing, 330–335
CopyFromRecordset (Excel), 344               sending data to, 326
description of, 75–77                        template, creating, 327–328
DoCmd object for forms, 111–113,            Mid() function, 278, 280
364–365                                  modifying existing code
Help page and, 112                           argument list and, 57
OpenForm of DoCmd object, 82, 107–108,       copying and pasting from Web, 46–47
357–358                                   For Each...Next loop and, 153
Requery, 224–226                             importing standard module, 47–48
TransferDatabase of DoCmd object,            overview of, 46, 48, 153–154
304–307                                   SQL statement into VBA code, 123–125
using, 81–82                                module. See also class module (Access);
Method icon, 77                                  standard module (Access)
Microsoft. See also Microsoft Access;         declarations area, 60
Microsoft Excel; Microsoft Word           definition of, 89
ActiveX Data Objects 2.1 Library, 32, 141   Module icon, 78
DAO 3.6 Object Library, 32                  module-level declaration, 36, 60–61
maintenance mode technology, 142            Modules button (Access), 13
Outlook, sending e-mail via, 322–326        Modules container (Access), 10
Web site, 354                               MOMSecure.mdb example, 300–303
Microsoft Access                             moving editor window, 282
class module, 12–15                         MsgBox() keyword
client-server relationship and, 297–303      arguments for, 43–45
database window, 10                          buttons argument, 43–44, 177–179
description of, 1                            Help page, 40–43
11.0 Object Library, 31                      overview of, 38–39
Help system, 354                             question asking, 176–177
learning before using VBA, 351               responding to button click, 180–182
Modules container, 10                        syntax for, 176, 272
Query menu, 122                             multidimensional array, 60
references for learning about, 9            multiplication operator (*), 106
standard module, 12–13
switching between Visual Basic editor
and, 15–16
•N•
Microsoft Excel                              name, correcting case of with PCase()
code to copy form data to worksheet,            function, 248–252
338–347                                  Name property
interacting with, 336                        combo box and list box, 203
macro, running from Access, 347–348          of object, 152
query and form, creating, 337–338            of recordset, 145–146
worksheet, creating, 336–337
named argument, 45                           help with objects and collections from,
naming                                          76–77
bang character (!) and, 359                 icons for items in, 77–78
cell or range in Excel, 336                 methods listed in, 112–113
control on form, 203                        object model, exploring through, 319
square brackets and, 91                     overview of, 30–33, 355–356
variable, 61–62                            Object data type, 54
New Customer form example,                  object library
223–226, 228                             accessing, 317–319
New keyword, 321                             description of, 28–29
NOT operator, 67                             list of, 31–32
numbers                                      Outlook, 325
ASCII, 191                                  referencing external, 321–322
for buttons argument of MsgBox()            searching, 32–33
keyword, 178–179                         setting reference to, 29–30
numeric expression, 42                      Object Library icon, 78
NumWord() function                          object model
code for, 253–255                           ADO (ActiveX Data Objects), 141
explanation of, 258–265                     description of, 71, 72
uses of, 255–257                            exploring, 319
writing and checking for logical errors,    icons for items in, 77–78
279–280                                  objects and collections, 72–74
properties and methods, 75–77
•O•                                          viewing, 74
object variable, declaring, 152
object                                      object-oriented, definition of, 10
for code, choosing, 92–95                  OLE Automation object library, 32
as collection, 148                         On Change event, 89
collection compared to, 72–74              On Click event
color, changing, 99–103                     description of, 87, 89
description of, 71                          PrintBttn control, 188
exposing, 28–29                             right-click, detecting, 198–199
font or style of text, controlling, 103    On Close form event, 98
methods of, 81–82                          On Current form event, 98
Name property, 152                         On Error statement, 288, 291
referring to, 155–156                      On Insert event, 226
special effects, changing, 103–104         On Load event
using shorter name for, 152                 description of, 190
With...End With statement, using to         Form_Load() procedure, 219
change appearance of, 104–105            use of, 208
Object Browser tool (Visual Basic editor)   On Mouse Down event, 89, 198
DoCmd object information in, 365           one-dimensional array, 59
Help methods in, 84–85                     opacity of background or border, 101
Index   383
Open method of recordset, 142–143          passing data
OpenArgs argument of OpenForm               to function, 244–245
method of DoCmd object, 108              to procedure, 55–57
OpenForm method of DoCmd object, 82,        to SkipLabels procedure, 164
107–108, 357–358                       PaymentMethod_AfterUpdate()
opening                                        procedure listing, 94
class module, 14–15                       PCase() function
Excel and workbook, 340–341                code for, 247–248
Expression Builder, 241                    explanation of, 249–250
form, 107–108                              uses of, 250–252
form and adding related record to          writing and checking for logical errors,
another table, 109–111                     277–279
form from VBA, 357–358                    pipe (|), 63
form to enter new record, 226–227         placeholder, 41, 45. See also bookmark
Help page, 39                             plus sign (+), 44, 106, 157
module, 15                                pop-up message box, 178
module in VBA editor, 24                  positioning cursor, 38, 91–92
Object Browser, 30                        pound (#) character, 83
program, 321                              Print Preview, using, 170, 257
Properties sheet, 73–74                   printing check amount (NumWord()
Query Properties sheet, 214                   function)
operators                                   code for, 253–255
comparison, 66–67                          explanation of, 258–265
logical, 67                                uses of, 255–257
Option Compare Database                     writing and checking for logical errors,
declaration, 243                           279–280
OR operator, 67                            printing report, 363–364
Orders form example, 223–226, 228          Private procedure, 19, 20
Outlook (Microsoft), sending e-mail via,   procedure. See also function;
322–326                                     SkipLabels procedure;
OutputTo action, 311–313, 314                  Sub procedure
calling, 18
•P•                                         calling from event, 170–172
class type, working with, 87–89
page, description of, 10                    creating, 36–38
pane                                        decision-making and, 128
sizing, 23                                 definition of, 17
undocking or re-docking, 22                Form_Current(), 97–98
parameters, 161                             Form_Load(), 219
parentheses [( )]                           Function, 19–20, 36–38, 56
checking, 275–276                          parentheses for ending, 37, 42, 353
for ending procedure, 37, 42, 353          passing data to, 55–57
procedure (continued)                       IsLoaded property of form, 358
PaymentMethod_AfterUpdate()                Item Data(x), 203
listing, 94                             Limit to List, 203
Private, 19, 20                            List Rows, 203
Public, 19, 37                             List Width, 203
running from Immediate window, 281         Name, 145–146, 152, 203
saving, 95                                 on open form, referring to, 359
Send button, 323                           Row Source, 201, 203, 205
Sub, 18–19, 20                             Row Source Type, 203
tying to event, 17                         Special Effects, 103–104
procedure-level declaration, 61             Unique Records, 214
program, opening, 321                       Unique Values, 214
programming language                        Value, 145–146, 203
description of, 11                         value of, changing, 80
high-level, 268–269                        value of, getting, 79–80
Project Explorer window (Visual Basic       Visible property of control, 79–80,
editor), 23–24, 25                         95–96
Project icon, 78                           Property icon, 77
Project/Library list (Object Browser),     Public function, 19, 37
31–32
Properties sheet
All tab, 185, 203
•Q•
Data tab, 189                             QBE (Query-by-Example) grid, 119, 120,
Events tab, 88, 96                            121, 133–134
Format tab, 99                            query
viewing, 73–74                             in Access compared to in VBA, 120
Properties window (Visual Basic editor),    action, 121–123, 125–126, 136
24–25                                   append, 122, 131–132
property                                    to append record to table, 133–134
BackColor, 100                             Customer Lookup, 230–231, 234
BackStyle, 101                             Datasheet view of, 138
BOF, 145                                   Delete, 122, 134–137
BorderColor, 100                           description of, 10
BorderStyle, 101                           importing from external database,
Bound Column, 230, 232–233                    307–309
Column Count, 232                          Make-Table, 122, 128–129
of control, referring to, 359              select, 117–118, 121–123, 126, 137–140
Count property of collection, 79,          as SQL statement, 117–118, 120–121
147–148                                 Unique Cities, 237–238
Default Value, 203                         Update, 122, 123, 134–136
description of, 75–77                      viewing SQL view of, 213
EOF, 145                                  Query Design, 123
ForeColor, 100, 103                       Query menu (Access), 122
Index    385
query names, making list of, 210              referring
Query Properties sheet, 214                     to field in recordset, 145–146
Query➪Append Query, 131, 133                    to field name, 154–155
Query-by-Example grid. See QBE grid             to object, 155–156
Query➪Run, 123, 252                             to open form, 358–359
Query➪Update Query, 251                         to worksheet cell from VBA, 341–343
question, asking of user, 362–363             report
question mark (?), 79                           class module, opening, 14–15
quick find tool, combo box as, 233–236          creating, 159
Quick Info tip, 38, 39, 84                      description of, 10
quotation marks ("), 58, 83, 353                printing, 363–364
for printing checks, 256–257
•R•                                           report names, making list of, 211–213
Requery method, 224–226
read-only control, 96                         requerying control with every new
record. See also recordset                         record, 238–239
action query on one, 136                     responding to form event, 96–99
appending to table, 131–134                  Restore Window button
changing or deleting, 134–136                     (Code window), 27
opening form to enter new, 226–227           Resume statement, 288
requerying control with every new,           returning value
238–239                                    custom procedure and, 56
recording macro, 347–348                        from function, 245
recordset                                     RGB values, 100–101
closing, 146                                 right-click, detecting, 198–199
connection, defining, 140–141                Row Source property
copying to Excel worksheet, 344–347            combo box and list box, 201, 203
creating from external table, 309–310          Field List combo box, 205
defining, 141                                Row Source Type property (combo box
filling with data, 142–143                        and list box), 203
managing, 144–145                            running
as object, 140                                 action query, 122–123
referring to fields in, 145–146                Excel macro from Access, 347–348
select query and, 126, 137–140               running code
SkipLabels procedure, 166–170                  when form opens, 219–220
of table, creating, 303                        when user makes a choice, 220–223
re-docking item, 22                           runtime, 269
re-executing statement in Immediate           runtime error
window, 79                                 description of, 270–271, 285–286
reference, setting to object library, 29–30     error handler, writing, 290–294
References dialog box, 29–30                    responding to, 286–287
referencing external object library,            trapping, 287–290
321–322
F8 (Step Into), 283
•S•                                         Shift+F8 (Step Over), 283
SalesTax() function example, 244–247        VBA editor, 369
saving                                     showing
document as Word template, 327–328         control, 95–96
procedure, 95                              custom message, 361–362
scope, 60                                   Immediate window, 25
Screen.ActiveControl, 155                   properties, 75–76
Search box (Object Browser), 31             toolbar, 21
Search Results pane (Object Browser), 33   Simpson, Alan, Access 2003 All-in-One
searching object library, 32–33                 Desk Reference For Dummies, 9
Select Case block                          Single data type, 54, 62
decision-making and, 68–69                sizing
message box buttons and, 182               editor window, 282
select query                                pane, 23
example of, 117–118                       skills, as learned, 356
overview of, 121                          SkipLabels procedure
recordset and, 126, 137–140                calling procedure from event, 170–172
selecting code to copy, 46                  combo box, creating for, 189–195
self-referential combo box, creating,       controls, adding to dialog box, 186–188
237–239                                copying label report, 165
Send button procedure, 323                  declaring variables, 165
sending e-mail via Outlook, 322–326         getting report record source, 165–166
SendObject action, 314                      listing, 162–164
server, definition of, 297                  overview of, 159–161
setting                                     passing data to, 164
breakpoint in code, 280–284                recordset, creating and filling, 166–170
reference to object library, 29–30         spin box control, creating for, 195–198
Shift+F8 (Step Over), 283                   table for storing dialog box settings for,
shortcut keys                                   183–184
Alt+F11 (switch between Access and        skipping over used mailing labels. See
Visual Basic editor), 16, 354              SkipLabels procedure
Alt+Q (close Visual Basic editor), 15     slowing down code, 280–284
Code and Immediate window, 367–369        source code, 269, 270
Ctrl+C (Copy), 46                         Special Effects property, 103–104
Ctrl+F8 (Run to Cursor), 283              spin box control, creating, 195–198
Ctrl+Shift+F8 (Step Out), 283, 284        Split bar (Object Browser), 31
Ctrl+V (Paste), 46                        splitting database into two files, 297–303
for debugging, 370                        SQL INSERT INTO statement, 132–133
F1 (Help), 39, 356                        SQL (Structured Query Language)
F2 (open Object Browser), 30               Access query and, 120–121
F4 (Properties sheet), 74                  getting into VBA, 123–125
Index    387
overview of, 117–118                      lifetime of variable and, 60–61
statement syntax, 118–119                 in variables and constants, 57–58
storing statement in variable, 126–128   StrConv() function, 247, 249
SQL View, 118, 123                        string
square brackets ([])                       concatenating, 157
in Access, 91                             literal, 83, 157–158
as representing argument, 40, 41          Value List, 207–203
as representing name, 154–155            String data type, 54, 62
typing, 353                              string expression, 42
stacking editor windows, 282              string variable, creating, 58
standard module (Access)                  Structured Query Language. See SQL
creating, 35–36                          style of text, changing, 103
custom function and, 243                 Sub procedure
importing, 47–48                          arguments and, 56
overview of, 12–13                        creating, 36–38
standard OLE Automation object library     overview of, 18–19, 20
(stdole), 32                           testing, 150–151
statement                                  testing from Immediate window, 281
CREATE TABLE (SQL), 129–130              switching between Access and Visual
Debug.Print, 150, 277–280                     Basic editor, 15–16
definition of, 17                        syntax. See also code; listings
Dim, 152                                  ADO recordset, creating, 142–143
DoCmd.RunSQL, 124, 125, 146               for argument, 55
If...Else...End If, 93                    assigning object to object variable, 152
If...End, 67–68                           for built-in functions, accessing, 242
If...Then...End If, 197                   calling custom VBA function from
On Error, 288, 291                            event, 172
order of arguments in, 45                 calling procedure from event, 171
re-executing in Immediate window, 79      color of property, changing, 100
Resume, 288                               CREATE TABLE statement (SQL), 129–130
SQL INSERT INTO, 132–133                  declaring object variable, 321
SQL, storing in variable, 126–128         definition of, 31, 38–39
With...End With, 104–105                  Do...Loop block, 63
stdole (standard OLE Automation object     enabling or disabling control, 90
library), 32                           focus, setting to control, 92
step mode, 281, 283–284                    Help page and, 40
storing                                    identifier, 81
dialog box settings, 183–184              If...End statement, 67
SQL statement in variable, 126–128        importance of, 78–79, 351–352
storing data                               MsgBox() keyword, 176, 272
in arrays, 58–60                          OpenForm method of DoCmd object, 82
in combo box, 232–233                     opening program, 321
syntax (continued)                             function, 246–247
punctuation marks in, 353                     function from Immediate window, 281
quotation marks (") in, 83                    macro, 312, 348
recordset, creating, 138–139                  Print Preview, using, 170
for referring to object property, 75, 79      Sub procedure, 150–151
Requery method, 224                           Sub procedure from Immediate
running macro in open workbook, 348               window, 281
Select Case block, 68                         while writing code, 277–280
SQL INSERT INTO statement, 132–133          text
SQL statement, 118–119                        font or style of, controlling, 103
Visible property of control, 80               literal, 83, 157–158
system modal, 178                            text options, listing
system table, 209                              form or report names, 211–213
table and query names, 208–211
•T•                                            overview of, 207–208
title argument, 44
table. See also recordset                    Toggle Folders button (Project Explorer
action query and, 121                             window), 23
action query on one record, 136             Toggle➪Breakpoint, 281, 284
adding related record to another,           toolbar
109–111                                   Debug, 284
appending record to, 131–133                  Project Explorer, 23
closing and deleting through VBA,           Tools➪Database Utilities➪Database
130–131                                       Utilities, 299
creating recordset from external,           Tools➪Database Utilities➪Linked Table
309–310                                       Manager, 298
description of, 10                          Tools➪Macro➪Convert Macros to Visual
linked, converting to local, 300                  Basic, 313
linking to external, 308–309                Tools➪Macro➪Macros, 348
making list of names of, 208–211            Tools➪Macro➪Record New Macro, 347
new, creating from existing, 128–129        Tools➪References, 29, 318
new, empty, creating from VBA, 129–130      TransferDatabase action, 314
record, changing or deleting, 134–136       TransferDatabase method of DoCmd
select query and, 121                             object, 304–307
for storing dialog box settings, 183–184    TransferSpreadsheet action, 314
Table/Query field values, listing, 213–217   TransferText action, 314
tag, 61–62                                   transparency of background or
taskbar button, 15, 16                             border, 101
testing                                      trapping runtime error, 287–290
on copy of data, 251                        two-dimensional array, 59–60
expression in Immediate window,             typing code, 27–28
26, 42–43
Index   389
variable
•U•                                     checking with Debug.Print
UCase() function, 278                       expression, 277–280
unbound control, adding to form,        declaring, 165
187, 202–203                        lifetime of, 60–61
undocking                               naming conventions for, 61–62
Code window, 27                        storing color in, 102
item, 22                               storing data in, 57–58
Unique Cities query, 237–238            storing SQL statement in, 126–128
Unique Records property, 214           Variant data type, 54, 62
Unique Values property, 214            VBA editor. See Visual Basic editor
unlocking control, 96                  VBA Keyword or data icon, 78
Update query, 122, 123, 134–136        VBA (Visual Basic for Applications)
updating                                Access and, 1
combo box from table, 222              consistency of language, 1, 9
combo box of report names, 221         description of, 9, 11–12
combo box or list box, 224–226, 361    Help system, 354
control on separate form, 228–229      learning Access before using, 351
user, asking question of, 362–363       object library, 32
User Defined Type icon, 78             View argument of OpenForm method of
DoCmd object, 107
View Code button (Project Explorer
•V•                                         window), 24
value                                  View Object button (Project Explorer
for argument, 42                           window), 24
declaring and assigning, 60           View➪Immediate Window, 22
function and, 242                     viewing
hidden, in combo box and list box,     existing code, 153–154
229–233                             form events, 96–97
parameters and, 161                    object model, 72, 74
of property, getting and changing,     Properties sheet, 73–74
79–80                               SQL view of query, 213
returned by MsgBox() keyword          View➪Locals Window, 282
buttons, 180                       View➪Object Browser, 30
returning, 56                         View➪Properties, 74
returning from function, 245          View➪Toolbars, 21
RGB, 100–101                          Visible property of control, 79–80,
Value List string, 207–213                  95–96
Value property                         Visual Basic editor. See also Code
combo box and list box, 203                window; Immediate window; Object
recordset, 145–146                         Browser tool
closing, 15–16
description of, 15
Visual Basic editor (continued)          what-if scenario, 336
item, undocking and re-docking, 22      WhereCondition argument of OpenForm
moving and sizing windows in, 282           method of DoCmd object, 108
Project Explorer window, 23–24, 25      While...Wend block, 64
Properties window, 24–25                WindowMode argument of OpenForm
shortcut keys, 369                          method of DoCmd object, 108
sizing pane, 23                         With...End With statement, 104–105
toolbar, hiding or showing, 21          Word (Microsoft)
View menu, 22                            form, creating, 329–330
Visual Basic for Applications. See VBA    merge code, writing, 330–335
sending data to, 326
•W•                                       template, creating, 327–328
warning message, hiding, 125–126
Web sites
•X•
for book, 5                             XOR operator, 67
Help file, custom, creating, 180
Microsoft, 354
more done and ha ve more fun
The easy way to get
Also available:
Accounting For Dummies       Resumes For Dummies
(0-7645-5314-3)              (0-7645-5471-9)
Business Plans Kit For       Small Business Kit For
Dummies                      Dummies
(0-7645-5365-8)              (0-7645-5093-4)
Managing For Dummies         Starting an eBay Business
(1-5688-4858-7)              For Dummies
Mutual Funds For Dummies     (0-7645-1547-0)
(0-7645-5329-1)              Taxes For Dummies 2003
0-7645-2431-3      0-7645-5331-3      0-7645-5307-0         QuickBooks All-in-One Desk   (0-7645-5475-1)
Reference For Dummies
(0-7645-1963-8)
HOME, GARDEN, FOOD & WINE
Also available:
Bartending For Dummies       Grilling For Dummies
(0-7645-5051-9)              (0-7645-5076-4)
Christmas Cooking For        Home Maintenance For
Dummies                      Dummies
(0-7645-5407-7)              (0-7645-5215-5)
Cookies For Dummies          Slow Cookers For Dummies
(0-7645-5390-9)              (0-7645-5240-6)
Diabetes Cookbook For        Wine For Dummies
Dummies                      (0-7645-5114-0)
0-7645-5295-3      0-7645-5130-2      0-7645-5250-3         (0-7645-5230-9)
FITNESS, SPORTS, HOBBIES & PETS
Also available:
Cats For Dummies             Pilates For Dummies
(0-7645-5275-9)              (0-7645-5397-6)
Chess For Dummies            Power Yoga For Dummies
(0-7645-5003-9)              (0-7645-5342-9)
Dog Training For Dummies     Puppies For Dummies
(0-7645-5286-4)              (0-7645-5255-4)
Labrador Retrievers For      Quilting For Dummies
Dummies                      (0-7645-5118-3)
(0-7645-5281-3)              Rock Guitar For Dummies
Martial Arts For Dummies     (0-7645-5356-9)
0-7645-5167-1      0-7645-5146-9      0-7645-5106-X         (0-7645-5358-5)              Weight Training For Dummies
Piano For Dummies            (0-7645-5168-X)
(0-7645-5105-1)
world of resources to help you grow
A
Also available:
America’s National Parks For   France For Dummies
Dummies                        (0-7645-6292-4)
(0-7645-6204-5)                Las Vegas For Dummies
Caribbean For Dummies          (0-7645-5448-4)
(0-7645-5445-X)                London For Dummies
Cruise Vacations For           (0-7645-5416-6)
Dummies 2003                   Mexico’s Beach Resorts
(0-7645-5459-X)                For Dummies
Europe For Dummies             (0-7645-6262-2)
0-7645-5453-0      0-7645-5438-7      0-7645-5444-1      (0-7645-5456-5)                Paris For Dummies
Ireland For Dummies            (0-7645-5494-8)
(0-7645-6199-5)                RV Vacations For Dummies
EDUCATION & TEST PREPARATION                                                               (0-7645-5443-3)
Also available:
The ACT For Dummies            Italian For Dummies
(0-7645-5210-4)                (0-7645-5196-5)
Chemistry For Dummies          Research Papers For Dummies
(0-7645-5430-1)                (0-7645-5426-3)
English Grammar For            SAT I For Dummies
Dummies                        (0-7645-5472-7)
(0-7645-5322-4)                U.S. History For Dummies
French For Dummies             (0-7645-5249-X)
(0-7645-5193-0)                World History For Dummies
0-7645-5194-9      0-7645-5325-9      0-7645-5249-X      GMAT For Dummies               (0-7645-5242-2)
(0-7645-5251-1)
Inglés Para Dummies
HEALTH, SELF-HELP & SPIRITUALITY                            (0-7645-5427-1)
Also available:
The Bible For Dummies          Menopause For Dummies
(0-7645-5296-1)                (0-7645-5458-1)
Controlling Cholesterol        Nutrition For Dummies
For Dummies                    (0-7645-5180-9)
(0-7645-5440-9)                Potty Training For Dummies
Dating For Dummies             (0-7645-5417-4)
(0-7645-5072-1)                Pregnancy For Dummies
Dieting For Dummies            (0-7645-5074-8)
(0-7645-5126-4)                Rekindling Romance For
0-7645-5154-X      0-7645-5302-X      0-7645-5418-2      High Blood Pressure For        Dummies
Dummies                        (0-7645-5303-8)
(0-7645-5424-7)                Religion For Dummies
Judaism For Dummies            (0-7645-5264-3)
(0-7645-5299-6)
Plain-English solutions
for everyday challenges
Also available:
Excel 2002 All-in-One Desk   Upgrading & Fixing PCs For
Reference For Dummies        Dummies
(0-7645-1794-5)              (0-7645-1665-5)
Office XP 9-in-1 Desk        Windows XP For Dummies
Reference For Dummies        (0-7645-0893-8)
(0-7645-0819-9)              Windows XP For Dummies
PCs All-in-One Desk          Quick Reference
Reference For Dummies        (0-7645-0897-0)
(0-7645-0791-5)              Word 2002 For Dummies
0-7645-0838-5       0-7645-1663-9      0-7645-1548-9       Troubleshooting Your PC      (0-7645-0839-3)
For Dummies
(0-7645-1669-8)
Also available:
CD and DVD Recording         Internet For Dummies
For Dummies                  Quick Reference
(0-7645-1627-2)              (0-7645-1645-0)
Digital Photography          Internet Privacy For Dummies
All-in-One Desk Reference    (0-7645-0846-6)
For Dummies                  Paint Shop Pro For Dummies
(0-7645-1800-3)              (0-7645-2440-2)
eBay For Dummies             Photo Retouching &
(0-7645-1642-6)              Restoration For Dummies
0-7645-0894-6       0-7645-1642-6      0-7645-1664-7       Genealogy Online For         (0-7645-1662-0)
Dummies                      Photoshop Elements For
(0-7645-0807-5)              Dummies
Internet All-in-One Desk     (0-7645-1675-2)
Reference For Dummies        Scanners For Dummies
(0-7645-1659-0)              (0-7645-0783-4)
Get smart! Visit www.dummies.com
• Find listings of even more Dummies titles
• Browse online articles, excerpts, and how-to’s
• Sign up for daily or weekly e-mail tips
• Check out Dummies fitness videos and other products
• Order from our online bookstore
™
r horizons and realize your potential
u
Helping you expand yo
Also available:
Adobe Acrobat 5 PDF             HTML 4 For Dummies
For Dummies                     (0-7645-0723-0)
(0-7645-1652-3)                 Illustrator 10 For Dummies
ASP.NET For Dummies             (0-7645-3636-2)
(0-7645-0866-0)                 PowerPoint 2002 For
ColdFusion MX For Dummies       Dummies
(0-7645-1672-8)                 (0-7645-0817-2)
Dreamweaver MX For              Web Design For Dummies
Dummies                         (0-7645-0823-7)
0-7645-1651-5      0-7645-1643-4      0-7645-0895-4         (0-7645-1630-2)
FrontPage 2002 For Dummies
(0-7645-0821-0)
Also available:
Access 2002 For Dummies         JavaScript For Dummies
(0-7645-0818-0)                 (0-7645-0633-1
Beginning Programming           Oracle9i For Dummies
For Dummies                     (0-7645-0880-6)
(0-7645-0835-0)                 Perl For Dummies
Crystal Reports 9 For           (0-7645-0776-1)
Dummies                         PHP and MySQL For
(0-7645-1641-8)                 Dummies
Java & XML For Dummies           (0-7645-1650-7)
0-7645-0746-X      0-7645-1626-4      0-7645-1657-4         (0-7645-1658-2)                 SQL For Dummies
Java 2 For Dummies              (0-7645-0737-0)
(0-7645-0765-6)                 Visual Basic .NET For
Dummies
LINUX, NETWORKING & CERTIFICATION                                                              (0-7645-0867-9)
Also available:
A+ Certification For Dummies    Firewalls For Dummies
(0-7645-0812-1)                 (0-7645-0884-9)
CCNP All-in-One Certification   Home Networking For
For Dummies                     Dummies
(0-7645-1648-5)                 (0-7645-0857-1)
Cisco Networking For            Red Hat Linux All-in-One
Dummies                         Desk Reference For Dummies
(0-7645-1668-X)                 (0-7645-2442-9)
CISSP For Dummies               UNIX For Dummies
0-7645-1545-4      0-7645-1760-0      0-7645-0772-9         (0-7645-1670-1)                 (0-7645-0419-3)
CIW Foundations For
Dummies
(0-7645-1635-3)