Index
Symbols
(backslash), input masks, 90
_ (underscore), line continuation character (VBA), 364
; (semicolon), input masks, 90
< (less than sign), query criteria operator, 146
! (bang), 409
#Const directive, VBA, 367
& (ampersand), Null values (avoiding in strings), 624
' (apostrophe), VBA comments, 363
* (asterisk), queries (adding fields), 140
> (greater than sign), query criteria operator, 146
<> (not equal to), query criteria operator, 147
>= (greater than or equal to), query criteria operator, 146
. (period), 409
= (equal sign), query criteria operator, 146
<= (less than or equal to), query criteria operator, 146
A
About dialog box, 997
AbsolutePosition property (ADO recordsets), 699-700
ACCDB files, front ends as links to communicate to back ends, 919
Access
as a front end, 842-845
options, 844
controlling from other applications, 976-979
Excel, controlling, 956-960
Outlook, controlling, 974-976
PowerPoint, controlling, 970-973
taking SharePoint lists offline, 884, 903
discarding changes, 904-905
synchronizing changes, 903-904
working online, 904
Word, controlling, 966-968
Access 2007 (accdb), SharePoint Server 2007 and, 885
Access data projects, front ends communicating directly to back ends, 921
Access Database Engine, 806
Access Object Model
AllForms collection, 420
AllMacros collection, 421
AllModules collection, 421
AllQueries collection, 423
AllReports collection, 421
AllTables collection, 422
Application object, 416
methods, 417-418
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
Access tables, linking external data, 856-857
AcDataErrAdded constant (NotInList event), 473
AcDataErrContinue constant (NotInList event), 473
AcDataErrDisplay constant (NotInList event), 473
Action queries
Append, creating, 543-545
compared to VBA code, 547-548
Delete, creating, 542
Make Table, creating, 545-547
naming prefixes, 541
recordsets, making bulk changes, 708-712
results, previewing, 543
Update, creating, 539-541
Activate event, forms, 438
ActiveControl property, 415
ActiveForm property, 415
ActiveReport property, 415
ActiveX automation, 949
ActiveX controls, 1125-1126
ActiveX Data Objects. See ADO
Add method
After argument, 649
Before argument, 649
Collection object, 626-627
custom collections, 649
Key argument, 649
Add New button, 1039
Add Procedure dialog box, 352, 487
Add Watch dialog box, 747
Add-In Manager, configuring registry entries, 1036-1039
Add-In Manager command (Add-Ins menu), 1038
Add-In Manager dialog box, 1038
builders, 1027
creating, 1028
designing, 1028, 1047, 1049-1050, 1052
forms design, 1031-1032
functions, 1028-1031
registering, 1032-1039
testing, 1035
menus, 1045-1046
wizards
creating, 1040-1044
defined, 1039
designing, 1039
registering, 1044-1045
Add-Ins command (Tools menu), 1038
Add-Ins menu commands, Add-In Manager, 1038
AddFromFile method, 1021
adding
columns to form layouts, 212-213
command buttons to forms, 244-245
commands to ribbons, 944-945
comments to macros, 336-337
fields
to forms, 198-199
to reports, 278
groups to ribbons, 944-945
hyperlinks to forms, 251-253
smart tags
to forms, 253-255
to queries, 175-178
Trusted Publishers, 1127-1128
AddInPath key, 1014
AddNew method, data access class, 664
ADO (ActiveX Data Objects), 671, PDF:1133
ADO object model, 671
Command object, 678-679
Connection object, 672-675
Recordset object, 676-678
queries, creating, 718
recordsets
AbsolutePosition property, 699-700
adding records, 714-715
BOF property, 690-691
Bookmark property, 700-701
CursorType parameter, 680-682
deleting records, 712-713
EOF property, 690-691
Filter property, 695
Find method, 696-699
LockType parameter, 682-683
modifying records, 701-702
Options parameter, 684-685
parameter queries, 701-702
persisting, 702-704
record-movement methods, 688-689
RecordCount property, 691-693
Sort property, 693-694
tables
adding, 715-716
deleting, 717
relationships, establishing, 717-718
ADO tags, 1133
ADP (Access Data Project), splitting tables from other objects, 835
ADP (Access Data Project) files, 56
After argument, Add method, 649
AfterDelConfirm event, forms, 435
AfterInsert event, forms, 432
AfterUpdate event
controls, 443
forms, 434
Alias property, query tables, 171
aliasing functions, 989-991
Align tools, 202
aligning
objects, forms, 200-202
report objects, 281
text, object text, 202
Alison Balter's Mastering Access 11 Client/Server Development, 10
ALL clause, SQL, 572
ALL keyword, SQL, 576
AllForms collection, 420
AllMacros collection, 421
AllModules collection, 421
Allow AutoCorrect property, controls, 241
Allow Datasheet View property, forms, 231
Allow Filters property, 288
forms, 234
Allow PivotChart View property, forms, 231
Allow PivotTable View property, forms, 231
Allow Zero Length property (fields), 95-96
AllQueries collection, 423
AllReports collection, 421
AllTables collection, 422
ALTER TABLE statement, SQL, 585
alternate keys, 117
ampersand (&), Null values (avoiding in strings), 624
And (query criteria operator), 147
ANSI functions, renaming, 990
ANSI-92 extensions, 590
stored procedures, 593-594
tables, 590-593
transactions, 594
views, 593-594
API calls, 782-784
operating environment information, 997-1005
API functions
aliasing, 989-991
constants, 991
hard drives, 1005-1008
APIs, functions, 993
Windows registry, 994-997
apostrophe ('), VBA comments, 363
app prefix, PDF:1132
Append queries, creating, 543-545
Application Database, 836
tables, storing in, 837
Application Object
Access Object Model, 416
CompactRepair method, 1098-1099
methods, 417-418
properties, 417
application switchboards, 839
applications
client/server, suitability of Access, 10
computer consulting firm example, developing, 72-73
controlling Access from, 976-979
corporate, developing, 8-9
designing
multiuser issues, 909
options, 919
development process
data analysis, 24-27
design phase, 24-27
implementation, 28
maintenance activities, 28
normalization rules, 24-27
prototypes, 27
task analysis, 24
testing stage, 27
development strategies, 835-837
Access as a front end, 842-845
distribution, 839-841
forms, basing on queries/embedded SQL statements, 837
reports, basing on queries/embedded SQL statements, 837
runtime version, 839-841
splitting database objects, 835-836
distributing, runtime version, 839-841
financial considerations, developing, 8
front-ends, 847
index usage, performance benefits, 103
installing, Windows 2000 Terminal Services, 912
Internet, developing, 11
intranets, developing, 11
library databases, Time and Billing application example, 1023-1025
multiuser, 910-911
navigating forms, 190
ACCDE files, 829
bookmarks, 826-827
Boolean expressions, 815
built-in collections, 816-817
compiling modules, 828
constants, 826
data structures, 808-809
Declare statements, 821
defined, 801
Dim statements, 821
dynamic arrays, 826
form-optimization techniques, 829-831
hardware configuration, 802-805
inline code, 814
Len function, 818
Me keyword, 825
object references, 821
object variables, 827
organizing modules, 829
Performance Analyzer, 807
queries, 810
report-optimization techniques, 831
specific object variables, 812-813
stored queries, 827-828
true/false evaluations, 819-820
unused code, 821
variant variables, eliminating, 811
Windows 95/98, 806
Windows registry settings, 806
With...End With construct, 823-824
performance considerations, compacting databases, 1091-1093
personal, developing, 8
referencing, object variables, 950-952
scalability, 11
small businesses, developing, 8
source code, removing, 839
system performance, limiting factors, 9
troubleshooting, 361
ApplyFilter event, forms, 440
applying custom ribbons
to entire databases, 940-941
to forms or reports, 941-943
archiving records, Append queries, 543
arguments. See also parameters
Add method, 649
macro action arguments, 318-320
Open method, ConnectionString argument, 864
TransferDatabase method, 852
TransferSpreadsheet method, 854
TransferText method, 853
declaring, 605-607
dynamic arrays, 826
declaring, 607-609
fixed arrays, 605
parameter arrays, 617-618
passing as parameters, 609-610
scope, 605
As String keyword, 374
ascending sorts (query results), 145
assertions, debugging applications, 752
asterisk (*), queries (adding fields), 140
attached labels, 91
Attachment field type, 86
Auto Center property, reports, 286
Auto Data Tips (debugging), 745
Auto Indent feature, 395
Auto List Members option, 395
Auto Quick Info feature, 395
Auto Repeat property, controls, 242
Auto Resize property
forms, 231
reports, 286
Auto Syntax Check feature, 395
AutoCorrect, 480-481
AutoExec macro
creating, 341-342
example of adding, 343
AutoFill method, 959
AutoFilter method, 982
automatic error checking, 477-479
reports, 505-507
automation, 950
ActiveX automation, 949
controlling Access from other applications, 976-979
controlling from Access, 956-960
graphs, creating from Access, 961-962, 964-965
object models, 950
object types, 950
object variables, referencing applications, 950-952
objects, 950
Outlook, controlling from Access, 974-976
PowerPoint, controlling from Access, 970-973
Time and Billing application example, 979-983
type libraries, 950
Word, controlling from Access, 966-968
automation client, 950
automation objects, 953-954
binding, 954
creating, object variables, 953
Excel automation objects, closing, 960-961
methods, 954
properties, 953
automation servers, object models, 950
AutoNumber field type, 83
AutoNumber fields
Cascade Update Related Fields option, 131
upsizing to client/servers, 923
autonumber seed, changing (ANSI-92 extensions), 592
axes
exchanging in forms, 262
pivot tables, exchanging, 185
B
Back Color, 290
back ends
compared to back ends, 919
executing stored procedures, 921
tables, linking to front end application database, 919
Back Style, 290
backgrounds, alternating background colors for forms, 216-218
backing up
compacting databases, 1093
databases, 1099-1100
backslash (), input masks, 90
bang (!), 409
bas prefix, PDF:1132, PDF:1135
basUtils module, General Declarations section, 998-1003
batch updates, recordsets, 707-708
Before argument, Add method, 649
BeforeDelConfirm event, forms, 435
BeforeInsert event, forms, 432
BeforeUpdate event
controls, 443
forms, 433
BeginTrans method, transaction processing, 929-930
Between (query criteria operator), 147
Between Date And Date function, 149
binding
automation objects, 954
late, required functions, 955-956
black-box processing, 611
BOF property (ADO recordsets), 690-691
Bookmark property (ADO recordsets), 700-701
bookmarks, 826-827
adding, 394
Boolean expressions, toggling with Not function, 815
Border Color, 290
Border Style, 290
Border Style property
forms, 231
reports, 286
Border Width, 290
forms, 231
reports, 286
Bound Column property, combo boxes, 223
bound controls, 242
Bound Object Frame tool, 283
bound object frames, reports, 283
bound OLE objects, 466-467
In-Place activation, 466
breakpoints (debugging), 735
removing, 736
setting, 736-741
breaks, page breaks (inserting in reports), 293
brk prefix, PDF:1132
broken references, locating, 1102
bugs, avoiding, 728
builders, 1027
builder functions, preset arguments, 1030
creating, 1028
Load events, 1050-1052
ValidTextPolite function, 1049-1050
ValidTextRude function, 1050-1052
forms
designing, 1031-1032
Modal property, 1031
functions
preset arguments, 1029-1031
writing, 1028-1031
registering, 1032
Add-In Manager, 1036-1039
manual entries, 1032-1035
testing, 1035
types, 1027
built-in collections, 816-817
businesses, corporate environments (applications development), 8-9
byt data-type prefix, 1133
ByVal keyword, 987
C
expressions, 293
calculated fields
nested queries, 554
queries
calculating, 159
creating, 157-158
calculations, tables and, 119
Call Stack feature (debugging), 743-744
Call Stack window, 743-744
call stacks
error handling, 765
calling
functions
DLL functions, 993
invalid characters, 989
ordinal number references, 991
procedures, 354
Can Edit Subkey value (registry), 1033
Can Grow, 290
controls, 290
reports, 304
subreports, 300
Can Shrink, 290
controls, 290
reports, 304
subreports, 300
Cancel property, controls, 241
candidate keys, 117
Caption, 290
Caption property
query fields, 170
reports, 285-286
Caption property
fields, 91
forms, 229
command buttons, 290
labels, 290
default, 219
option groups, 226
Cascade Delete Related Fields, Delete queries (warnings), 543
Cascade Delete Related Records option, 131-133
Cascade Update Related Fields, Update queries (warnings), 541
Cascade Update Related Fields option, 130
cascading error effect, 765-766
case sensitivity
function names, 986
library names, 986
upsizing to client/servers, 925
Catalog object, Delete method, 871
CBF (Code Behind Forms), 347
cbo prefix, 1132
centralizing error handling, 760
certificates, creating self-signed certificates, 1111-1112
Change event, controls, 444
Change To command (Format menu), 830
ChangeCaption procedure, 450
ChangeCaption routine, 413
characters
fields, naming, 81
literals, input masks, 90
charts, reports with charts, 272
Check Box tool, 224
check boxes
forms, adding, 224
properties, Triple state, 224
CHECK keyword (ANSI-92 extensions), 591
checkboxes, forms (adding), 224
chk prefix, 1132
Choose Builder dialog box, 1047
Class modules, 347, 484, 637, 652-654, 656, 658-659, 661-662, 664-667, 669, 791, 794. See also modules
creating, 638-641
instantiating objects, 640
methods, adding, 639
properties, adding, 639
Property Get routine, 640-642
Property Let routine, 640-642
custom collections, 648-651
adding items to, 649
creating, 649
deleting items, 650
looping through, 650
referencing items, 650
custom error handler class, 790-791, 794
ErrorProcess method, 791-793
LogError routine, 793-794
Property Get routine, 793
Property Let routine, 793
Standard Access routine, 790
data access class, 656, 658-661, 663-665
AddNew method, 664
Delete method, 665
Edit method, 664-665
Initialize event, 658
Load event, 659
MoveNext method, 659-661
MovePrevious method, 661-662
Property Get routine, 656-658
Property Let routine, 656-658
Save method, 663-664
Scatter method, 658-659
file information class, 653-656
FullFileName property, 652-654
GetDrive routine, 653-655
GetName routine, 653-655
GetPath routine, 653-655
manipulating instances (collections), 655-656
property declarations, 652
Initialize event, 643
multiple class instances, creating, 642-643
object orientation, 637-638
Property Set, 642
system information class, 666-669
Initialize event, 668-669
Private variables, 666-667
Property Get routine, 667-668
Property Let routine, 667-668
type structures, 666-667
Terminate event, 644
classes, 638
custom events, adding, 651-652
hierarchies, building, 646
interfaces, sharing, 648
properties, adding Parent property, 647-648
clauses, SQL
ALL, 572
FROM, 568
GROUP BY, 573
HAVING, 573
JOIN, 570-571
ORDER BY, 569
SELECT, 567
WHERE, 568-569
clearing Immediate window, 732
clearing errors, 764
Click event
controls, 446
forms, 439
client/server technology
benefits of, 843
considerations for migrating to, 845
converting to, 842-843
costs of, 843
client/servers, 914
Access, integrating, 921
applications, suitability of Access, 10
compared to Access, 914-915
evaluating needs, 918
field names, compatibility, 81
versus file servers, 9
system performance
application types, 917
backup/recovery capability, 917
network demands, 917
security, 918
user base, 916
terminology, 922
upsizing, 923-925
AutoNumber fields, 923
case-sensitivity, 925
preparation, 925
properties, 925
relationships, 924
reserved words, 925
security, 924
tables, 923
validation rules, 924
VBA, 925
clients, automation clients, 950
Close Button property, reports, 287
Close event
forms, 438
reports, 494
Close keyword, 628
CloseExcel routine, 960
closing
Excel automation objects, 960-961
forms, Form event sequence, 442
windows, designing forms, 197
cmd prefix, PDF:1132
cmdFinish1 command button, 1042
cmdFinish2 command button, 1042
cmdSave command button (frmClients form), 396
cmdUndo command button (frmClients form), 396
cnt object-naming prefix, PDF:1133-1134
cnts object-naming prefix, PDF:1133-1134
Codd, E. F., Dr., 117
code
compacting databases, 1096-1099
compiling, Compile On Demand feature, 629-630
debugging, 728
Auto Data Tips, 745
breakpoints, 735-741
Call Stack window, 743-744
Immediate window, 729-730, 734
Locals window, 744
naming standards, 728
Option Explicit statement, 728
potential problems, 751-752
quick watches, 745
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
strong-typing variables, 728
variable scoping, 728
Watch expressions, 745
watches, 735
importing external data, 852-854
TransferDatabase method, 852-853
TransferSpreadsheet method, 853-854
TransferText method, 853
library database references, VBA, 1021
linking external data, 859-863
connection information, providing, 860-861
creating links, 861-863
optimizing, 811
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
organizing modules, 829
specific object variables, 812-813
stored queries, 827-828
true/false evaluations, 819-820
unused code, 821
variables, 827
variant variables, eliminating, 811
With...End With construct, 823-824
writing to create documentation, 1088-1089
Code Behind Forms (CBF), 347
code modules
exporting, 630-631
structuring (library databases), 1012-1013
Code window (VBA), 388
splitting, 393
CodeData object, 423
CodeDB function, 1013
CodeProject object, 423
coding library databases, 1013
Collection object, custom collections, 626-627
AllForms collection, 420
AllMacros collection, 421
AllModules collection, 421
AllQueries collection, 423
AllReports collection, 421
AllTables collection, 422
compared to objects, 412
custom collections, 648-651
adding items to, 649
creating, 649
deleting items, 650
looping through, 650
referencing items, 650
For...Each construct, 413
Forms collection, 418-419
manipulating multiple instances of file information class, 655-656
Modules collection, 420
Printer, 504
referencing objects, 407
Reports collection, 419
Column Count property, combo boxes, 223
Column Width property, combo boxes, 223
columns
adding to forms, 212-213
client/servers, 922
queries, selecting, 567
removing from forms, 213-214
COM (Component Object Model), 950
Combo Box tool, 220
Combo Box Wizard, 220
combo boxes
adding/removing items at runtime, 474-475
converting text boxes to, 227-228
converting to list boxes, 228
forms, adding, 219-223
labels, 222
NotInList event, 472-473
pop-up forms, 473-474
properties, 222
Command Button Wizard, 244-245
inefficient code generation, fixing, 396-398
command buttons
adding to forms, 244-245
captions, 290
enabling and disabling, 425-428
enabling/disabling, 426-427
hyperlinks, adding, 251
record source, saving changes to, 396
records, undoing changes to, 396
Command object
ADO object model, 678-679
declaring, 678
command tabs, ribbons (new features), 30-33
commands
Add-Ins menu, Add-In Manager, 1038
adding to ribbons, 944-945
Debug menu
Compile, 828
Set Next Statement, 742-743
Edit menu, New, 1034
File menu, Export, 104-109
Format menu, Change To, 830
New menu, 1034
Start menu, Run, 1033
Tools menu
Add-Ins, 1038
Relationships, 124
VBA, viewing code, 387
View menu, Object Browser, 405
Comment Block tool (VBE), 364
comments
adding to macros, 336-337
VBA, 363-364
CommitTrans method, transaction processing, 929-930
Compact command-line option, 1095
Compact Database Into dialog box, 1094
Compact on Close environmental setting, 1095-1096
Compact utility, 803
CompactDatabase method, JetEngine object, 1096-1098
compacting databases, 803, 1091-1099
with code, 1096-1099
Compact on Close environmental setting, 1095-1096
shortcut icons, creating, 1095
user interface, 1093-1095
CompactRepair method, 417
Application object, 1098-1099
compatibility, database conversion, 1100-1101
compilation objects, 629
Compile command (Debug menu), 828
Compile On Demand feature (VBA), 629-630
compiler constants, 367
compiling, 629
library databases, 1013
modules, 828
queries, 553
compiling code, Compile On Demand feature (VBA), 629-630
Complete Word feature, modules, 386
Component Object Model (COM), 950
composite keys, 117
compressing drives, 804
computer consulting firm example, application design, 72-73
conditional formatting, 229
forms, 228-229
Conditional Formatting dialog box, 228
Conditional If control structure, 367-368
Connection Object, creating, 675
connection information (linking external data), 860-861
Connection object
ADO object model, 672-675
BeginTrans method, 929-930
CommitTrans method, 929-930
declaring, 672
RollbackTrans method, 929-930
connection problems (external data), 873
Connection string properties, 673
ConnectionString argument (Open method), 864
compiler, declaring, 367
declaring, 991
defining, 602-604
scope, 603-604
errors, 382
functions, 991
Intrinsic, 381-383
intrinsic constants, 602-604
examples, 632-633
naming, 382
naming conventions, 603-604
NotInList event, 473
Object Browser, 379-380
Public, 603
symbolic, 381-382
Symbolic constants, 602
system-defined constants, 602
values, errors, 603
VBA, 380
Object Browser, 604
constructs
For...Each, 413
For...Next, 371
With...End With, 412
With...End With construct, 823-824
Contents tab (Database Properties dialog box), 1080
context-sensitive help, VBA, 391
contextual command tabs, ribbons (new features), 34
Continuous forms
creating, 451-453
subforms, 453
Control Box property, reports, 286
control builders, 1027
Control events, 443
AfterUpdate, 443
BeforeUpdate, 443
Change, 444
Click, 446
DblClick, 447-448
Enter, 445
Exit, 445
GotFocus, 445
KeyDown, 448
KeyPress, 449
KeyUp, 449
LostFocus, 446
MouseDown, 448
MouseMove, 448
MouseUp, 448
NotInList, 444-445
sequence, 449
Updated, 444
Control properties, forms, 237, 241-242
Control Source property, 292
combo boxes, 223
control sources, adding expressions, 243
control structures (VBA), 364
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
breaking, 371
Select Case statement, 368-369
With...End With statement, 372
Control Wizard, combo boxes (adding to forms), 220
controlling printers, 503
controls
Allow AutoCorrect property, 241
Auto Repeat property, 242
bound, 242
calculated controls, expressions, 293
Cancel property, 241
check boxes, 224
checkboxes, 224
combo boxes, 219-223
converting text boxes to, 227-228
converting to list boxes, 228
conditional formatting, 229
ControlTip Text property, 241
ControlType property, 414-415
data-bound controls, 91
default properties, optimization and, 831
determining type, 414-415
Enter Key Behavior property, 241
Format property, 238
forms, 218
Data Properties, 239-240
Format Properties, 236-239
labels, 219
morphing controls, 227-228
Other Properties, 240-242
Help Context ID property, 241
hyperlinks, adding, 251
IME (Input Method Editor) property, 242
labels, associating/disassociating, 219
list boxes, 223
converting combo boxes to, 228
moving independently of label, 200
Name property, 240
naming, conflicts, 292
option buttons, 224
option groups, 224-226
availability, 236
Back Color, 290
Back Style, 290
Can Grow, 290
Can Shrink, 290
Data, 292
Font Color, 291
Font Italic, 291
Font Name, 291
Font Size, 291
Font Underline, 291
Font Weight, 291
Format, 290-291
Name, 292
Other, 292
reports, 289-291
Running Sum, 292
Tag, 292
reports, 282-284
bound object frames, 283
image controls, 284
labels, 282
lines, 282
rectangles, 283
text boxes, 282
unbound object frames, 283-284
Scroll Bars property, 237
Special Effect property, 237
Status Bar Text property, 241
subform controls, referring to, 476-477
Tab controls
adding to forms, 456
properties, changing, 456-457
Tab Index property, 241
Tab Stop property, 241
Tag property, 242
text boxes, 219
converting to combo boxes, 227-228
transparent, 237
unbound, 242
Vertical property, 241
ControlTip Text property, controls, 241
ControlType property, 414-415
ConvertAccessProject method, 418
Application object, 1100-1101
converting
to client/server technology, 842
linked tables to local tables, 871-872
macros to VBA code, 339-341
CopyFromRecordset method, 982-983
copying
macro actions, 331
records, controlling user access, 231
corporations, developing applications, 8-9
counter fields, resetting, 1092
CREATE INDEX statement, SQL, 584-585
CREATE TABLE statement, SQL, 584
CreateControl function, 1044
CreateCustomForm function, 1041-1042
CreateExcelObj subroutine, 956
CreateForm function, 1044
CreateObject function, late binding, 955-956
CreateObject function for Creating Late-bound Excel Instance, 955
CreateRecordset function, 964-965
CreatExcelObj function, 957
creating
builders, 1028
databases, new (from scratch), 78-79
forms, 193
Design view, 196
library databases, 1012-1013
new databases from scratch, 78-79
new forms, Form Wizard, 193
pivot tables
from forms, 256-259
Primary Key indexes, 98
tables, 79-81
criteria
date criteria, refining query results, 149-150
function results as criteria for queries, 586-587
queries, Like, 554
selection criteria, refining query results, 146-149
specifying at runtime (Parameter queries), 172-175
Cross-tabulation reports, 270
Crosstab queries, 556-557
columns, fixed headings, 562
creating
with Crosstab Query Wizard, 557, 559
Design view, 559-560
fixed column headings, creating, 561
parameters, 562
reports, creating, 523, 525-530
Crosstab Query Wizard, 557, 559
crosstab reports, multifact crosstab reports (creating), 533-536
ctl prefix, PDF:1132
ctls prefix, PDF:1132
cur data-type prefix, PDF:1133
Current event, forms, 431
CurrentData object, 422-423
CurrentDB function, 1013
CurrentProject object, 420-421
cursors
batch updates, 707-708
Recordset objects, selecting location, 686-687
CursorType parameter, ADO recordset types, 680-682
custom collections, 625-628, 648-651
accessing items, 627
adding items, 626-627
adding items to, 649
deleting items, 650
iterating through items, 628
looping through, 650
referencing items, 650
removing items, 627
custom error handler class (class modules), 790-791, 794
LogError routine, 793-794
Property Get routine, 793
Property Let routine, 793
Standard Access routine, 790
custom events, adding to classes, 651-652
custom methods, creating, 489
custom properties, creating, 484-488
PropertyGet routine, 486-488
PropertyLet routine, 486-488
Public variables, 484-486
custom ribbons, building, 841-842
Custom tab (Database Properties dialog box), 1081
customizing
Quick Access toolbar, 38, 40-41
ribbons, 935-936
enabling display of system errors, 937
showing system tables, 936-937
USysRibbons table, 938
USysRibbons table, adding data to, 939-940
Visual Basic Editor (VBE), 394-395, 399
coding options, 394-395
general options, 395
Currency field type, 85
Cycle, 289
Cycle property, forms, 235
D
DAO (Data Access Objects), 671, 922
Errors collection, 769-770
naming conventions, PDF:1133-1134
records, deleting, 722-723
recordsets, RecordCount property, 692
dat data-type prefix, 1133
data
exporting
to SharePoint Server 2007, 883-884
to SharePoint sites, 885-890
external data. See external data
Attachment, 86
AutoNumber, 85
Currency, 85
Date/Time, 84
Hyperlink, 87
Memo, 84
Number, 84
OLE Object, 86
selecting, 82-84
Text, 84
Yes/No, 86
pivot tables, filtering, 183
publishing to SharePoint sites, 891-896
tables
saving in HTML format, 104-107
saving in XML format, 107-109
data access class (Class modules), 656, 658-661, 663-665
AddNew method, 664
Delete method, 665
Edit method, 664-665
Initialize event, 658
Load event, 659
MoveNext method, 659-661
MovePrevious method, 661-662
Property Get routine, 656-658
Property Let routine, 656-658
Save method, 663-664
Scatter method, 658-659
Data Access Objects. See DAO
data analysis, application development, 24-27
data entry forms, recordset methods, 719-724
Data Entry property, forms, 234
data locking, 913
data normalization, 115
Data properties
controls, Running Sum property, 292
form controls, 239-240
forms, 233-234
reports, 287
data structures, optimization, 808-809
data types
dBASE, mapping, 873
optimization, 809
variables, 359
data-bound control, 91
Database Documenter, 1074, 1082
documentation output, 1085-1086
launching, 1081-1082
Object Definition window, 1082
database files, linking external data, 858
Database locking, 913
Database Locking Mode property (Connection strings), 673
database objects
Database window, viewing icons, 12
saving as XML, 1066-1068
splitting, 835-836
Database Password property (Connection strings), 673
database projects, properties, 631-632
Database Properties dialog box, 1079-1081
Contents tab, 1080
Custom tab, 1081
General tab, 1079
Statistics tab, 1080
Summary tab, 1079
Database Splitter, 877
linking external data, 854-856
Database Splitter Wizard, 912
Database to Compact From dialog box, 1093
Database window, 12
Database window objects, naming conventions, PDF:1135
databases
backing up, 1099-1100
behavior when trusted and untrusted, 1123
compacting, 554, 803, 1091-1099
Compact on Close environmental setting, 1095-1096
shortcut icons, creating, 1095
user interface, 1093-1095
with code, 1096-1099
converting to other version formats, 1100-1101
creating, 75-76
from scratch, 78-79
from SharePoint lists, 884
with templates, 76-78
default location, 1095
defined, 11
denormalization, 120
design issues, 115
benefits, 117
goals, 117
history, 117
integrity rules, 121-122
normalization rules, 118-120
documentation, 1081
Database Documenter, 1081-1085
Database Properties dialog box, 1079-1081
writing code for documentation, 1088-1089
encrypting, 1096-1099
forms, documenting (Database Documenter), 1075-1077
front-ends, 847
Jet engine, converting from old versions, 1096-1098
library databases, see library databases, 1011
macros, documenting (Database Documenter), 1078
modules, documenting (Database Documenter), 1078
naming conventions, 79
normalization, 115-116
first normal form, 118
second normal form, 119
third normal form, 119-120
Object Dependency feature, invoking, 1086-1088
passwords, 867-869
performance considerations
application types, 917
backup/recovery capability, 917
network demands, 917
security, 918
user base, 916
properties, documentation, 1081
queries, documenting (Database Documenter), 1075-1076
re-creating, 53-56
relationships, types of, 122
renaming, compiling and, 829
reports, documenting (Database Documenter), 1078
specifications/limitations, 110
tables
adding with ADO code, 715-716
deleting with ADO code, 717
documenting (Database Documenter), 1074-1075
relationships, establishing, 717-718
specifications/limitations, 110-111
trusting, 1106-1107
date criteria, refining query results, 149-150
Date Grouping, 288
Date( ) function, 92
Date() function, 149
Date/Time field type, 83-84
DateAdd function, 378
DateCreated property, 424
DateDiff function, 378
DateModified property, 424
Day() function, 149
db object-naming prefix, 1133-1134
dBASE files, 872-873
mapping dBASE data types, 873
dbe object-naming prefix, PDF:1133-1134
dbl data-type prefix, PDF:1133
DblClick event
controls, 447-448
forms, 439
dbs object-naming prefix, PDF:1133-1134
dcm prefix, PDF:1132
DDL (client/servers), 922
Deactivate event, forms, 438
Debug menu commands
Compile, 828
Set Next Statement, 742-743
Debug toolbar, displaying, 730
Debug.Print statements, 361, 734, 739
debugging. See also troubleshooting
library databases, 1022-1023
tips, 752-753
debugging applications
assertions, 752
Auto Data Tips, 745
avoiding bugs, 728
breakpoints, 735
removing, 736
setting, 736-741
Call Stack window, 743-744
Immediate window, 729-730, 734
clearing, 732
printing values to, 734
setting values of variables/properties, 730, 732
testing functions, 733
testing subroutines, 733
testing values of variables/properties, 730
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
adding, 746-748
breaking when expressions are true, 748-749
breaking when expressions change, 749
editing, 748
watches, 735
quick watches, 745
Decimal Places, 290
declarations
automation objects, 953
Command objects, 678
Connection objects, 672
disambiguation, 672
Recordset objects, 676
variables, memory usage considerations, 674
declaring
arrays, 605-607
dynamic arrays, 607-609
compiler constants, 367
constants, 991
DLL functions, 985-986
procedures
Class modules, 355
private, 356
types, 992-993
user-defined types, 600
variables, 358
modules, 348
Option Explicit statement, 349
performance considerations, 359
decomposition, 119
DEFAULT keyword (ANSI-92 extensions), 590
default properties, 410
Default Value property (fields), 91-92
Default View property (forms), 230
defining constants, 602-604
scope, 603-604
Definition feature, modules, 387
defragmenting
compacting databases, 1092
hard disks, 803
Delete event, forms, 435
Delete method
Catalog object, 871
data access class, 665
Delete queries, creating, 542
DELETE statement, QL, 582-583
DeleteSetting function, 994
deleting
breakpoints, 736
custom collection items, 650
fields, tables, 80
fields from query grid, 142-143
files from disk, 760
macro actions, 329-330
records
Action queries, 542
controlling user access, 231
DAO, 722-723
records from recordsets (ADO), 712-713
relationships, 128
tables with ADO code, 717
delimiters, strings, 697-699
denormalization, 120
denormalizing tables, 808
dependencies
object dependencies, viewing, 109-110
objects, viewing, 110
derived keys, 116
Description property
forms, 1075
query fields, 170
reports, 1078
tables, 101
Description Subkey value (registry), 1033
design issues, 115
benefits, 117
goals, 117
history, 117
integrity rules, 121-122
normalization rules, 118-120
design phase, application development, 24-27
Design view
Crosstab queries, 559-560
forms
creating, 196
designing, 18
reports, creating, 278
tables, viewing, 14
designing
applications
computer consulting firm example, 72-73
options, 919
builder forms, 1031-1032
Load events, 1050-1052
ValidTextPolite function, 1049-1050
ValidTextRude function, 1050-1052
forms
Design view, 18
shortcut keys, 198
Time and Billing application, 263-268
toolbars, 196
windows, 197
menu add-ins, 1045
properties, setting, 402
queries, Query Design window, 17
tables
from scratch, 79-81
Time and Billing application, 111-113
wizards, 1039
designing tables, examples, 107
detail (reports), displaying, 516-517
Detail reports, 270
Detail section
forms, 192
reports, 274
Detail Section Format event, reports, 527-529
Detail Section Print event, reports, 529
detecting objects, broken references, 1102
developing applications, 835-837
Access as a front end, 842-845
data analysis, 24-27
design phase, 24-27
forms, basing on queries/embedded SQL statements, 837
implementation stage, 28
maintenance activities, 28
prototype stage, 27
reports, basing on queries/embedded SQL statements, 837
runtime version, distribution, 839-841
splitting database objects, 835-836
task analysis, 24
testing stage, 27
development environment, RAM recommendation, 803
dialog boxes, 460
About, 997
Add Watch, 747
Add-In Manager, 1038
Choose Builder, 1047
Compact Database Into, 1094
Conditional Formatting, 228
Database Properties, 1079, 1081
Database to Compact From, 1093
Documenter, 1081
Edit DWORD Value, 1034
Edit String, 1034
Edit Watch, 748
FileDialog object, 463-464
Find, 390
Import, 1037
Import Objects, 1037
input boxes, 462-463
InputBox function, 462-463
Insert Hyperlink, 103
Link, 856
message boxes, 460-462
MsgBox function, 460-462
Modal property, 235
New Object, 1045
New Query, 138
Object Browser, 405
Object Properties, 1075
Open, 1039
Parameters, 588
Performance Analyzer, 808
Pop Up property, 235
Print Form Definition (Database Documenter), 1084
Print Query Definition (Database Documenter), 1084
Print Table Definition (Database Documenter), 1083
Project Properties, 631
Query Parameters, 173-175
Replace, 391
Runtime Error, 750
Save Backup As, 1099-1100
Show Table, 139
self join queries, 564
Tab Order, 204
Zoom, 243
Dialog forms, creating, 459-460
Dim keyword, custom collections, 649
Dim statement, 953
variables, 359
Dim statements, 821
memory usage considerations, 674
directives, VBA (#Const), 367
Dirty event, forms, 434
disabling
command buttons, 425-428
Sandbox mode
in Windows Vista, 1123-1124
in Windows XP or Windows Server 2003, 1124-1125
Snap to Grid feature, 281
disambiguation (object declarations), 672
disk space, external data, 874
Display as Hyperlink, 291
display of system errors, customizing ribbons, 937
Display on SharePoint Site, 288
displaying
Debug toolbar, 730
detail (reports), 516-517
error descriptions, 759
error numbers, 759
errors, errors collection, 769-770
forms, in PivotTable view, 257-258
overlapping windows, tabbed documents, 46-47
pivot tables, from forms (Pivot Table view), 256-257
summary (reports), 516-518
distributing
Access databases, 1110-1111
applications, runtime version, 839-841
Dividing Lines property, forms, 232
DLLs (dynamic link libraries), 985
constants, 991-993
functions
aliasing, 989-991
calling, 993
declaring, 985-986
passing parameters, 987-989
types, 992-993
Do loops, 369-370
doc object-naming prefix, 1133-1134
dockable windows, 396
adding report sections at runtime, 501
examples, 632-633
importing external data, 852-854
TransferDatabase method, 852-853
TransferSpreadsheet method, 853-854
TransferText method, 853
macros, executing, 374
OpenForm method, 375
docs object-naming prefix, 1133-1134
documentation
benefits, 1073
databases, 1081
Documenter dialog box, 1081
documenting
macros, adding comments, 336-337
tables, 81
documenting databases
Database Documenter, 1081-1083, 1085
documentation output, 1085-1086
forms, 1075-1077
macros, 1078
modules, 1078
Object Definition window, 1082
options, 1083-1085
queries, 1075-1076
reports, 1078
tables, 1074-1075
Database Properties dialog box, 1079-1081
writing code for documentation, 1088-1089
documents
HTML documents
importing, 874-876
linking, 877
mail merge (Word), 966-968
tabbed documents, 44-46
displaying overlapping windows, 46-47
domains, 116
drill-down, pivot table data, 184
drivers, ISAM drivers (Windows registry keys), 866
drives, 804
DROP INDEX statement, SQL, 585
DROP TABLE statement, SQL, 586
DSN keyword, 867
DWORD Value command (New menu), 1034
dynamic arrays, 826
declaring, 607-609
dynamic link libraries. See DLLs
dynasets, tables, 16
E
Edit DWORD Value dialog box, 1034
Edit menu commands, New, 1034
Edit method, data access class, 664-665
Edit Relationships dialog box, 16, 125
Edit String dialog box, 1034
Edit Watch dialog box, 748
editing
project properties, 631
Watch expressions, 748
Editor Format Tab, 395
editors, Registry Editor (invoking), 1033
emailing, errors, 780-781
embedded macros, 315
creating, 332-333
embedded objects, In-Place activation, 466
embedded SQL statements, basing forms/queries on, 837
embedding SQL statements, 250-251
Empty value, Variant variables, 618-619
enabling command buttons, 425-428
encryption, CompactDatabase method.
Application object, 1098-1099
JetEngine object, 1096-1098
enforced referential integrity, 155
Enter event, controls, 445
Enter Key Behavior property, controls, 241
entity integrity, 122
entity integrity rules, 121-122
enumerated types, Intellisense and, 644-646
environment, 802
development, RAM recommendation, 803
environmental settings, Compact on Close, 1095-1096
EOF property (ADO recordsets), 690-691
equal sign (=), query criteria operator, 146
Err object, 766-767
properties, 766
Raise method, 767-769
generating errors, 767-768
user-defined errors, creating, 768-769
err object-naming prefix, PDF:1133-1134
Error Checker, 479
error descriptions, displaying, 759
Error event
forms, 440
reports, 495
error events, 796
error forms, 784
creating, 782-784
printing, 785
error handling, 755-757
call stacks, 765
cascading error effect, 765-766
centralizing, 760
clearing errors, 764
default error message, 756
default handler, returning to, 759
distributing applications, 841
Err object, 766-767
properties, 766
Raise method, 767-769
error descriptions, displaying, 759
error numbers, displaying, 759
examples, 798-799
Exit Sub statement, 759
generic error handlers
creating error forms, 782-784
ErrorHandler function, 772-774, 778-780
LogError routine, 774-776
On Error routine, 770-771
printing error forms, 785
type structure declaration, 772
implementing, 755-757
On Error Goto 0 statement, 759-760
On Error Goto statement, 758-759
On Error Resume Next statement, 760
On Error statements, 758
personal error handling, preventing, 786
response options, 760-764
Resume Next statement, 762-763
Resume statement, 761-762
Resume [LineLabel] statement, 763-764
Time and Billing application, 798-799
error messages
referential integrity, 129
relationships, 132
error numbers, displaying, 759
ErrorHandler function, 778-780
generic error handlers, 772-774
ErrorProcess method, custom error handler class, 791-793
errors
automatic error checking, 477-479
reports, 505-507
clearing, 764
constants, 382
values, 603
determining appropriate response to, 776-778
emailing errors, 780-781
functions, 986
generating, 767-768
Immediate If (IIf) statements, 366
Invalid add-in entry message, 1036
listing, 797
logging, 774-776
logic errors, 728
responding to, 778-780
runtime errors, 750-751
user-defined, creating, 768-769
Errors collection, 769-770
errs object-naming prefix, 1133-1134
event procedures, 347
creating, 352
modules, 350
events, 404
Control events, 443
AfterUpdate, 443
BeforeUpdate, 443
Change, 444
Click, 446
DblClick, 447-448
Enter, 445
Exit, 445
GotFocus, 445
KeyDown, 448
KeyPress, 449
KeyUp, 449
LostFocus, 446
MouseDown, 448
MouseMove, 448
MouseUp, 448
NotInList, 444-445
sequence, 449
Updated, 444
custom, adding to classes, 651-652
Detail Section Format, reports, 527-529
Detail Section Print, reports, 529
error events, 796
Filter event, forms, 464-465
Form events, 431
Activate, 438
AfterDelConfirm, 435
AfterInsert, 432
AfterUpdate, 434
ApplyFilter, 440
BeforeDelConfirm, 435
BeforeInsert, 432
BeforeUpdate, 433
Click, 439
Close, 438
Current, 431
DblClick, 439
Deactivate, 438
Delete, 435
Dirty, 434
Error, 440
Filter, 440
GotFocus, 438
KeyDown, 439-440
KeyPress, 440
KeyUp, 440
Load, 436
LostFocus, 439
MouseDown, 439
MouseMove, 439
MouseUp, 439
Open, 435
order of occurrence, 433
Resize, 436
sequence, 441-442
Timer, 441
Undo, 434
Unload, 437
Group Header Format event, 534-535
Initialize, 643
Initialize event
data access class, 658
system information class, 668-669
Load event, data access class, 659
No Data event, 831
NotInList event, combo boxes, 472-473
Page Footer Format, reports, 532
Page Header Format, reports, 527
Page Header Format event, 533
report events, 493-496
Close, 494
Error, 495
manipulating sections, 501
NoData, 494-495
order of, 496
Page, 495
Report Footer Format, reports, 530-532
Report Header Format, 526-527, 531
report section events, 496-497, 500
Format, 496-498
order of events, 500
Retreat, 500
Section events, 443
Terminate, 644
Excel
automation objects, closing, 960-961
controlling from Access, 956-960
graphs, creating from Access, 961-962, 964-965
exchanging
axes in forms, 262
pivot table axes, 185
excluding fields, from queries, 166-167
ExclusiveAsyncDelay setting (Windows registry), 928
executing
macros, 21
from ribbons, 946-947
queries, 16
stored procedures on back ends, 921
Exit event, controls, 445
Exit Sub statement, error handling, 759
explicit references
creating in library databases, 1017-1020
limitations, 1018
RefLibPaths key, 1020
explicit transaction processing, 927
implementing, 929-930
Export command (File menu), 104-109
exporting
code modules, 630-631
data
to SharePoint Server 2007, 883-884
to SharePoint sites, 885-890
form modules, 630-631
Expression Builder, 243. See also builders
building expressions in queries, 160-161
expression builders, 1027
expressions
adding to control sources, 243
Boolean expressions, toggling with Not function, 815
building in queries (Expression Builder), 160-161
calculated controls, 293
forms, 243
Null values, converting to zero, 167
queries, 554
Rushmore, 555
Watch expressions, 745
adding, 746-748
breaking when expressions are true, 748-749
breaking when expressions change, 749
editing, 748
Extensible Markup Language. See XML
external data, 848
compatibility, 847
dBASE files, 872-873
mapping dBASE data types, 873
defined, 847
Database Splitter, 877
refreshing links, 878-881
importing, 848-850
DoCmd object, 852-854
user interface, 850-851
linking, 848-849, 854, 862-863, 912
Database Splitter, 854-856
Database Splitter Wizard, 912
performance considerations, 874
refreshing links, 869-871, 878-879, 881
removing links, 869-871
to Access tables, 856-857
to database files, 858
user interface, 856-858
user interfaces, 854
with code, 859-863
links, refreshing, 877-880
passwords, 867-869
storage, 847
text data, 873
troubleshooting, 873-874
connection problems, 873
disk space, 874
external tables
connection information, providing, 864
OpenDatabase method, 864-865
extracting signed packages, 1113-1114
F
f data-type prefix, 1133
Fast Laser Printing, 289
forms, 236
fdlg object-naming prefix, 1136
Field List properties, queries, 171
Field List window, availability, 233
field properties
propagating, 481-483
for reports, 507-509
queries, 170
Field Size property (fields), 88
field types, Hyperlink, 103-104
fields (tables), 82-84
adding
to queries, 139-141
to reports, 278
AutoNumber, Cascade Update Related Fields option, 131
behaviors, modifying, 170
calculated fields (queries)
creating, 157-159
nested queries, 554
changing location on query grid, 143-144
combo boxes, setting width, 221
counter, resetting, 1092
data types, selecting, 82-84
Date( ) function, 92
foreign keys, 95
forms
adding, 198-199
adding multiple, 198
selecting, 194
indexing
queries, 554
selecting field types, 82
inserting in queries, 143
maximum number allowed, 123
multifield indexes, creating, 96
multivalued fields
creating, 65-69
naming, 81
normalization, 25
properties, 87
Allow Zero Length, 95-96
Caption, 91
Default Value, 91-92
Field Size, 88
Format, 88-89
Indexed, 96-98
Input Mask, 89-90
Required, 94-96
Unicode Compression, 97
Validation Rule, 92-94
Validation Text, 94
queries
excluding fields from output, 166-167
performance considerations, 554
removing from query grid, 142-143
searching, indexes, 96-97
selecting, reports, 275
size considerations, referential integrity, 129
sorting
query results, 145-146
selecting field types, 82
space requirements, 82
subforms, order of fields, 249
tables
defining, 79
deleting, 80
descriptions, 80
inserting, 80
selecting types, 81
types, 79
types
:Attachment, 86
AutoNumber, 85
Currency, 85
data, 82
Date/Time, 84
Hyperlink, 87
Memo, 84
Number, 84
OLE Object, 86
Text, 84
Yes/No, 86
updating queries, 539
validation rules, adding, 93-94
values, lookups (Lookup Wizard), 98-100
file information class (Class modules), 653-656
FullFileName property, 652-654
GetDrive routine, 653-655
GetName routine, 653-655
GetPath routine, 653-655
manipulating instances (collections), 655-656
property declarations, 652
File menu commands, Export, 104-109
file servers
applications, installing, 910
versus client/servers, 9
FileDiaolg object, 463-464
files
ACCDE files, 829
ADP files, 56
dBASE files, 872-873
mapping dBASE data types, 873
deleting, from disk, 760
importing, supported file formats, 850
INF files, 872
linking, supported file formats, 850
low-level file handling, 628-629
opening, supported file formats, 850
text files, importing, 630
WZMAIN80.MDE, importing, 1037
XML files, importing, 1068-1069
FillCells subroutine, 958
Filter event, forms, 440, 464-465
Filter On Load property, 288
forms, 234
queries, 172
tables, 102
Filter property (ADO recordsets), 695
filtering
data, pivot table data, 183
forms, 464-465
pivot table data, forms, 261
recordsets, 695
FilterOn property, 513-514
filters, forms, 234
find and replace feature, VBA, 390-391
Find dialog box, 390
Find method (ADO recordsets), 696-699
finding records in recordsets, 696-699
first normal form, 118
fixed arrays, 605
fld object-naming prefix, 1133-1134
flds object-naming prefix, 1134
FlipEnabled routine, 414-415
fmnu object-naming prefix, 1136
fmsg object-naming prefix, 1136
folders, My Documents, 1095
Font Color property, controls, 291
Font Italic property, controls, 291
Font Name property, controls, 291
Font Size property, controls, 291
Font Underline property, controls, 291
Font Weight property, controls, 291
Footer section, forms, 192
footers, 274. See also Page Footers
adding at runtime (reports), 501
For Each...Next loops, 816-818
For Each...Next, 817
For Each...Next statement, 372-373
For...Each construct, 413
For...Each loops, iterating through collection items, 628
For...Next construct, 371
Force New Page property, reports, 293, 303
Fore Color, 290
foreign key fields, 95
foreign keys, 115-116
referential integrity and, 121
foreign keys (client/servers), 922
Form and Report Class modules, 347
Form Design toolbar, 196
Form Design window, 196, 198-200, 202-204
Form events, 431
Activate, 438
AfterDelConfirm, 435
AfterInsert, 432
AfterUpdate, 434
ApplyFilter, 440
BeforeDelConfirm, 435
BeforeInsert, 432
BeforeUpdate, 433
Click, 439
Close, 438
Current, 431
DblClick, 439
Deactivate, 438
Delete, 435
Dirty event, 434
Error, 440
Filter, 440
GotFocus, 438
KeyDown, 439-440
KeyPress, 440
KeyUp, 440
Load, 436
LostFocus, 439
MouseDown, 439
MouseMove, 439
MouseUp, 439
Open, 435
order of occurrence, 433
Resize, 436
sequence, 441-442
closing forms, 442
key presses, 442
mouse actions, 442
moving between forms, 442
opening forms, 441
sizing forms, 442
Timer, 441
Undo event, 434
Unload, 437
form layouts
columns
adding, 212-213
removing, 213-214
formatting, 210
moving, controls in, 210-211
removing, 214-215
form modules, exporting, 630-631
Form property, 416
Form view, viewing forms, 18
Form Wizard
customizing, 1040-1044
forms, creating, 193-195
one-to-many forms, creating, 246-247
starting, 195
Format, Format properties, 290
Format event, report sections, 496-497
Format function, 376
Format menu, spacing forms, 203
Format menu commands, Change To, 830
Format properties
Back Color property, 290
Back Style property, 290
Can Grow property, 290
Can Shrink property, 290
Font properties, 291
Scroll Bars, 237
Special Effect, 237
fields, 88-89
form controls, 236-239
forms, 231
Allow Datasheet View, 231
Allow Form View, 233
Allow PivotChart View, 231
Allow PivotTable View, 231
Auto Resize, 231
Border Style, 231
Dividing Lines, 232
Moveable, 232
Navigation Buttons, 232
Orientation, 233
Picture properties, 231
Record Selectors, 231
Scroll Bars, 232
Width, 231
query fields, 170
reports, 285
Auto Center property, 286
Auto Resize property, 286
Border Style, 286
Caption property, 285-286
Close Button, 287
Control Box, 286
Grid X property, 287
Grid Y property, 287
Grp Keep Together, 286
Grp Keep Together property, 286
Layout for Print property, 287
Min Max Buttons, 286
Page Footer, 286
Page Footer property, 286
Page Header, 286
Page Header property, 286
Picture properties, 287
Width property, 287
FormatCount property, reports, 503
formatting form layouts, 210
Formatting toolbar, 196
Align tools, 202
Report Design window, 278
forms
Access, opening from (SharePoint sites), 896-899
Allow Datasheet View property, 231
Allow Filters property, 234
Allow PivotChart View property, 231
Allow PivotTable View property, 231
alternating background colors, 216, 218
applications, navigating, 190
Auto Resize property, 231
axes, exchanging, 262
basing applications on runtime application distribution, 839
basing on queries, 249-250
basing on queries/embedded SQL statements, 837
Border Style property, 231
borders, 231
builders
designing, 1031-1032
Modal property, 1031
closing, Form event sequence, 442
combo boxes, record sources, 220
command buttons, adding, 244-245
components, 192
conditional formatting. See conditional formatting
Continuous forms, 451-453
Control properties, 237, 241-242
controls, 218
checkboxes, 224
combo boxes, 219-223
Data properties, 239-240
Format properties, 236-239
labels, 219
list boxes, 223
morphing, 227-228
option buttons, 224
option groups, 224-226
Other properties, 240-242
text boxes, 219
creating, 193
Design View, 196
from queries, 194
layouts, selecting, 194
queries, 193
styles, selecting, 195
Time and Billing application example, 263-268
titles, 195
custom methods, creating, 489
custom properties
creating, 484-488
PropertyGet routine, 486-488
PropertyLet routine, 486-488
Public variables, creating, 484-486
Cycle property, 235
data, storing in Type variables, 601
Data Entry property, 234
designing, toolbars, 196
designing (Design view), 18
Detail section, 192
Dialog forms, creating, 459-460
displaying in PivotTable view, 257-258
Dividing Lines property, 232
documenting (Database Documenter), 1075-1077
error, creating, 784
error events, 796
error forms
creating, 782-784
printing, 785
event procedures, 350
examples
frmClients, 263-265
frmProjects form, 266-267
expressions, 243
Fast Laser Printing property, 236
fields
adding, 198-199
adding multiple, 198
selecting, 194
Filter property, 234
filtering features, 464-465
filters, 234
Footer section, 192
Format properties, 231
frmClients, 659-662
command button programming code, 396
frmGetFormInfo, 1040
frmSpecialEffect, 1030-1032
Has Module property, 235
Header section, 192
HTML format, saving in, 253
hyperlinks, adding, 251-253
Internet, compatibility, 251-252
key presses, Form event sequence, 442
labels, 219
sizing, 203
large recordsets, display options, 838
Layout for Print property, 233
Layout view, 205-206
stacked and tabular layouts, 206-211
macros, triggering from, 326-327
Me keyword, 450
Menu Bar property, 235
modal displays, 191
Modal property, 235
Moveable property, 232
moving between, Form event sequence, 442
multipage forms, creating, 453-455
navigating, setting properties, 232
Navigation Buttons property, 232
new features, 51
objects
aligning, 200-202
moving, 200
selecting, 199-200
sizing, 202-203
spacing, 203-204
tab order, changing, 204-205
one-to-many, 246
creating, 246-248
OpenArgs property, 468
opening
Form event sequence, 441
mouse actions, 442
opening with command button code, efficiency considerations, 396-398
optimizing, 829-831
Order By property, 234
Orientation property, 233
passing parameter query values, 588-589
Picture properties, 231
pivot tables
controlling information detail (Pivot Table view), 261-262
creating from, 256
displaying (Pivot Table view), 256-257
filtering data (Pivot Table view), 261
summarized data (Pivot Table view), 259
Pop Up property, 235
pop-up forms, 473-474
printed, 190
printing, 233
properties, 229-235
Data properties, 233
Description property, 1075
property sheet, 229-230
Record Selectors property, 231
record sources, 198
changing at runtime, 233
records, ordering, 234
recordset methods, 719-724
Recordset Type property, 234
RecordSource property, 720
changing at runtime, 468-471
reports with forms, 272
Scroll Bars property, 232
Short Menu Bar property, 235
Short Menu property, 235
shortcut keys, 198
single forms, creating, 451-452
sizing, Form event sequence, 442
smart tags
action buttons, 255
adding, 253-255
Snap to Grid
adjusting grid, 233
temporarily disabling, 202
specifications/limitations, 262
Splash Screen forms, creating, 459
splash screens, creating, 491
split forms, 215-217
splitting into two form layouts, 214
startup forms, 490
styles, 191
subforms, 249, 453, 476-477, 830
referring to subform controls, 476-477
switchboard forms, 190
creating, 457
switching to PivotChart view, 262
synchronizing with underlying recordsets, 483-484
Tab controls
adding, 456
properties, changing, 456-457
tabbed forms
creating, 455-457
Tab controls, adding, 456
Tag property, 236
titles
Caption property, 229
Default View property, 230
UserForm, 976-977
uses, 189
viewing (Form view), 18
Width property, 231
Windows, Form Design, 198-200, 202-204
XML format, saving in, 253
Forms collection, 418-419
referencing forms, 407
fra prefix, 1132
frames
bound object frames, reports, 283
unbound object frames, reports, 283-284
frm prefix, PDF:1132, PDF:1135
command button additions, 268
command buttons, programming code for, 396
Time and Billing Application, creating, 263-265, 268
frmGetFormInfo form, 1040
frmProjects form
creating, 266-267
Time and Billing Application, creating, 266-267
frms prefix, 1132
frmSpecialEffect form, 1030-1032
FROM clause, SQL, 568
front ends
as Access data project communicating directly to back ends, 921
communicating to backends, with SQL Pass Through, 920-921
compared to back ends, 919
executing stored procedures on back ends, 921
file format compatibility, 918
tables, linking from back end servers, 919
using Access as, 842-845
options, 844
front-ends, 847
fsfr object-naming prefix, 1136
fsub prefix, 1132
FullFileName property (file information class), 652-654
Function Subkey value (registry), 1033
functions, 348-349, 610. See also procedures
aliasing, 989-991
ANSI, renaming, 990
API functions, 993
Windows registry, 994-997
Between Date And Date, 149
builders
preset arguments, 1029-1031
writing, 1028-1031
calling
invalid characters, 989
ordinal number references, 991
calling from libraries at runtime, 1015
code, viewing, 387
CodeDB, 1013
constants, 991
CreateControl, 1044
CreateCustomForm, 1041-1042
CreateForm, 1044
CreateRecordset, 964-965
CreatExcelObj, 957
creating, 352-354
shortcut for, 354
CurrentDB, 1013
Date( ), 92
Date(), 149
DateAdd, 378
DateDiff, 378
Day(), 149
Declare statement, 986
DeleteSetting, 994
DLL functions
aliasing, 989-991
calling, 993
declaring, 985-986
passing parameters, 987-989
error messages, 986
ErrorHandler, 778-780
generic error handlers, 772-774
Format, 376
GetAllSettings, 994
GetSetting, 994
hard drives, 1005-1008
IIf, 366
Initials function, 586
InputBox, 462-463
Instr, 376
InstrRev, 376
IsMissing, 614
IsNull, 619-620
IsNull(), 169
Left, 377
Len, 818
libraries
DLLs (dynamic link libraries), 985
Win32 API, 985
Mid, 377
Month(), 149
MonthName, 379
MsgBox, 460-462
MyCustomForm, 1045
names, resolving conflicts, 990
Not, toggling Boolean expressions, 815
NumberOfBytesFree, 1008
Object Browser, 379-380
parameters, 373-374
named parameters, 616
optional parameters, 613-615
parameter arrays, 617-618
passing, 610-613
recursive procedures, 616-617
passing objects to, 413-414
ReLink, 881
Replace, 378
Required, late binding, 955-956
results as criteria for queries, 586-587
Right, 377
SaveSetting, 994
SpecialEffect, 1030
strings, 376-377
StrRev, 379
SysCmd, 1031
TypeOfDrive, 1007
UCase, 377
values, returning, 373-374
VBA, 377-380
Weekday(), 149
Year(), 149
G
g scope prefix, 1135
gallery, ribbons, 34
General Declarations sections
basUtils module, 998-1003
compiler constants, 367
modules, 348
General tab (Database Properties dialog box), 1079
generating errors, 767-768
generic error handlers, 770-775, 778-780, 783-784
error forms
creating, 782-784
printing, 785
ErrorHandler function, 772-774, 778-780
LogError routine, 774-776
On Error routine, 770-771
type structure declaration, 772
generic object variables, compared to specific object variables, 411
GetAllSettings function, 994
GetDrive routine, file information class, 653, 655
GetDriveInfo, 1006
GetDriveInfo procedure, 1006
GetName routine, file information class, 653-655
GetObject function, late binding, 955-956
GetOutlook function, 781
GetPath routine, file information class, 653-655
GetSetting function, 994
GetSysInfo, 1003
GetSysInfo subroutine, 782, 1003
GotFocus event
controls, 445
forms, 438
graphics, reports with graphics, 272
graphs, Excel, creating from Access, 961-965
greater than or equal to (*=), query criteria operator, 146
greater than sign (*), query criteria operator, 146
Grid X property, reports, 287
Grid Y property, reports, 287
Gridline Styles, 291
GROUP BY clause, SQL, 573
Group Footers, reports, 275
Group Header Format event, 534-535
Group Headers, reports, 275
group levels, adding to reports, 276
grouping data, reports, 301-303
groups, adding to ribbons, 944-945
Grp Keep Together property, reports, 286
grp object-naming prefix, PDF:1134
grps object-naming prefix, PDF:1134
H
hard disks
compression, 804
defragmenting, 803
compacting databases, 1091-1092
hard drives, 1005-1008
hardware
optimization, 802
defragmenting hard disk, 803
drive compression, 804
RAM, 802
system requirements, 23
Has Module, 289
forms, 235
HasContinued property, reports, 503
HAVING clause, SQL, 573
Header section, forms, 192
headers, 274. See also Page Headers
reports, adding at runtime, 501
headings, Crosstab queries
fixed, 562
fixed column headings, 561
help
custom, adding to distribution version, 841
properties, 402
VBA, context-sensitive, 391
Help Context Id, 289
controls, 241
Help File, 289
hidden objects, restoring, 64-65
Hide Duplicates, 290
hiding
objects in parent groups, 63-64
status bar, 49
system objects, 943
Unassigned Objects group, 61
hierarchies, building classes, 646
horizontal spacing, adjusting forms automatically, 204
HTML
forms, saving as, 253
saving reports as, 306
table data, saving in, 104-107
tables, converting data, 107
HTML documents
importing, 874-876
linking to, 877
Hyperlink Address, 290
hyperlink base, 1079
Hyperlink SubAddress, 290
adding to reports, 306
forms, adding, 251-253
labels, reports, 306
I
I/O operations, text files, 629
icons, shortcut icons (compacting databases), 1095
idx object-naming prefix, 1134
idxs object-naming prefix, 1134
If statements, 365
compared to Select Case statements, 366
If...Then...Else control structure, 365
IIf function, 366
image controls
compared to unbound object frames, 283
reports, 284
images, splash screens, 459
IME (Input Method Editor) property, controls, 242
img prefix, 1132
Immediate If (IIf) control structure, 366
Immediate window, 734
clearing, 732
debugging features, 729-730
functions, testing, 733
printing values to, 734
setting values of variables/properties, 730-732
subroutines, testing, 733
testing values of variables/properties, 730
troubleshooting applications, 361
implementation stage, applications development, 28
Implements keyword, sharing interfaces, 648
implicit transaction processing, 927
registry settings, 927-929
ImplicitCommitSync setting (Windows registry), 928
Import dialog box, 1037
Import HTML Wizard, 874, 876-877
Import Objects dialog box, 1037
Import Spreadsheet Wizard, 851
importing
DoCmd object, 852-854
user interface, 850-851
files, supported file formats, 850
from SharePoint lists, 900, 902
text files, 630
USysRegInfo tables, 1037
XML files, 1068-1069
In-Place activation (embedded objects), 466
Indexed property
Fields, 96-97
tables, 98
indexed sequential access method data, performance considerations, 849
indexes
application performance, improving, 102
limitations, 809
multifield, creating, 97
optimization, 809
performance considerations, 97
Primary Key, creating, 98
relationships, 134
searching table fields, 97
table relationships and, 103
tables, 98
performance benefits, 103
upsizing to client/servers, 97, 923
indexing fields
queries, 554
selecting field types, 82
INF files, 872
infinite loops, 371
inheritance, 638
Initialize event, 643
data access class, 658
system information class, 668-669
Initials function, 586
InitVars routine, reports, 527
inline code, 814
inline code benchmark, 814
Input # keyword, 628
input boxes, 462-463
InputBox function, 462-463
input devices, minimum system requirements, 23
Input Mask property, 292
query fields, 170
Input Mask property (fields), 89-90
Input Mask Wizard, 89-90
InputBox function, 462-463
Insert Hyperlink dialog box, 103-104
INSERT INTO statement, SQL, 583
Insert Object dialog box, 283, 466
inserting
fields, tables, 80
OLE objects, reports, 283
page breaks, reports, 293
installing
Access
multiuser applications, 910
Windows 2000 Terminal Services, 912
menu add-ins, 1046
multiuser applications, 910-911
instances, manipulating multiple instances of file information class, 655-656
instantiating objects (Class modules), 640
instantiation, 638
Recordset objects, 676
Instr function, 376
InstrRev function, 376
int data-type prefix, 1133
integrity rules, referential and entity rules, 121-122
Intellisense, enumerated types and, 644-646
interfaces, sharing (Implements keyword), 648
International Standards Organization (ISO), 990
Internet
applications, developing, 11
forms, compatibility, 251-252
reports, Internet compatibility, 305-306
intranets, developing applications, 11
Intrinsic constants, 381-383, 602, 604
intrinsic constants
examples, 632-633
viewing (Object Browser), 604
intTemp variable, 739
Invalid add-in entry error messages, 1036
invoking Registry Editor, 1033
Is Hyperlink, 291
Is Not (query criteria operator), 147
Is Null (query criteria operator), 147
ISAM (indexed sequential access method) data, performance considerations, 849
ISAM drivers, Windows registry keys, 866
IsLoaded functions, 1015
IsMissing function, 614
IsNull function, 619-620
IsNull() function, 169
ISO (International Standards Organization), 990
Item method, Collection object, 627
iterating through collection items, 628
J
Jet, 806
JET, ANSI-92 extensions, 590
stored procedures, 593-594
tables, 590-593
transactions, 594
views, 593-594
client/servers, 922
Errors collection, 769-770
Jet Engine
converting databases from old versions, 1096-1098
objects, naming conventions, PDF:1133-1134
queries, optimizing, 553-555
Jet OLEDB Link Datasource, 861
refreshing external links, 869-871
Jet OLEDB Link Provider, 860
Jet OLEDB Link Provider String, 866-867
JOIN clause, SQL, 570-571
join lines (relationships), 124
joining tables, queries, 570-571
joins
outer joins
left outer joins, 563
queries, 562-564
right outer joins, 563
self joins, queries, 564-565
junction tables, 123-124
K
Key argument, Add method, 649
Key command (New menu), 1034
KeyDown event
controls, 448
forms, 439-440
KeyPress event
controls, 449
forms, 440
KeyUp event
controls, 449
forms, 440
keywords
ALL, SQL, 576
ANSI-92 extensions
CHECK, 591
DEFAULT, 590
NO INDEX, 591
As String, 374
ByVal, 987
Close, 628
Dim, custom collections, 649
DSN, 867
Implements, 648
Input #, 628
Open, 628
Preserve, 608-609
Public, 356
PWD, 867
Static, 355
TOP, SQL, 572
UID, 867
Write #, 628
Kill statement, 760
L
Label Wizard, 273
labels
associating/disassociating with controls, 219
attached labels, 91
default, 219
combo boxes, 222
forms
adding, 219
controls, 219
sizing, 203
adding, 251
adding to reports, 306
mailing labels, reports with labels, 273
printing
multiple labels, 520-521
in specific locations, 522-523
reports, 282
hyperlinks, 306
sizing, automatically, 281
lassoing objects, 200
late binding, required functions, 955-956
Layout for Print property
forms, 233
reports, 287
Layout view, forms, 205-206
stacked and tabular layouts, 206-211
layouts
forms, 194
reports, 277
lbl prefix, 1132
Left function, 377
left outer joins, 563
Len function, 818
less than or equal to (=), query criteria operator, 146
less than sign (), query criteria operator, 146
libraries
function libraries, 985
procedures, unique names, 990
library databases, 1011
calling functions at runtime, 1015
compiling, 1013
creating, 1012-1013
code modules structure, 1012-1013
CodeDB function, 1013
CurrentDB function, 1013
explicit references, 1017-1020
Time and Billing application example, 1023-1025
writing code, 1013
debugging, 1022-1023
references, 1013
creating, 1014
creating with VBA code, 1021
runtime, creating, 1014-1016
Library Subkey value (registry), 1033
lifetime, variables, 355
Like (query criteria operator), 147
Like criteria, queries, 554
limitations of reports, 307
lin prefix, PDF:1132
line continuation character, VBA, 364
Line Spacing, 291
Line tool, 282
lines, reports, 282
Link Child Fields property
subforms, 249
subreports, 300
Link dialog box, 856
Link Master Fields property
subforms, 249
subreports, 300
linked tables, converting to local tables, 871-872
linking
external data, 848-849, 854, 862-863, 912
to Access tables, 856-857
with code, 859-863
to database files, 858
Database Splitter, 854-856
Database Splitter Wizard, 912
performance considerations, 874
refreshing links, 869-871, 878-879, 881
user interface, 856-858
user interfaces, 854
files, supported file formats, 850
HTML documents, 877
tables, accessing in networks, 913
reestablishing when SharePoint sites have been moved, 906
refreshing, 877-880
LinkTables routine, 877-880
LinkToDBase subroutine, 862
List Box Wizard, 224
list boxes
adding/removing items at runtime, 474-475
converting to combo boxes to, 228
forms, adding, 223
Multiselect property, 475-476
List Constants feature, modules, 385
List Properties and Methods feature, modules, 384
listing errors, 797
lng data-type prefix, 1133
Load event
data access class, 659
forms, 436
LoadOnStartup key, 1016
local tables, converting from linked tables, 871-872
local variables, 361
Locals window, 744
Lock Delay property (Connection strings), 674
Lock Retry property (Connection strings), 674
locking
data, 913
tables, creating reports, 288
LockType parameter, ADO recordset types, 682-683
LogError routine
custom error handler class, 793-794
generic error handlers, 774-776
logging errors, 774-776
logic errors, 728
Long Date format (fields), 89
Lookup Wizard
Display Control property, 99-100
launching, 98-100
Lookup Wizard field type, 83
lookups
disadvantages, 100
Display Control property, 99-100
field values, executing (Lookup Wizard), 98, 100
looping
through custom collections, 650
through recordsets, 827
loops, 369-370
breaking, 371
control references, avoiding repeating, 370
Do, 369-370
For Each...Next, 816-818
For...Each loops, iterating through collection items, 628
infinite, 371
records, modifying all in a recordset, 704-706
With statement, 823-824
LostFocus event
controls, 446
forms, 439
low-level file handling, 628-629
lst prefix, 1132