M
m scope prefix, 1135
macro action arguments, 318-320
macro actions, 316-317
copying actions, 331
deleting actions, 330
deleting new actions, 329
inserting new actions, 329
moving actions, 330-331
OnError, 334
macro arguments, 316
macro conditions, 316, 323-324
Macro Design window, 316
running macros, 325
macros
AutoExec macro, creating, 341-342
capabilities of compared to VBA programming, 345-347
converting to VBA code, 339-341
DoCmd object, 342
documenting
adding comments, 336-337
Database Documenter, 1078
embedded macros, 315
creating, 332-333
executing
DoCmd object, 374
from ribbons, 946-947
mcrPersonalInfo, 328
modifying existing macros, 329
copying macro actions, 331
deleting macro actions, 330
deleting new macro actions, 329
inserting new macro actions, 329
moving macro actions, 330-331
versus modules, 20
new features, 334-335
from Macro Design window, 325
from Macros group of the Navigation Pane, 326
stepping through, 338
testing, 337-338
triggering, from forms or report events, 326-327
Trust Center, 1116-1117
when to use, 339
mail merge documents (Word), 966, 968
MailError, 780-781
mailing labels, reports with labels, 273
maintenance, splitting tables from other objects, 836
maintenance activities, applications development, 28
Make Table queries
creating, 545-547
performance considerations, 554
MakeItBold Subroutine, 812
manipulating report sections, 501
many-to-many relationships, 123-124
mapping dBASE data types, 873
MaxBufferSize settings (Windows registry), 806
mcr prefix, 1135
mcrPersonalInfo, 328
MDE files, 829
Me property, 416
Memo field type, 84
memory
Declare statements, 987
freeing, 412
optimization, 802-803
usage considerations, declaring variables, 674
virtual, optimizing, 803-805
menu add-ins, 1045-1046
Menu Add-ins section (registry), library references, 1014
Menu Bar, 288
forms, 235
message boxes, 460-462
MsgBox function, 460-462
preventing rebooting during long processes, 801
Add
Collection object, 626-627
custom collections, 649
AddFromFile, 1021
adding to Class modules, 639
AddNew method, data access class, 664
Application object, 417-418
AutoFill, 959
AutoFilter, 982
automation objects, 954
BeginTrans, transaction processing, 929-930
BrokenReferences (Application object), 1102
CommitTrans, transaction processing, 929-930
CompactDatabase method (JetEngine object), 1096-1098
CompactRepair, 417
CompactRepair method (Application object), 1098-1099
compared to properties, 408-409
ConvertAccessProject, 418
ConvertAccessProject (Application object), 1100-1101
CopyFromRecordset, 982-983
Custom, adding to classes, 651-652
custom methods, creating, 489
Delete
Catalog object, 871
data access class, 665
Edit method, data access class, 664-665
ErrorProcess method, custom error handler class, 791-793
executing, 408-409
Find method (ADO recordsets), 696-699
Item method, Collection object, 627
MoveFirst, 688
MoveLast, 688
data access class, 659-661
data access class, 661-662
objects, object library, 951
OpenDatabase, 864-865
OpenForm, DoCmd object, 375
Raise (Err object), 767-769
generating errors, 767-768
user-defined errors, 768-769
record-movement methods (ADO recordsets), 688-689
Remove method, 650
Collection object, 627
Requery, refreshing recordset data, 702
Resync, refreshing recordset data, 702
Rollback, transaction processing, 929-930
Save method, data access class, 663-664
Scatter method, data access class, 658-659
Supports, Recordset objects, 687
TransferDatabase
arguments, 852
importing external data, 852-853
TransferSpreadsheet
arguments, 854
importing external data, 853-854
TransferText, importing external data, 853
Microsoft Office Access button, new features, 36
Microsoft Word. See Word
Mid function, 377
migration
importing tables, 848
to client/server environment, 845
Upsizing Wizard, 843
Min Max Buttons property, reports, 286
mini toolbar, 49-50
Modal, 288
modal forms, 191
Modal property
builder forms, 1031
forms, 235
modifying
existing macros, 329
copying macro actions, 331
deleting macro actions, 330
deleting new macro actions, 329
inserting new macro actions, 329
moving macro actions, 330-331
Trusted Locations, 1120
Module Design window, modules (viewing design), 22
Module property, 416
Module window, executing procedures, 374-375
module-level variables, see private variables, 348
modules
basUtils, General Declarations section, 998-1003
bookmarks, adding, 394
CBF, 347
class, 347
Class, declaring procedures, 355
Class compared to Standard, 357-358
Class modules. See Class modules
code modules, exporting, 630-631
compiling, 828
Complete Word feature, 386
constants, intrinsic, 382-383
Definition feature, 387
documenting (Database Documenter), 1078
event procedures, 347, 350-352
features, 22
Form and Report Class, 347
form modules, exporting, 630-631
function libraries, 22
functions, 348-349
creating, 352-354
General Declarations sections, 348
List Constants feature, 385
List Properties and Methods feature, 384
versus macros, 20
organizing, 829
Parameter Info feature, 386
procedures
private procedures, 356
public procedures, 355
scope precedence, 357
static procedures, 358
Quick Info feature, 385
standard, 347
statements
Option Explicit, 349
Option Explicit, 351
subroutines, 347-349
creating, 352-354
tools, 384-386
variables, declaring, 348
viewing (Module Design window), 22
Modules collection, 420
Month() function, 149
MonthName function, 379
morphing
combo boxes to list boxes, 228
text boxes to combo boxes, 227-228
morphing controls, 227-228
mouse
moving form objects, 200
reports, selecting objects, 279
selecting form objects, 199-200
sizing objects, 202
mouse actions, forms (Form event sequence), 442
MouseDown event
controls, 448
forms, 439
MouseMove event
controls, 448
forms, 439
MouseUp event
controls, 448
forms, 439
Moveable property, 287
forms, 232
MoveFirst method, recordsets, 688
MoveLast method, recordsets, 688
MoveLayout property, reports, 502
MoveNext method, 721
data access class, 659-661
recordsets, 688
MovePrevious method, 721
data access class, 661-662
recordsets, 688
moving
controls within form layouts, 210-211
form layouts, 210-211
form objects, 200
macro actions, 330-331
report objects, 280
moving between forms, Form event sequence, 442
MS Office 97, VBA, 345
MsgBox function, 460-462
multifact crosstab reports, creating, 533-536
multipage forms, creating, 453-455
multiple class instances (Class modules), creating, 642-643
Multiselect property, list boxes, 475-476
multitable queries
creating, 151-153
pitfalls, 153-155
row fix-up feature, 155-157
multiuser applications, 910-911
multivalued fields
creating, 65-69
effect on queries, 69-71
My Documents folder, 1095
MyCustomForm function, 1045
N
Name property, 292
naming
constants, 382
controls, conflicts, 292
databases, rules, 79
fields, 81
objects, PDF:1131-1135
DAOs, PDF:1133-1134
Database window objects, PDF:1135
Jet object/collection prefixes, PDF:1133-1134
object tag prefixes, PDF:1132-1133
Reddick naming convention, 23
scope/lifetime prefixes, PDF:1134
variable data type tags, PDF:1133
queries, 144
tables, conventions, 81
changing, 390-391
Jet object/collection prefixes, PDF:1134
Private, 362
Public, 363
scope/lifetime prefixes, PDF:1134
variable data type tags, PDF:1133
naming conventions, constants, 603-604
naming prefixes, action queries, 541
naming standards, avoiding bugs, 728
natural keys, 116
navigating
applications, forms, 190
forms, setting properties, 232
Navigation Buttons property, forms, 232
Navigation Pane
new features, 42-43
running macros from, 326
tips and tricks, 56-58
adding custom groups to categories, 59-60
adding objects to custom groups, 60
creating custom groups containing objects found in existing groups, 62
hiding objects in parent groups, 63-64
hiding Unassigned Objects group, 61
restoring hidden objects, 64-65
nested queries, calculated fields, 554
client/server applications, suitability of Access, 10
file servers versus client/servers, performance considerations, 9
networks
applications, designing, 909
data locking, 913
linked tables, accessing, 913
servers, installing applications, 910
workstations, installing applications, 911
New command (Edit menu), 1034
new databases, creating, 75-76
from scratch, 78-79
with templates, 76-78
new features
forms, 51
macros, 334-335
mini toolbar, 49-50
Navigation Pane, 42-43
Quick Access toolbar, customizing, 38-41
reports, 51
ribbons, 30
command tabs, 30-33
contextual command tabs, 34
gallery, 34
Microsoft Office Access button, 36
Quick Access toolbar, 34-36
status bar, 47-49
tabbed documents, 44-46
displaying overlapping windows, 46-47
undoing/redoing, 89
user interfaces, 28-30
new features (Access 2003), 51
New menu commands
DWORD Value, 1034
Key, 1034
String Value, 1034
New Object dialog box, 1045
New Query dialog box, 138
NextRecord property, reports, 502
No Data events, 831
NO INDEX keyword (ANSI-92 extensions), 591
NoData event, reports, 494-495
non-equi joins (SQL), 572
normal forms, 116
first, 118
second, 119
third, 119-120
normalization, 115-116
application development, rules, 24-27
denormalization, 120
first normal form, 118
second normal form, 119
third normal form, 119-120
Not (query criteria operator), 147
not equal to (*), query criteria operator, 147
Not function, toggling Boolean expressions, 815
Nothing value, object variables, 412
NotInList event
combo boxes, 472-473
controls, 444-445
Null (query criteria operator), 147
null entries, compared to zero-length, 95
Null values
avoiding in strings, 624
converting to zero, 167
examples, 632-633
primary keys, 122
queries, 579-581
variables, 359
Variant variables, 619-625
Number field type, 84
NumberOfBytesFree, 1008
NumberOfBytesFree function, 1008
Numeral Shapes, 291
numeric data, summarizing (Totals queries), 161-165
O
obj data-type prefix, 1133
object, Connection object, 929-930
information available in, 406
intrinsic constants, 382
pasting code templates into procedures, 406
Project/Library drop-down list, 406
running, 405
viewing constants, 604
Object Browser command (View menu), 405
Object Browser dialog box, 405
Object Definition window (Database Documenter), 1082
object dependencies, viewing, 479-480
Object Dependencies feature, 110
invoking, 1086-1088
object libraries, 951
object models, 950
object orientation, 637-638
Object Properties dialog box, 1075
object types, 950
object variables, 410
compared to regular variables, 410-411
generic compared to specific, 411
Nothing value, 412
referencing applications, 950-952
Access Object Model
AllForms collection, 420
AllMacros collection, 421
AllModules collection, 421
AllQueries collection, 423
AllReports collection, 421
AllTables collection, 422
Application object methods, 417-418
Application object properties, 417
CodeData object, 423
CodeProject object, 423
CurrentData object, 422-423
CurrentProject object, 420-421
DoCmd object, 423-424
Forms collection, 418-419
Modules collection, 420
Reports collection, 419
Screen object, 423
special properties that refer to objects, 415-416
Application, CompactRepair method, 1098-1099
automation, 950
automation objects, 953-954
bound OLE objects, 466-467
Catalog object, Delete method, 871
CodeData object, 423
CodeProject object, 423
Collection object, 626
Add method, 626-627
Item method, 627
Remove method, 627
command buttons, enabling/disabling, 426-427
Command object, ADO object model, 678-679
compared to collections, 412
compilation, 629
Connection object, ADO object model, 672-675
CurrentData object, 422-423
CurrentProject object, 420-421
custom collections, 648-651
DAOs, naming conventions, 1133-1134
database objects, splitting, 835-836
Database window objects, naming conventions, 1135
declaring disambiguation, 672
dependencies, displaying (Object Dependency feature), 1086-1088
DoCmd, 423-424
adding report sections at runtime, 501
examples, 632-633
executing macros, 374
importing external data, 852-854
OpenForm method, 375
Err, 766-767
Err object
properties, 766
Raise method, 767-769
event procedures, 350
creating, 352
events, 404
Excel automation objects, closing, 960-961
FileDialog, 463-464
form objects
aligning, 200-202
moving, 200
selecting, 199-200
sizing, 202-203
spacing, 203-204
tab order, changing, 204-205
hidden objects, restoring, 64-65
hiding in parent groups, 63-64
instantiating (Class modules), 640
executing, 408-409
object library, 951
naming (Reddick naming convention), 23
naming conventions, PDF:1131-1135
Jet object/collection prefixes, PDF:1133-1134
object tag prefixes, PDF:1132-1133
scope/lifetime prefixes, PDF:1134
variable data type tags, PDF:1133
OLE objects
converting to images, 830
inserting in reports, 283
passing to subroutines and functions, 413-414
Printer, 503-504
procedures, calling, 354
default, 410
modifying, 408-409
object library, 951
setting for single objects, 412
viewing, 402
Recordset object
ADO object model, 676-678
consistent compared to inconsistent updates, 685-686
CursorType parameter, 680-682
LockType parameter, 682-683
Options parameter, 684-685
selecting location, 686-687
Supports method, 687
referencing, 407
shortcuts, 410
registering, 951
report objects
aligning, 281
moving, 280
selecting, 279-280
sizing, 281
Snap to Grid feature, 281
spacing, 282
reports, moving, 281
Screen object, 423
Table object, Properties collection, 860
unbound OLE objects, 467
viewing object dependencies, 109-110
With...End With construct, 412
ocx prefix, 1132
ODBC, client/servers, 922
OLE DB, 922
OLE Object field type, 86
OLE objects
bound OLE objects, 466-467
In-Place activation, 466
converting to images, 830
inserting in reports, 283
unbound OLE objects, 467
ole prefix, 1132
On Error Goto 0 statement, 759-760
On Error Goto statement, 758-759
On Error Resume Next statement, 760
On Error routine, generic error handlers, 770-771
On Error statements, 758
On Error Goto, 758-759
On Error Goto 0, 759-760
On Error Resume Next, 760
one-to-many forms, creating, 246-248
one-to-many queries, creating one-to-many reports, 296
one-to-many relationships, 16, 122
join lines, 124
one-to-many reports, creating, 294-296, 299
one-to-many queries, 296
Report Wizard, 294-296
Subreport Wizard, 298-300
one-to-one relationships, 123
join lines, 124
OnError macro action, 334
Open dialog box, 1039
Open event
forms, 435
Open keyword, 628
Open method, ConnectionString argument, 864
OpenArgs property, forms, 468
OpenDatabase method, 864-865
OpenForm method, DoCmd object, 375
opening
Access forms and reports from SharePoint sites, 884, 896-899
external tables, 848-849, 864-865
connection information, providing, 864
OpenDatabase method, 864-865
files, supported file formats, 850
forms, Form event sequence, 441
windows, designing forms, 197
operators, query criteria, 146-147
opt prefix, PDF:1132
optimization, 802
code
ACCDE files, 829
bookmarks, 826-827
Boolean expressions, 815
built-in collections, 816-817
compiling modules, 828
constants, 826
Declare statements, 821
Dim statements, 821
dynamic arrays, 826
inline code, 814
Len function, 818
Me keyword, 825
object references, 821
object variables, 827
organizing modules, 829
specific object variables, 812-813
stored queries, 827-828
true/false evaluations, 819-820
unused code, 821
variant variables, eliminating, 811
With...End With construct, 823-824
coding changes, 811
data structures, 808-809
defined, 801
form-optimization techniques, 829-831
hardware, 802
Compact utility, 803
defragmenting hard disks, 803
disk compression, 804
RAM, 802-803
virtual memory, 803-805
local execution and, 804
Performance Analyzer, 807
queries, 810
report-optimization techniques, 831
software, Windows 95/98, 806
splitting tables from other objects, 836
Windows 2000 Terminal Services, 804
Windows registry settings, 806
option buttons, adding to forms, 224
Option Explicit statement
avoiding bugs, 728
modules, 349-351
Option Group Wizard, 225-226
option groups
captions, 226
forms, adding, 224-226
optional parameters, 613-615
Options parameter, ADO recordset types, 684-685
Or (query criteria operator), 147
Order, of report events, 496
ORDER BY clause, SQL, 569
Order By On Load property, 288
Order By property, 288
forms, 234
queries, 172
order of events, report section events, 500
OrderBy property, 515-516
OrderByOn property, 515-516
ordering query results, 144-145
Orientation property, 287
forms, 233
orphan records, 131
Other properties
controls, 292
form controls, 240-242
outer joins
left outer joins, 563
queries, 562-564
right outer joins, 563
Outlook, controlling from Access, 974-976
Output All Fields property, queries, 171
overlapping windows, displaying, 46-47
P
packages, signed packages
creating, 1112-1113
extracting, 1113-1114
packaging Access databases, 1110-1111
signed packages, 1112-1114
Page Break tool, 293
Page event, reports, 495
Page Footer Format event, reports, 532
Page Footer property, reports, 286
Page Footers, reports, 274
Page Header Format event, 533
reports, 527
Page Header property, reports, 286
Page Headers, reports, 274
printing first and last page entries, 530, 532-533
Page locking, 913
pal prefix, 1132
Palette Source property, 287
parameter arrays, 617-618
Parameter Info feature, modules, 386
Parameter queries, building, 172-175
parameter queries (ADO), 701-702
parameter query values, passing from forms, 588-589
parameters, 991. See also arguments
Crosstab queries, 562
CursorType, ADO recordset types, 680-682
functions, 373-374
LinkToDBase subroutine, 862
LockType, ADO recordset types, 682-683
named, 375
named parameters, 616
optional parameters, 613-615
Options, ADO recordset types, 684-685
passing, 373-374
by reference, 987
passing arrays as, 609-610
passing by reference vs. passing by value, 610-613
string parameters, passing, 987, 989
subroutines, 373-374
Parameters dialog box, 588
Parent property, 416
classes, adding to, 647-648
pass-through queries, 578
creating, 578-579
passing objects to subroutines and functions, 413-414
passing parameters, 373-374
by reference vs. by value, 610-613
passing string parameters, 987-989
passwords
using database passwords to encrypt Access 2007 databases, 1109-1110
databases, 867-869
external data, 867-869
removing, 1110
pdbe object-naming prefix, 1134
Performance Analyzer, 807
queries, 553
Performance Analyzer dialog box, 808
period (.), 409
persisting recordsets, 702-704
personal applications, developing, 8
personal error handling, preventing, 786
Picture properties
forms, 231
reports, 287
pivot charts, creating from queries, 179
Pivot Table view
forms
controlling information detail, 261-262
data filtering, 261
displaying, 256-257
summarized data, 259
query displays, 179-183
axes exchange, 185
drill-down, 184
exchanging axes, 185
filtering data, 183
summarized data, 181-183
pivot tables
forms
controlling information detail (Pivot Table view), 261-262
creating from, 256-259
displaying (Pivot Table view), 256-257
filtering data (Pivot Table view), 261
summarized data (Pivot Table view), 259
queries
creating from, 179-183
filtering data, 183
replacing Crosstab queries with, 562
PivotChart view, switching to, 185, 262
PivotCharts, new features, 51
PivotTable view, displaying forms, 257-258
PivotTables, new features, 51
polymorphism, 638
Pop Up, 288
Pop Up property, forms, 235
pop-up forms, 473-474
PowerPoint
controlling from Access, 970-973
slides, 972-973
precedence, procedures, 357-358
prefixes (naming conventions), 1131
Preserve keyword, 608-609
preventing personal error handling, 786
previewing reports, 20
PreviousControl property, 416
Primary Key indexes, creating, 98
primary keys, 117
Append queries, 545
normalization, 25
normalization and, 115
Null values and, 122
Update queries, warnings, 541
primary keys (client/servers), 922
Print event, report sections, 522-523
Print Form Definition dialog box (Database Documenter), 1084
Print Query Definition dialog box (Database Documenter), 1084
Print Table Definition dialog box (Database Documenter), 1083
PrintCount property, reports, 503
printed forms, 190
Printer object, 503-504
printers, controlling, 503
Printers collection, 504
printing
error forms, 785
forms, 233
labels
in specific locations, 522-523
multiple labels, 520-521
page headers, reports, 530-533
reports
page breaks, inserting, 293
values to the Immediate window, 734
PrintSection property, reports, 502
private procedures, 356
Private variables, system information class, 666-667
prm object-naming prefix, 1134
prms object-naming prefix, 1134
procedures. See also subroutines
calling, 354
ChangeCaption, 450
declaring Class modules, 355
error handling, creating call stacks, 786, 789-790
event
creating, 352
modules, 350
executing, Module window, 374-375
GetDriveInfo, 1006
libraries, unique names, 990
private, 356
public, 355-356
recursive procedures, 616-617
scope, 358
precedence, 357-358
Static, 358
updating records, 828
variables, lifetime, 355
programming. See also coding
importing external data, 854
VBA
constants, 380
find and replace feature, 391
viewing code, 387
project properties, 631-632
Project Properties dialog box, 631
Project window, VBA, 388
Project/Library drop-down list (Object Browser), 406
propagating field properties, 481-483
reports, 507-509
AbsolutePosition property (ADO recordsets), 699-700
ActiveControl, 415
ActiveForm, 415
ActiveReport, 415
adding to Class modules, 639
Alias property, query tables, 171
Allow Filter, 288
Application object, 417
assigning multiple properties, With...End With construct, 412
automation objects, 953
BOF property (ADO recordsets), 690-691
Bookmark property (ADO recordsets), 700-701
Bound Column, combo boxes, 223
Caption property, query fields, 170
check boxes, Triple state property, 224
Column Count, combo boxes, 223
Column Width, combo boxes, 223
combo boxes, 222
compared to methods, 408-409
Connection strings, 673
control defaults, optimization and, 831
Control Source, combo boxes, 223
Allow AutoCorrect, 241
Auto Repeat, 242
availability, 236
Back Color, 290
Back Style, 290
Can Grow, 290
Can Shrink, 290
Cancel, 241
ControlTip Text, 241
Data, 292
Enter Key Behavior, 241
Font Color, 291
Font Italic, 291
Font Name, 291
Font Size, 291
Font Underline, 291
Font Weight, 291
Help Context ID, 241
IME (Input Method Editor), 242
Other, 292
Running Sum, 292
Scroll Bars, 237
Special Effect, 237
Status Bar Text, 241
Tab Index, 241
Tab Stop, 241
Vertical, 241
ControlType property, 414-415
custom properties
adding to classes, 651-652
creating, 484-488
PropertyGet routine, 486-488
PropertyLet routine, 486-488
Public variables, creating, 484-486
Data properties, form controls, 239-240
databases, documentation, 1081
DateCreated, 424
DateModified, 424
default, 410
Description property
queries, 171
query fields, 170
EOF property (ADO recordsets), 690-691
Err object, 766
Field List properties, queries, 171
field properties
queries, 170
fields
Default Value, 91
Input Mask, 90
Validation Rule, 94
file information class, FullFileName property, 652-654
Filter, 287
tables, 102
Filter On Load, 288
Filter property
ADO recordsets, 695
queries, 172
FilterOn, 513-514
Force New Page, reports, 293
Form, 416
Format properties
form controls, 236-239
query fields, 170
Allow Datasheet View, 231
Allow Filters, 234
Allow PivotChart View, 231
Allow PivotTable View, 231
Auto Resize, 231
Border Style, 231
Cycle, 235
Data Entry, 234
Data properties, 233
Description property, 1075
Dividing Lines, 232
Fast Laser Printing, 236
Filter, 234
Format, 231
Has Module, 235
Layout for Print, 233
Menu Bar, 235
Modal, 235
Moveable, 232
Navigation Buttons, 232
OpenArgs property, 468
Order By, 234
Orientation, 233
Picture, 231
Pop Up, 235
property sheet, 229-230
Record Selectors, 231
Recordset Type, 234
RecordSource property, 468, 470-471
Scroll Bars, 232
Shortcut Menu, 235
Shortcut Menu Bar, 235
Tag, 236
Width, 231
Help, 402
Input Mask property, query fields, 170
Lookup Wizard, Display Control property, 99-100
Me, 416
modifying, 408-409
Module, 416
Multiselect property (list boxes), 475-476
Object Browser, 379-380
objects, object library, 951
Order By, 288
Order By On Load, 288
Order By property, queries, 172
OrderByOn, 515-516
Orientation, tables, 102
Other properties, form controls, 240-242
Output All Fields property, queries, 171
Parent, 416
adding to classes, 647-648
PreviousControl, 416
project properties, 631-632
queries
Description property, 1075
modifying behavior and appearance, 170
viewing, 548
Query properties, 171-172
Record Locks property, queries, 172
Record Source, 287
RecordCount property (ADO recordsets), 691-693
Recordset Type property, queries, 172
RecordsetClone, 416
RecordSource, 720
Report, 416
reports, 284, 286-289, 501-503
Auto Center, 286
Auto Resize, 286
Border Style, 286
Can Grow property, 304
Can Shrink property, 304
Caption, 285-286
Close Button, 287
Control Box, 286
controls, 289-291
Data, 287
Description property, 1078
Force New Page property, 303
Format, 285
FormatCount, 503
Grid X, 287
Grid Y, 287
Grp Keep Together, 286
HasContinued, 503
Layout for Print, 287
Min Max Buttons, 286
Moveable, 287
MoveLayout, 502
NextRecord, 502
Orientation, 287
Page Footer, 286
Page Header, 286
Palette Source, 287
Picture, 287
PrintCount, 503
PrintSection, 502
Repeat Section property, 304
Tag, 289
Width, 287
WillContinue, 503
Row Source Type, combo boxes, 223
Run Permissions property, queries, 172
Section, 416
Sort property (ADO recordsets), 693-694
Source property, query tables, 171
special properties that refer to objects, 415-416
Subdatasheet Name, tables, 102
subforms, 249
subreports, 300
Tab controls, 456-457
table fields, 87
Allow Zero Length, 95-96
Caption, 91
Default Value, 91-92
Field Size, 88
Format, 88-89
Indexed, 96-97
Input Mask, 89-90
Required, 94-96
Unicode Compression, 97
Validation Rule, 92-94
Validation Text, 94
tables, 102
Description, 101
viewing, 101-102
Top Values, queries, 171, 551-552
Unique Records, queries, 171, 549
Unique Values, queries, 171, 548-549
upsizing to client/servers, 925
Validation Rule, tables, 101
Validation Text, tables, 102
viewing, 402
Properties collection, Table object, 860
Properties window
reports, 285
VBA, 390
properties Filter, 513-514
properties OrderBy, 515-516
property builders, 1027
Property Get routine
Class modules, 640-642
custom error handler class, 793
data access class, 656-658
system information class, 667-668
Property Let routine
Class modules, 640-642
custom error handler class, 793
data access class, 656-658
system information class, 667-668
Property Set, setting values, 642
property sheet, forms, 229-230
PropertyGet routine, custom properties, 486-488
PropertyLet routine, custom properties, 486-488
prototypes, applications development, 27
prp object-naming prefix, 1134
prps object-naming prefix, 1134
prps prefix, 1132
Public constants, 603
Public keyword, 356
public procedures, 355-356
public variables, 348, 362-363
custom properties, creating, 484-486
publishing data to SharePoint sites, 891-896
PWD keyword, 867
Q
qapp object-naming prefix, PDF:1135
qddl object-naming prefix, PDF:1135
qdel object-naming prefix, PDF:1135
qflt object-naming prefix, PDF:1135
qlkp object-naming prefix, PDF:1135
qmak object-naming prefix, PDF:1135
qry object-naming prefix, PDF:1134
qry prefix, PDF:1135
qrys object-naming prefix, PDF:1134
qsel object-naming prefix, PDF:1135
qspt object-naming prefix, PDF:1135
qtot object-naming prefix, PDF:1136
queries
Action. See Action queries
analyzing, 553
improving performance, 554-555
basing forms on, 249-250
basing forms/reports on, 837
columns, selecting, 567
compiling, 553
creating, 138-143
multiple tables, 152
with ADO code, 718
criteria
Like, 554
specifying at runtime, 172-175
Crosstab, 556-557
creating with Crosstab Query Wizard, 557-559
creating in Design view, 559-560
fixed column headings, 561-562
parameters, 562
crosstab queries, reports, creating, 523, 525-530
design examples, Time and Billing applications, 186-187
designing (Query Design window), 17
documenting (Database Documenter), 1075-1076
effect of multivalued fields, 69, 71
efficiency of compared to SQL statements, 838
expressions, 554
creating (Expression Builder), 160-161
Field List properties, 171
Field properties, 170
fields
adding, 139-141
calculated fields, creating, 157-159
changing location on query grid, 143-144
excluding from output, 166-167
indexing, 554
inserting after query is built, 143
performance considerations, 554
removing from query grid, 142-143
forms
combo boxes, 220
creating, 193
record sources, 198
function results as criteria, 586-587
modifying behavior and appearance, properties, 170
creating, 151-153
pitfalls, 153-155
row fix-up feature, 155-157
naming, 144
nested, calculated fields, 554
null values, 579-581
one-to-many queries, creating one-to-many reports, 296
optimization, 810
outer joins, 562-564
Parameter queries
ADO, 701-702
building, 172-175
parameter query values, passing from forms, 588-589
pass-through, 578
creating, 578-579
Performance Analyzer, 553
performance considerations, 553
pivot tables
creating from, 179-183
filtering data, 183
properties
Description property, 1075
Top Values, 551-552
Unique Records, 549
Unique Values, 548-549
Validation Rules, 94
viewing, 548
Query Plan, 553
Query properties, 171
Records, counting, 555
reports
creating, 527
efficiency considerations, 275
results
date criteria, 149-150
excluding fields, 166-167
Null values, 166-169
ordering, 144-145
selection criteria, 146-149
sorting, 145-146
updating, 150
Rushmore technology, 555-556
saving, 144
select, 16
select queries, defined, 137
self joins, 564-565
smart tags
action buttons, 176
adding, 175-178
specifications/limitations, 185-186
creating, 574
stored queries, 250-251, 827-828
basing reports on, 305
subqueries, 581
table statistics, recreating, 1092
tables
adding, 139
joining, 570-571
removing, 153
selecting, 568
Time and Billing application example, 595-597
Totals queries, 161-165
Union, 577
creating, 575
creating with graphical QBE, 576-577
sorting results, 576
viewing shortcut keys, 142
windows, SQL View, 574
Query Design window, 139
queries, designing, 17
query grid, fields
moving, 143-144
removing, 142-143
Query Parameters dialog box, 173, 175
Query Plan, 553
Query properties, 171-172
Query Properties window, 1075
Quick Access toolbar
new features, 34-36
Quick Info feature, modules, 385
quick watches (debugging), 745
quni object-naming prefix, 1136
qupd object-naming prefix, 1136
qxtb object-naming prefix, 1135
R
Raise method (Err object), 767-769
generating errors, 767-768
user-defined errors, creating, 768-769
RAM
development environment recommendation, 803
optimization, 802-803
ReadAheadPages settings (Windows registry), 806
reading, text files, 629
Reading Order, 291
rebooting, preventing during long processes, 801
recompiling modules, 828
Record Locks, 288
queries, 172
Record Selectors property, forms, 231
Record Source property, 287
record sources, forms, 198
changing at runtime, 233
combo boxes, 220
record-movement methods, ADO (ActiveX Data Objects), 688-689
RecordCount property (ADO recordsets), 691-693
records, 599-602
adding queries, 543-545
archiving, Append queries, 543
copying/deleting, controlling user access, 231
counting, queries, 555
deleting
DAO, 722-723
queries, 542
dynasets, 16
forms, ordering, 234
normalization rules, 25
orphan records, 131
updating, 828
queries, 539-541
RecordserClone property, 416
Recordset object
ADO object model, 676-678
declaring, 676
Open method
CursorType parameter, 680-682
LockType parameter, 682-683
Options parameter, 684-685
Recordset objects
cursors, selecting location, 686-687
Supports method, 687
updating, consistent compared to inconsistent, 685-686
Recordset property
AddNew method, 722
MoveNext method, 721
Recordset Type property
forms, 234
queries, 172
recordsets
action queries, making bulk changes, 708-712
ADO methods
Find method, 696-699
record-movement methods, 688-689
AbsolutePosition property, 699-700
BOF property, 690-691
Bookmark property, 700-701
EOF property, 690-691
Filter property, 695
RecordCount property, 691-693
Sort property, 693-694
ADO recordset types
CursorType parameter, 680-682
LockType parameter, 682-683
Options parameter, 684-685
batch updates, 707-708
data entry forms, 719-724
filtering, 695
finding records, 696-699
forms, 719-724
looping through, 827
modifying all records, 704-706
MoveFirst method, 688
MoveLast method, 688
MoveNext method, 688
MovePrevious method, 688
parameter queries, 701-702
persisting, 702-704
records
adding (ADO), 714-715
deleting (ADO), 712-713
modifying (ADO), 701-702
refreshing data, 702
sorting, 693-694
stored procedures and, 713
underlying recordsets, synchronizing forms with, 483-484
RecordSource, 720
forms, changing at runtime, 468-471
RecordSource property (forms), changing at runtime, 468, 470-471
Rectangle tool, 283
rectangles, 283
recursive procedures, 616-617
Reddick naming convention, naming objects, 23
Reddick VBA Naming Conventions (RVBA), 1131
Redim Preserve statement, 826
redoing actions, 89
references
explicit references, creating in library databases, 1017-1020
passing parameters, 987
runtime references, LoadOnStartup key, 1016
References dialog box, 951, 1017
referencing
applications, 950-951
controls, avoiding repeated in loops, 370
default properties, 410
disambiguation, 672
library databases, 1013
creating references, 1014
explicit references, limitations, 1018
RefLibPaths key, 1020
runtime references, 1014-1016
VBA code, 1021
objects, 407
shortcuts, 410
referencing applications, object variables, 950-952
referencing items (custom collections), 650
referential integrity, 128-130
ANSI-92 extensions, 591
Cascade Delete Related Records option, 131-133
Cascade Update Related Fields option, 130
Delete queries, warnings, 543
Update queries, warnings, 541
referential integrity rules, 121-122
RefLibPaths key, 1020
refreshing links, 877-880
external data, 869-871, 878-881
RegEdit utility, 928
Regional Options, date formats, 89
registering
builders, 1032
Add-In Manager, 1036-1039
manually, 1032-1035
wizards, 1044-1045
registering objects, 951
Registry, AddInPath key, 1014
registry
API functions, 994-997
builder configuration
Add-In Manager, 1036-1039
manual, 1032-1035
Menu Add-ins section, library references, 1014
Registry
RefLibPaths key, 1020
subkeys, creating, 1034
wizard configuration, 1044
Registry Editor, invoking, 1033
registry keys (ISAM drivers), 866
registry settings
implicit transactions, 927-929
Windows, 806
RegQueryValueEx, 995
RegSetValueEx, 996
rel object-naming prefix, 1134
relationships, 115
benefits, 133
Cascade Delete Related Records option, 131-133
Cascade Update Related Fields option, 130
creating, 125-127
default behaviors, overriding, 130
defining, 14
deleting, 128
many-to-many, 123-124
modifying, 128
join lines, 124
one-to-one, 123
join lines, 124
outer joins, 562-564
referential integrity, 128-130
tables, establishing relationships with ADO code, 717-718
Time and Billing database example, 134-136
unhiding, 14
update queries, warnings, 541
upsizing to client/servers, 924
Relationships command (Tools menu), 124
Relationships window, 124
tables, 14
ReLink function, 881
ReLink routine, 879-881
rels object-naming prefix, 1134
Remove method, 650
Collection object, 627
removing
columns, from form layouts, 213-214
form layouts, 214-215
links, external data, 869-871
passwords, 1110
Trusted Locations, 1119
user-level security, 1125
repairing databases, 1092
Repeat Section property, reports, 304
Replace dialog box, 391
Replace function, 378
replication, 52
re-creating databases, 53-56
Report Builder, 272
Report Design window, creating reports, 278-281
report events, 493-496
Close, 494
Error, 495
NoData, 494-495
order of, 496
Page, 495
Format event, 496-498
manipulating, 501
order of events, 500
Retreat event, 500
triggering macros from, 326-327
Report Footer Format event, reports, 530, 532
Report Footers, 275
Report Header Format event, 526-527, 531
Report Headers, 275
Report property, 416
report sections, manipulating, 501
Report Wizard, 275-277
one-to-many reports, creating, 294-296
reports
Access, opening from SharePoint sites, 896-899
adding, fields, 278
automatic error checking, 505-507
basing on queries/embedded SQL statements, 837
Border Style property, 286
borders, 286
charts, 272
controls, 282-284
Design view, 278
from crosstab queries, 523, 525-530
locking tables, 288
queries, 527
Report Design window, 278-281
Report Wizard, 275-277
Word, 969-970
Cross-tabulation reports, 270
custom methods, creating, 489
custom properties
creating, 484-488
PropertyGet routine, 486-488
PropertyLet routine, 486-488
Public variables, creating, 484-486
Data properties, controls, 292
detail, displaying, 516-517
Detail reports, 270
Detail section, 274
documenting (Database Documenter), 1078
error events, 796
rptClientListing report, 307-309
rptTimeSheet report, 311-313
field properties, propagating, 507-509
fields, selecting, 275
filter properties, 513-514
forms, 272
graphics, 272
Group Footers, 275
Group Headers, 275
group levels, adding, 276
hyperlinks, adding, 306
Internet compatibility, 305-306
labels
captions, 91
hyperlinks, 306
printing, 520-523
layouts, 277
mailing labels, 273
Me keyword, 450
multifact crosstab reports, creating, 533-536
new features, 51
numbering items, 518
objects
aligning, 281
moving, 280-281
selecting, 279-280
sizing, 281
Snap to Grid feature, 281
spacing, 282
one-to-many reports, creating, 294-296, 299
optimizing, 831
Other properties, controls, 292
page breaks, inserting, 293
Page Footers, 274
Page Headers, 274
printing first and last page entries, 530-533
previewing, 20
Printer object, 503-504
Printers collection, 504
printing, 287
properties, 502
properties, 284, 286-289, 501-503
Auto Center, 286
Auto Resize, 286
Can Grow, 304
Can Shrink, 304
Caption, 285-286
Close Button, 287
Control Box, 286
controls, 289-291
Data, 287
Description property, 1078
Format, 285
FormatCount, 503
Grid X, 287
Grid Y, 287
Grp Keep Together, 286
HasContinued, 503
Layout for Print, 287
Min Max Buttons, 286
MoveLayout, 502
NextRecord, 502
Page Footer, 286
Page Header, 286
Picture, 287
PrintCount, 503
PrintSection, 502
Repeat Section, 304
Tag, 289
Width, 287
WillContinue, 503
Properties window, 285
queries, efficiency considerations, 275
RecordSource, changing at runtime, 509-513
Report Footers, 275
Report Headers, 275
saving as HTML, 306
sections, 274-275
adding at runtime, 501
hiding/displaying, 516-518
sort order properties, 515-516
sorting, 301-303
sorting levels, selecting, 276
specifications and limitations, 307
stored queries, 305
styles, selecting, 277
subreports, 300
summary, displaying, 516-518
Summary reports, 270
Time and Billing application examples, 537
titles, 277
Caption property, 285
types, 269
Reports collection, 419
Requery method, refreshing recordset data, 702
Require Variable Declaration option, 395
Required property (fields), 94-96
reserved words, upsizing to client/servers, 925
Resize event, forms, 436
form objects, 202-203
forms, Form event sequence, 442
labels, automatically, 281
report objects, 281
responding to errors, 776-780
restoring
default settings to ribbons, 943-944
hidden objects, 64-65
Resume Next statement, 762-763
Resume statement, 761-762
Resume [LineLabel] statement, 763-764
Resync method, refreshing recordset data, 702
Retreat event, report sections, 500
retrieving data from Type variables, 601
Ribbon Name, 289
ribbons, 935
adding groups and commands to, 944-945
applying custom ribbons
to entire databases, 940-941
to forms or reports, 941-943
custom ribbons, building, 841-842
customizing, 935-936
enabling the display of system errors, 937
showing system tables, 936-937
USysRibbons table, 938
USysRibbons table, adding data to, 939-940
executing macros from, 946-947
new features, 30
command tabs, 30-33
contextual command tabs, 34
gallery, 34
Microsoft Office Access button, 36
Quick Access toolbar, 34-36
tips and tricks, 36-38
restoring default settings, 943-944
Right function, 377
right outer joins, 563
Rollback method, transaction processing, 929-930
routines
ChangeCaption, 413
CloseExcel, 960
FlipEnabled, 414-415
GetDrive routine, file information class, 653-655
GetName routine, file information class, 653-655
GetPath routine, file information class, 653-655
LogError, custom error handler class, 793-794
PropertyGet
Class modules, 640-642
custom error handler class, 793
custom properties, creating, 486-488
data access class, 656-658
system information class, 667-668
PropertyLet
Class modules, 640-642
custom error handler class, 793
custom properties, creating, 486-488
data access class, 656-658
system information class, 667-668
row fix-up feature, multitable queries, 155-157
Row Source Type property, combo boxes, 223
rows (client/servers), 922
rpt prefix, PDF:1132, PDF:1135
rptClientListing report, creating, 307-309
rpts prefix, 1132
rptTimeSheet report, creating, 311-313
rsrp object-naming prefix, PDF:1136
rst object-naming prefix, PDF:1134
rsts object-naming prefix, PDF:1134
rsub prefix, PDF:1132
rulers, forms (selecting objects), 200
Run command (Start menu), 1033
Run Permissions property, queries, 172
macros, 324
from Macro Design window, 325
from Macros group of the Navigation Pane, 326
queries, 141
Running Sum property, controls, 292
Runtime Error dialog box, 750
runtime errors, 750-751
runtime references
calling functions, 1015
library databases, 1014-1016
LoadOnStartup key, 1016
runtime version, distributing applications, 839-840
basing applications on forms, 839
custom help, 841
database startup options, 839-840
error handling, 841
security, 839
Rushmore technology, 555-556
RVBA (Reddick VBA Naming Conventions), PDF:1131
S
s scope prefix, 1135
Sandbox mode, 1123
disabling in Windows Vista, 1123-1124
disabling in Windows XP or Windows Server 2003, 1124-1125
Save Backup As dialog box, 1099-1100
Save method, data access class, 663-664
Save Splitter Bar Position, 216
SaveSetting function, 994
saving
database objects as XML, 1066-1068
forms, 253
queries, 144
reports as HTML, 306
table data
in HTML format, 104-107
in XML format, 107-109
scalability
applications, 11
splitting tables from other objects, 836
Scatter method, data access class, 658-659
schema (client/servers), 922
scope
arrays, 605
procedures, 358
precedence, 357-358
variables, 360-363
scr prefix, 1132
Screen object, 423
Scroll Bar Align, 291
Scroll Bars property
controls, 237
forms, 232
searching
records in recordsets, 696-699
tables, indexes, 96-97
sec prefix, 1132
second normal form, 119
Section events, 443
Format, 496-498
order of events, 500
Retreat, 500
Section property, 416
sections, reports, 274-275
hiding/displaying, 516-518
security
Access compared to client/servers, 918
database passwords to encrypt Access 2007 databases, 1109-1110
distributing applications, 839
locking data, 913
transactions, ANSI-92 extensions, 594
Trust Center, 1115
Access macros and VBA code, 1116-1117
message bars, 1115
Trusted Locations, 1117-1120
Trusted Publishers, 1120-1122
trusting databases, 1106
for current sessions, 1106
permanently, 1106-1107
upsizing to client/servers, 924
user-level security, 1105
removing, 1125
Select Case statements, 368-369
compared to If statements, 366
SELECT clause, SQL, 567
SELECT INTO statement, SQL, 583
select queries, 16
defined, 137
SELECT statement (SQL), 567-571
selecting
form objects, 199-200
report objects, 279-280
selection criteria, refining query results, 146-149
selection handles, 199
objects, sizing, 202
self joins, queries, 564-565
self joins (SQL), 571-572
self-signed certificates, creating, 1111-1112
semicolon (;), input masks, 90
sequence of events, 449
sequence of Form events, 441-442
servers
applications, installing, 910
automation servers, 950
Set Next Statement command (Debug menu), 742-743
memory usage considerations, 674
SharedAsyncDelay setting (Windows registry), 928
SharePoint Server 2007, 883
Access 2007 (accdb) file format and, 885
benefits of, 884-885
creating databases from SharePoint lists, 884
exporting data to, 885-890
exporting entire databases to, 884
exporting selected data to, 883
linking to and importing from SharePoint lists, 900-902
opening Access forms and reports from, 884, 896-899
publishing data to, 891-896
reestablishing links when SharePoint sites have been moved, 906
taking lists offline with Access, 884, 903-905
Shift+F2 command, VBA (viewing code), 387
Short Date format (fields), 89
shortcut icons, compacting databases, 1095
shortcut keys
forms, 198
queries, viewing, 142
Shortcut Menu, 289
forms, 235
shortcuts, object references, 410
Show Tables dialog box, 126, 139, 576
self join queries, 564
showing
document tabs, 44-46
status bar, 49
showing system tables, customizing ribbons, 936-937
shp prefix, 1132
signed packages
creating, 1112-1113
extracting, 1113-1114
signing Access databases, 1110-1111
self-signed certificates, 1111-1112
simple keys, 117
Simple Query Wizard, 138
single forms, creating, 451-452
sizing
form layouts, 210-211
form objects, 202-203
forms, Form event sequence, 442
labels, automatically, 281
report objects, 281
slides (PowerPoint), 972-973
small businesses, developing applications, 8
smart tags
adding to forms, 253-255
forms
action buttons, 255
adding, 254-255
queries
action buttons, 176
adding, 175-178
Snap to Grid
disabling, 281
forms, 202
adjusting grid, 233
temporarily disabling, 202
report objects, 281
sng data-type prefix, 1133
software, optimization
compacting database, 803
defragmenting hard disk, 803
drive compression, 804
local execution, 804
swap file, 803-805
Windows 95/98, 806
Windows registry settings, 806
sort order properties, reports, 515-516
Sort property (ADO recordsets), 693-694
sorting
query results, 145-146
ascending order, 145
by more than one field, 145-146
recordsets, 693-694
reports, 301-303
Union query results, 576
Sorting and Grouping window, 301
sorting levels, selecting reports, 276
source code, removing from applications, 839
source database types, 860
Source Object property
subforms, 249
subreports, 300
Source property, query tables, 171
spacing
form objects, 203-204
report objects, 282
Special Effect, 290
controls, 237
SpecialEffect function, 1030
specific object variables, 812-813
compared to generic object variables, 411
specifications, reports, 307
SpecificBold, 813
Splash Screen forms, creating, 459
splash screens (forms), creating, 491
Split Form Orientation, 216
Split Form Printing, 216
Split Form Size, 216
Split Form Splitter Bar, 216
split forms, 215-217
splitting
database objects, 835-836
one control layout into two form layouts, 214
tables from other objects, 835-836
SQL
ALL clause, 572
All keyword, 576
client/servers, 922
FROM clause, 568
GROUP BY clause, 573
HAVING clause, 573
history, 566
JOIN clause, 570-571
non-equi joins, 572
ORDER BY clause, 569
creating, 574
SELECT clause, 567
SELECT statement, 567-571
self joins, 571-572
statements
ALTER TABLE, 585
Append queries, 545
CREATE INDEX, 584-585
CREATE TABLE, 584
DELETE, 582-583
DROP INDEX, 585
DROP TABLE, 586
executing from VBA, 574-575
INSERT INTO, 583
left outer joins, 563
reports, 304-305
saving as queries, 305
SELECT INTO, 583
storing queries, 541
UPDATE, 582
syntax, 567
TOP keyword, 572
updating data, 582-583
WHERE clause, 568-569
SQL Pass-Through, front ends communicating to back ends, 920-921
SQL Server 2000, Cascade Update and Delete features, 133
SQL statements
efficiency of compared to stored queries, 838
embedded SQL statements, basing forms/queries on, 837
embedding, 250-251
SQL View window, queries, 574
stacked layouts
Layout view, forms, 206-209
moving and resizing, 210-211
switching to tabular layouts, 213
standard modules, 347
Start menu commands, Run, 1033
starting Form Wizard, 195
startup forms, 490
statements
variables, 359
Exit Sub, error handling, 759
For Each...Next statement, 372-373
If, 365
compared to Select Case statements, 366
Kill, 760
On Error, 758
On Error Goto, 758-759
On Error Goto 0, 759-760
On Error Resume Next, 760
Option Explicit, modules, 349, 351
Redim Preserve, 826
Resume, 761-762
Resume Next, 762-763
Resume [LineLabel], 763-764
Select Case, 368-369
compared to If statements, 366
Set, 953
memory usage considerations, 674
Set statement, 640
SQL. See SQL, statements
Stop statement, 735
Type, 600
With, loops, 823-824
With...End With, 372
Static keyword, variable lifetime, 355
Static procedures, 358
Static variables, 361-362
Statistics tab (Database Priorities dialog box), 1080
status bar, new features, 47-49
Status Bar Text property, controls, 241
Step Into option (debugging), 738-739
Step Out feature (debugging), 742
Step Over option (debugging), 741-742
stepping through macros, 338
stepping through code
breakpoints, setting, 739-741
Step Into option, 738-739
Step Out feature, 742
Step Over option, 741-742
stf data-type prefix, 1133
Stop statements, 735
stored procedures, 713
ANSI-92 extensions, 593-594
executing on back ends, 921
stored procedures (client/servers), 922
stored queries, 250-251, 827-828
basing reports on, 305
storing form data in Type variables, 601
str data-type prefix, 1133
strControl argument (builder function), 1030
strCurrentValue argument (builder function), 1030
string parameters, passing, 987, 989
String Value command (New menu), 1034
strings
compiler constants, 367
compression, ANSI-92 extensions, 592
delimiters, 697-699
functions, 376-377
Null value, avoiding, 624
strObject argument (builder function), 1030
strong-typing variables, avoiding bugs, 728
StrRev function, 379
structs, 599-602
structures, 991
structuring code modules (library databases), 1012-1013
styles, forms (selecting), 195
subclassing, 638
subform controls, referring to, 476-477
Subform/Subreport Wizard, 300
one-to-many forms, creating, 247-248
modifying, 249
order of fields, 249
properties, 249
referring to subform controls, 476-477
viewing, 249
SubKey field (USysRegInfo table), 1038
subkeys, creating, 1034
subprocedures, 347
subqueries, 581
Subreport Wizard, creating one-to-many reports, 298-300
subreports, 300
viewing, 249
subroutines, 347-349. See also procedures
code, viewing, 387
CreateExcelObj, 956
creating, 352-354
shortcut for, 354
Declare statement, 986
FillCells, 958
LinkToDBase, 862
parameters, 373-374
passing objects to, 413-414
testing (Immediate window), 733
Suffixes (naming conventions), 1131
summarizing data (Totals queries), 161-165
Summary reports, 270
displaying, 516-518
Summary tab (Database Properties dialog box), 1079
Supports method, Recordset objects, 687
swap files, optimization, 803-805
Switchboard forms, creating, 457
Switchboard Manager, forms (creating), 190
switchboards
application switchboards, 839
forms, 190
between stacked and tabular form layouts, 213
to PivotChart view, forms, 262
Symantic Ghost, 842
symbolic constants, 381-382, 602
synchronizing changes with SharePoint, 903-904
syntax, SQL, 567
SysCmd function, 1031
System Database property (Connection strings), 673
system information class (Class modules), 666-669
Initialize event, 668-669
Private variables, 666-667
Property Get routine, 667-668
Property Let routine, 667-668
type structures, 666-667
system objects, hiding, 943
system performance. See also optimization
application types, 917
backup/recovery capability, 917
compacting database, 1091-1093
Declare statements, 987
limiting factors, 9
network demands, 917
queries, 553
improving performance, 554-555
security, 918
user base, 916
variables, declaring, 359
system requirements, hardware, 23
system-defined constants, 602
T
Tab controls
adding to forms, 456
properties, changing, 456-457
Tab Index property, controls, 241
tab order
form objects, changing, 204-205
forms, Cycle property, 235
Tab Order dialog box, 204
Tab Stop property, controls, 241
Tab Width feature, 395
tabbed documents, new features, 44-46
displaying overlapping windows, 46-47
tabbed forms, creating, 455-457
Tab controls, adding, 456
Table and Recordset locking, 913
Table database, 836
Table object, Properties collection, 860
tables, 12-14
Access tables, linking external data, 856-857
adding to databases, with ADO code, 715-716
adding to queries, 139
ANSI-92 extensions, 590, 592-593
back end tables, linking to front end application database, 919
calculations and, 119
creating, 79-81
designing, 81
Make Table queries, 545-547
data, saving as HTML, 104
data fields
Attachment, 86
AutoNumber type, 85
Currency type, 85
Date/Time type, 84
Hyperlink type, 87
Memo type, 84
Number type, 84
OLE Object type, 86
Text type, 84
Yes/No type, 86
Database window, viewing, 12
decomposition, 119
deleting, with ADO code, 717
denormalization, 120
Description property, 101
design rules, 116-117
Design view, viewing, 14
designing
from scratch, 79-81
for optimization, 808-809
Time and Billing application, 111-113
documenting, 81
Database Documenter, 1074-1075
dynasets, 16
external tables. See external tables
field types, Hyperlink, 103-104
fields
Allow Zero Length property, 95-96
Caption property, 91
Default Value property, 91-92
defining, 79
descriptions, 80
Field Size property, 88
Format property, 88-89
Indexed property, 96-97
Input Mask property, 89-90
inserting, 80
maximum number allowed, 123
normalization, 25
properties overview, 87
Required property, 94-96
selecting data types, 82-84
types, 79
Unicode Compression property, 97
Validation Rule property, 92-94
Validation Text property, 94
Filter property, 102
foreign keys, 115
referential integrity and, 121
forms
combo boxes, 220
one-to-many, 246
one-to-many, creating, 246-248
record sources, 198
indexes
limitations of, 809
performance benefits of, 103
Primary Key, 98
joining queries, 570-571
joins, outer joins, 563-564
junction, 123-124
linking, accessing in networks, 913
locking, creating reports, 288
multiple, creating queries, 152
multitable queries
creating, 151-153
pitfalls, 153-155
row fix-up feature, 155-157
names, upsizing to client/servers, 81, 924
naming, conventions, 80
non-equi joins, 572
normalization, 115-116
first normal form, 118
second normal form, 119
third normal form, 119-120
normalization rules, 24-25, 27
Orientation property, 102
primary keys, 117
properties, 102
viewing, 101-102
queries
selecting, 568
specifications/limitations, 185-186
relationships, 115
benefits, 133
Cascade Delete Related Records option, 131-133
Cascade Update Related Fields option, 130
creating, 125-127
defining, 14
deleting, 128
establishing with ADO code, 717-718
guidelines, 126-127
many-to-many, 123-124
modifying, 128
one-to-one, 123-124
referential integrity, 128-130
types of, 122
unhiding (Relationships window), 14
viewing (Relationships window), 14
removing from queries, 153
searching, indexes, 96
specifications/limitations, 110-111
splitting from other objects, 835-836
statictics (queries), recreating, 1092
Subdatasheet Name property, 102
temporary tables, 837
unique identifiers, 117
upsizing to client/servers, 923
USysRegInfo
importing, 1037
structure, 1038
Validation Rule property, 101
Validation Text property, 102
workstations, installing locally, 911
tables from hypothetical time and billing application
tblAddressTypes table, PDF:1148-1149
tblClientAddresses table, PDF:1143-1147
tblClientPhones table, PDF:1149-1151
tblClients table, PDF:1138-1143
tblCompanyInfo table, PDF:1159-1163
tblContactType table, PDF:1158-1159
tblCorrespondence table, PDF:1152-1155
tblCorrespondenceTypes table, PDF:1156-1157
tblEmployees table, PDF:1164-1167
tblErrorLog table, PDF:1168-1171
tblErrors table, PDF:1171-1172
tblExpenseCodes table, PDF:1172-1173
tblPaymentMethods table, PDF:1173-1174
tblPayments table, PDF:1174-1178
tblPhoneTypes table, PDF:1151-1152
tblProjects table, PDF:1178-1182
tblTerms table, PDF:1157-1158
tblTimeCardExpenses table, PDF:1182-1185
tblTimeCardHours table, PDF:1186-1189
tblTimeCards table, PDF:1190-1191
tblWorkCodes table, PDF:1191-1192
tabular layouts
Layout view, forms, 206-209
moving and resizing, 210-211
switching to stacked layouts, 213
Tag property
forms, 236
reports, 289
task analysis, application development, 24
tbl object-naming prefix, 1134
tbl prefix, 1135
templates, creating new databases, 76-78
temporary tables, 837
Terminate event, 644
testing
builders, 1035
macros, 337-338
testing stage, applications development, 27
text, aligning (object text), 202
Text Align, 291
text boxes
converting to combo boxes, 227-228
default property, 410
forms, adding, 219
reports, 282
text data, external data, 873
Text field type, 84-85
text files
I/O operations, 629
importing, 630
Text Format property, 292
tgl prefix, 1132
third normal form, 119-120
Time and Billing application
automation, examples, 979-980, 982-983
command buttons, enabling/disabling, 426-427
error handling, 798-799
form design, 263-268
frmClients form
command button additions, 268
designing, 263-265
frmProjects form, designing, 266-267
library databases, creating, 1023-1025
preparing for enterprise environment, 931
query design examples, 186-187
reports, examples, 307-309, 311-313, 537
table design overview, 111-113
tables. See tables from hypothetical time and billing application
VBA techniques, examples, 633-635
Time and Billing database, relationships example, 134-136
Timer event, forms, 441
titles
forms, 195
Caption property, 229
reports, 277
Caption property, 285
tlkp object-naming prefix, 1135
Toggling Boolean Benchmark, 816
Toolbar, 289
toolbars
Debug toolbar, displaying, 730
Format, Align tools, 202
Formatting, 196
Report Design window, 278
forms, designing, 196
mini toolbar, 49-50
Quick Access toolbar, 34-36
customizing, 38-41
tools
Bound Object Frame, 283
Check Box, 224
Combo Box, 220
Formatting toolbar, Align, 202
Line, 282
modules, 384-386
Page Break, 293
Rectangle, 283
Unbound Object Frame, 283
Tools menu commands
Add-Ins, 1038
Relationships, 124
TOP keyword, SQL, 572
Top Values property, queries, 171, 551-552
transaction (client/servers), 922
transaction processing, 926
benefits, 927
explicit transaction processing, 927
implementing, 929-930
implicit transaction processing, 927
methods, 929-930
modifying default behavior, 927-929
registry settings, 927-929
transactions, ANSI-92 extensions, 594
TransferDatabase method
arguments, 852
importing external data, 852-853
TransferSpreadsheet method
arguments, 854
importing external data, 853-854
TransferText method, importing external data, 853
transparent controls, 237
triggering macros from forms or report events, 326-327
triggers (client/servers), 922
Triple state property, check boxes, 224
troubleshooting. See also debugging; error handling
applications, 361
assertions, 752
Auto Data Tips, 745
avoiding bugs, 728
breakpoints, 735-741
Call Stack window, 743-744
Immediate window, 729-734
Locals window, 744
potential problems, 751-752
runtime errors, 750-751
Set Next Statement command, 742-743
Step Into option, 738-739
Step Out feature, 742
Step Over option, 741-742
Stop statement, 735
Watch expressions, 745-749
watches, 735
external data, 873-874
connection problems, 873
disk space, 874
library databases, 1022-1023
lookup feature, disadvantages of, 100
true/false evaluations, 819-820
Trust Center, 1115
Access macros and VBA code, 1116-1117
message bars, 1115
Trusted Locations, 1117-1119
modifying, 1120
removing, 1119
Trusted Publishers, 1120-1122
trusted databases, 1123
Trusted Locations, 1117-1119
modifying, 1120
removing, 1119
Trusted Publishers, 1120-1122
adding, 1127-1128
trusting
databases
for current sessions, 1106
permanently, 1106-1107
databases, 1106
txt prefix, 1132
Type field (USysRegInfo table), 1038
type libraries, 950
binding, 954
Type statement, 600
type structure declaration, generic error handlers, 772
type structures
examples, 633-635
system information class, 666-667
Type variables, 600-601
TypeOfDrive, 1007
types, DLLs, 992-993
typing variables, declaring, 359
U
UCase function, 377
UFI (Unsafe for Initialization), 1126
UID keyword, 867
Unassigned Objects group, hiding, 61
unbound controls, 242
Unbound Object Frame tool, 283
unbound object frames, reports, 283-284
unbound OLE objects, 467
UNC (universal naming conventions), 861
hyperlink field type, 87
UNC addresses, tables (Hyperlink field type), 103-104
underlying recordsets, synchronizing forms with, 483-484
underscore (_), line continuation character (VBA), 364
Undo event, forms, 434
undoing actions, 89
unhiding tables, Relationships window, 14
Unicode, 990
Unicode Compression property (fields), 97
uniform resource locators. See URLs
union queries, 577
creating, 575
graphical QBE, 576-577
sorting results, 576
unique identifiers (tables), 117
Unique Records property, queries, 171, 549
Unique Values property, queries, 171, 548-549
Universal Naming Convention. See UNC
Unload event, forms, 437
Unsafe for Initialization (UFI), 1126
untrusted databases, 1123
Update queries, creating, 539-541
UPDATE statement, SQL, 582
Updated event, controls, 444
updates, recordsets (batch updates), 707-708
updating
query results, 150
records, 828
queries, 16
Recordset objects, consistent compared to inconsistent, 685-686
updating links (external data), 869-871, 878-881
upsizing
issues, 923-925
AutoNumber fields, 923
case-sensitivity, 925
properties, 925
relationships, 924
reserved words, 925
security, 924
tables, 923
validation rules, 924
VBA, 925
preparation, 925
wizards, drawbacks, 926
Upsizing Wizard, 843
URLs (Universal Naming Convention), 87
hyperlink field type, 87
tables, 103-104
Use Default Paper Size, 289
user interface
compacting databases, 1093-1095
importing external data, 850-851
linking external data, 854-858
new features, 28-30
user-defined errors, creating, 768-769
user-defined routines
creating in class modules, 354
creating in code modules, 353
user-defined types, 599-602
declaring, 600
Type variables, 600-601
user-level security, 1105
removing, 1125
UserCommitSync setting (Windows registry), 928
UserForm, 976-977
usr object-naming prefix, 1134
USysRegInfo table, 1037-1038
USysRibbons table, 938
adding data to, 939-940
utilities, Compact, 803
V
Validation Rule property, tables, 101
Validation Rule property (fields), 92-94
validation rules
fields, adding to, 93-94
upsizing to client/servers, 924
Validation Text property
fields, 94
tables, 101
ValName field (USysRegInfo table), 1038
Value field (USysRegInfo table), 1038
values
constants, errors, 603
default, upsizing to client/servers, 91, 924
Empty, Variant variables, 618-619
Nothing value, object variables, 412
Null values
converting to zero, 167
examples, 632-633
queries, 579-581
Variant variables, 619-625
printing to the Immediate window, 734
returning, functions, 373-374
variables, changing values, 743
var data-type prefix, 1133
variable scoping, avoiding bugs, 728
data types, 359
declaring, 358
memory usage considerations, 674
modules, 348
Option Explicit statement, 349
performance considerations, 359
Dim statement, 359
intTemp, 739
lifetime, 355
local, 361
naming, 362
changing, 390-391
Public, 363
naming conventions, 1131-1134
null values, 359
object, referencing applications, 950-952
object variables, 410
automation objects, 953
compared to regular variables, 410-411
generic compared to specific, 411
Nothing value, 412
Private variables, system information class, 666-667
scope, 360-363
specific object variables, 812-813
Static, 361-362
Type, 600-601
values
changing, 743
setting (Immediate window), 730-732
testing (Immediate window), 730
Variant
Empty value, 618-619
Null value, 619-625
variant variables, eliminating, 811
variants, 359
variant variables, 359
eliminating, 811
Empty value, 618-619
Null value, 619-625
VBA (Visual Basic for Applications), 20, 345-347, 599
#Const directive, 367
advanced techniques, Time and Billing application examples, 633-635
declaring, 605-607
dynamic arrays, 607-609
fixed arrays, 605
passing as parameters, 609-610
bookmarks, adding, 394
code
compared to Action queries, 547-548
viewing, 387
Code window, splitting, 393
comments, 363-364
compared to macro capabilities, 345-347
Compile On Command feature, 629-630
defining, 602-604
intrinsic constants, 382-383, 602-604, 632-633
symbolic constants, 381-382, 602
system-defined constants, 602
control structures, 364-371
Conditional If, 367-368
For Each...Next statement, 372-373
For...Next construct, 371
If...Then...Else, 365
Immediate If (IIf), 366
loops, 369-370
Select Case statement, 368-369
With...End With statement, 372
converting macros to VBA code, 339-341
custom collections, 625-628
DLL functions, declaring, 986
DoCmd object, examples, 632-633
executing macros, DoCmd object, 374
find and replace feature, 390-391
DateAdd, 378
DateDiff, 378
DatePart, 377
Format, 376
Instr, 376
InstrRev, 376
Left, 377
Mid, 377
MonthName, 379
named parameters, 616
Object Browser, 379-380
optional parameters, 613-615
parameter arrays, 617-618
passing parameters, 610-613
recursive procedures, 616-617
Replace, 378
Right, 377
StrRev, 379
UCase, 377
help, context-sensitive, 391
line continuation character, 364
macros versus modules, 20
Module window, executing procedures, 374-375
modules, 347, 349-350, 354-358
event procedures, 352
functions, creating, 352-354
Option Explicit statement, 349-351
procedures, calling, 354-358
subroutines, creating, 352-354
named parameters, 375
Null values, examples, 632-633
passing parameters, 373-374
programming tools
bookmarks, 394
Code window, 388
Code Window, splitting, 393
Complete Word feature, 386
context-sensitive help, 391
Definition feature, 387
find and replace feature, 390-391
List Constants feature, 385
List Properties and Methods feature, 384
Parameter Info feature, 386
Project window, 388
Properties window, 390
Quick Info feature, 385
View Microsoft Access Tool, 390
project properties, 631-632
SQL statements, executing, 574-575
type structures, examples, 633-635
upsizing to client/servers, 925
user-defined types, 599-602
declaring, 600
Type variables, 600-602
data types, 359
declaring, 358-359
local variables, 361
Private variables, 362
Public variables, 362
scope, 360
Static variables, 361-362
Variant variables
Empty value, 618-619
Null value, 619-625
Visual Basic Editor (VBE)
coding options, 394-395
docking options, 396
general options, 395
VBA code
references, creating, 1021
Trust Center, 1116-1117
VBA functions
testing (Immediate window), 733
Windows registry, 994-997
VBE (Visual Basic Editor), 348
Comment Block tool, 364
customizing
coding options, 394-395
Editor Format tab, 395
general options, 395
Immediate window, 361
Option Explicit statement, inserting in all modules, 350
VerifyLink routine, 878-879
Versions, databases (conversion formats), 1100-1101
Vertical property, 292
controls, 241
View menu, forms (changing tab order), 204
View menu commands, Object Browser, 405
View Microsoft Access Tool, 390
viewing
code, VBA, 387
Object Browser, 604
database objects, icons (Database window), 12
events, 404
forms
Default View property, 230
Form view, 18
properties, 229
modules, Module Design window, 22
object dependencies, 109-110, 479-480
project properties, 631
properties, 402
queries, 548
queries, shortcut keys, 142
reports, preview mode, 20
subforms, 249
subreports, 249
tables, 12-14
views
ANSI-92 extensions, 593-594
Design, creating forms, 196
Layout view. See Layout view
virtual memory, optimizing, 803-805
Visible, 290
Visual Basic Editor. See VBE
Visual Basic for Applications. See VBA
W
Watch expressions, 745
adding, 746-748
breaking when expressions are true, 748-749
breaking when expressions change, 749
editing, 748
watches (debugging), 735
Weekday() function, 149
WHERE clause, SQL, 568-569
Width property
forms, 231
reports, 287
WillContinue property, reports, 503
Win32 API (Application Programming Interface), 985
data loss, preventing, 989
windows
Code window
splitting, 393
VBA, 388
dockable, 396
Field List, availability, 233
Form Design, 196-204
forms, designing, 197
Module, executing procedures, 374-375
Project window, VBA, 388
Properties
reports, 285
VBA, 390
windows
Relationships, 124
Report Design, creating reports, 278-281
SQL View, queries, 574
Windows, registry settings, 806
Windows 2000 Terminal Services
installing Access applications, 912
optimization and, 804
Windows 95/98, optimizing, 806
Windows API calls, 782-784
operating environment information, 997-1005
AddInPath key, 1014
keys, ISAM drivers, 866
RefLibPaths key, 1020
settings, transaction processing, 927-929
Windows Server 2003, disabling Sandbox mode, 1124-1125
Windows Vista, disabling Sandbox mode, 1123-1124
Windows XP, disabling Sandbox mode, 1124-1125
With statement, loops, 823-824
With...End With construct, 412
With...End With, 823
With...End With statement, 372
wizards
Combo Box, 220
Command Button, 244-245
fixing inefficient code, 396-398
Control, adding combo boxes to forms, 220
creating, 1040-1044
Crosstab Query Wizard, 557-559
Database Splitter Wizard, 912
defined, 1039
designing, 1039
Form
creating forms, 193-195
one-to-many forms, creating, 246-248
starting, 195
Import HTML Wizard, 874-877
Import Spreadsheet Wizard, 851
Input Mask, 89-90
Input Mask Wizard, 89-90
Label, 273
List Box, 224
Option Group, 225-226
registering, 1044-1045
Report, 275-277
one-to-many reports, creating, 294-296
Simple Query, 138
Subform/Subreport, 300
Subreport, one-to-many reports, creating, 298-300
Word (Microsoft)
controlling from Access, 966-968
mail merge documents, 966, 968
reports, creating, 969-970
workstations, installing applications, 911
Write # keyword, 628
writing
builder functions, 1028-1031
text files, 629
wrk object-naming prefix, 1134
WZMAIN80.MDE file, importing, 1037
X
XML (Extensible Markup Language), 107-109
forms, saving as, 253
importing XML files, 1068-1069
saving database objects as, 1066-1068
table data, saving in, 107-109
Y
Year() function, 149
Yes/No field type, 86
Z
Zoom dialog box, 243
Zoom option, 158