December 2006
Pages: 751
Cover | Table of Contents | Colophon
Index
[ Symbols ], [ A ], [ B ], [ C ], [ D ], [ E ], [ F ], [ G ], [ H ], [ I ], [ J ], [ K ], [ L ], [ M ], [ N ], [ O ], [ P ], [ Q ], [ R ], [ S ], [ T ], [ U ], [ V ], [ W ], [ X ], [ Y ], [ Z ],
Symbols[ Top ]
" (quotation marks), 191, 547
# (pound sign), 136
$ (currency symbol), 33
% (percent symbol), 218
& (ampersand), 220, 506
' (apostrophe), 499
- (hyphen), 283, 619
( ) (parentheses)
function parameters and, 531
functions and, 221
math operations and, 218
subroutines and, 513
* (asterisk)
delete queries, 257
inserting records, 35
required fields and, 629
selecting fields for queries, 187
validating text, 136
+ (plus sign), 283, 665
, (comma), 610
. (period)
modules and, 504, 531
XML tags and, 619
. . . (ellipsis)
conditions and, 491
Input Mask wizard, 131
ready-made masks, 126
Visual Basic editor and, 501
: (colon), 540
; (semicolon)
email addresses and, 477
input masks and, 130
SQL and, 200
< (less-than sign), 488, 526
<> (angle brackets)
not-equal-to operator, 488, 526
XML tags, 618
= (equal sign)
assignment statements and, 505, 525
conditions and, 488
expressions and, 345
logical operator and, 526
> (greater-than sign), 488, 526
? (question mark), 136
[] (square brackets)
assignment statements and, 506
functions and, 225
spaces in field names, 80, 216
\ (backslash), 572, 648
_ (underscore)
line continuation character, 506
XML tags and, 619
A[ Top ]
Abs( ) function, 222, 227
.accdb file extension
information in, 27
locking down front end, 579
multi-value fields and, 171
security and, 592
.accde file extension, 579, 580
.accdr file extension, 581, 582
Access (Microsoft)
backward compatibility, 13
Excel vs., 3, 4
exportable file type support, 611
importable file type support, 604
limitations of, 6
new features, 13-14
SQL Server vs., 5
Access 2007 ribbon (see ribbon)
Access Developer's Toolkit, 26
Access Options window
action query warnings, 249
AutoCorrect feature, 109
configuring settings with, 11
Customize section, 699
spell checking options, 106
Access runtime engine, 580-582
Access Web Datasheet, 684
ACCWIZ folder, 471
Action Arguments section
editing macros, 445
filter expressions, 448
macro action information, 448
Macro tab, 460
naming reports, 452
action queries
append queries, 251-256
defined, 184, 243
delete queries, 256-258
find-and-replace feature vs., 245
overview, 243-245
parameters and, 244, 652
select queries vs., 248
stored procedures and, 662
testing, 244
update queries, 245-250
viewing without running, 250
actions
creating macros, 458-462
defined, 458
methods and, 507, 511-513
reordering, 461
repeating with loops, 529, 530
safe, 466
unsafe macro, 465-467
viewing list of macro, 466
ActiveX control, 397
acwztool project, 497
Add New Record command, 418
Add Table dialog box, 662
adding
attachments, 73
buttons to QAT, 699
controls, 329-332
controls to forms, 392-395
controls to reports, 331
descriptions for fields, 54
fields between existing fields, 54
fields in forms, 362
fields in Layout view, 390
fields in pivot tables, 279
fields to end of tables, 54
fields to reports, 299-300
fields via Form wizard, 387
grouping levels in pivot tables, 279
label controls, 331
links to documents, 414
lookups to fields, 140-142
math operations for, 217, 219
modules, 497-498
objects to SQL Server databases, 655-665
pictures to reports, 300
records to forms, 367
Rectangle control, 331
references, 542
timestamp fields to tables, 650
values to lookup lists, 143
Web Parts, 677, 679
add-ins, 470
Adobe Reader, 307, 309
.adp file extension, 651, 656
AdventureWorks sample database, 263, 267
After Del Confirm event, 483
After Insert event, 483
After Update event, 482, 483
Alert Me action, 677
aliases, pivot tables and, 274
All tab (Property Sheet), 341
Allow [ . . . ] View property, 385
Allow Additions property, 385, 409
Allow Deletions property, 385, 409
Allow Edits property, 385, 409
Allow Filters property, 385, 450
Allow Multiple Values option, 165
Allow settings, 595
Allow Zero Length property, 118
Alt key
keyboard accelerators and, 8
KeyTips feature and, 9
macros and, 480
Alt+E key combination, 10
ampersand (&), 220, 506
anchoring
controls, 400-405
subforms and, 421
And operator
conditions and, 489, 527
date functions and, 230
field validation, 137
filtering and, 223
table validation, 138
angle brackets <>
not-equal-to operator, 488, 526
XML tags, 618
apostrophe ('), 499
Append or Make Table dialog box, 253
append queries, 245, 251-256
Application category, 418
ApplyFilter method (DoCmd), 544
arguments
defined, 221, 460
OpenForm method, 554
reading from forms, 484-487
arithmetic operators, 217
arithmetic rounding, 221
As keyword, 525
As String clause, 534
ASCII code, 513
ASP.NET, 582, 671
assignment statement
calling functions with, 531
defined, 505
example, 525
asterisk (*)
delete queries, 257
inserting records, 35
required fields and, 629
selecting fields for queries, 187
validating text, 136
Attachment control, 369, 396
Attachment data type
copying, 601
description, 58
Indexed field property, 120
lookup support, 140
one-to-one relationships and, 167
overview, 72-74
showing pictures from databases, 369
SQL Server, 658
attachments
Access support, 13
adding, 73
copying, 74
database considerations for, 576
deleting, 73
editing, 74
SQL Server conversion and, 652
viewing, 74
Attachments column (SharePoint), 679
Attachments dialog box, 73, 74
authentication
SharePoint, 673
SQL Server, 644
Auto Center property, 385
Auto Tab property, 407
AutoCorrect feature, 106, 108-109
AutoExec macro, 481
AutoFilter icon, 285
AutoFormat feature, 310-311, 364
AutoFormat wizard, 311
AutoKeys macro, 479, 480
automatic layout feature, 13
automatic refreshes, 583
automatic save feature, 39
AutoNumber data type
append query guidelines, 251
changing values in fields, 157
creating primary key field, 79
description, 58
filtering support, 99
forms and, 365
linking with ID number, 149, 629
lookup support, 140
manipulating start values, 254-256
overview, 75-77
parent-child relationships and, 152
sorting options, 95
SQL Server, 658
validation support, 132
Avery number, 337
Avg option (Total), 236
B[ Top ]
Back Style property, 440
Back Up SQL Database command, 654
BackColor property, 343, 508-510
back-end databases
Access versions and, 573
automatic refreshes, 583
compact and repair feature, 591
defined, 568
naming, 572
passwords and, 593
placement of, 570
splitting with wizard, 570-573
background color, 343
backslash (\), 572, 648
backups
before action queries, 244, 249
changing data types and, 59
corrupt databases and, 590, 591
overview, 39
scheduling tools, 568
SharePoint and, 688
SQL Server databases, 654
banker's rounding, 221
bar charts, 287, 288
Beep method (DoCmd), 544
beeps, Message box window, 461
Before Del Confirm event, 483, 490
Before Insert event, 483, 490, 492
Before Update event, 483, 490-492, 528
binary format, 618
blank fields
importing Excel files, 610
preventing, 116-118
blank values
email addresses as, 528
empty text vs., 118
filtering queries for, 209
functions and, 232, 233
importing Excel files with, 610
unlinked records, 157
.bmp file extension, 419
Boole, George, 489
Boolean data type (VB), 525
Border Style property, 385
borders
controls with, 330
formatting for reports, 315
removing from text boxes, 411
reusing settings, 395
Bound Column setting (SQL Server), 660
bound controls, 394, 409
Bound Object Frame control, 396
Boutique Fudge tutorial, 258-261
break mode, 538, 540
breakpoints
defined, 537
depicted, 537, 538
using, 537-539
British postal code mask, 130
business tasks
adding products during orders, 552-556
managing order fulfillment, 556-559
running with VB, 547-548
updating stock numbers, 559-562
Button wizard, 417, 439, 440, 444
buttons
adding to QAT, 699
closing forms with, 439
command, 417-419, 444
drawing on forms, 417
drop-down, 17
forms and, 330
naming, 419
OnClick event, 444
pictures on, 419
properties for, 440
reusing border settings, 395
selecting from Property Sheet, 448
spacing controls, 400
Windows XP support, 397
Byte option (Field Size property), 64
C[ Top ]
calculated fields
creating, 214-216
crosstab queries and, 273
defined, 214
Expression Builder support, 223
functions and, 220
overview, 213-214
pivot tables and, 279-282
placing orders task and, 551
simple math with, 217-219
SQL Server and, 664
summary queries and, 239
text expressions, 219, 220
text functions and, 228
Calculation tab (pivot tables), 281
calculations
expressions in forms, 410, 411
grouping for subtotals, 236
pivot table support for, 274
queries performing, 184
reports and, 298
calendar smart tag, 68
Calendar Web Part, 677
Can Grow property, 344
Canadian postal code mask, 130
CancelEvent action, 490, 491
capitalization
AutoCorrect feature, 108, 109
case sensitivity, 619
changing, 228
naming conventions, 80
spell checker and, 107
Caption property
Attachment data type and, 73
description, 385, 511
modifying text, 419
Cascade Delete Related Fields setting, 158, 159
cascading deletes, 157-159
cascading updates, 158
categorizing
database objects, 426
filtering and, 429-431
navigation pane, 426-428
Character Map tool, 110
Chart control, 396
Check Box control, 395
circular references, 216
Clear All Sorts command, 96, 97
Clear Filter command, 99
clicking, 18
client-side processing, 5
Clip Art, 331
clipboard
overview, 599-603
saving changes to, 585
shortcut keys, 599
Close action, 474
Close Button property, 558
Close Form command, 418
Close method (DoCmd), 545
Close Print Preview command, 112
collect-by-email feature, 626-636
collections, 511, 530
colon (:), 540
colors
background, 343
predefined names for, 508
setting, 517, 518
Column Count setting (SQL Server), 660
column fields (pivot tables), 276
column headers
formatting for reports, 312-313
grouping and, 349
hiding columns, 92
moving, 327
removing, 329
Column Heads setting (SQL Server), 660
Column Width dialog box, 91
Column Widths setting (SQL Server), 660
Columnar option (layouts), 335, 387
columns
default for lists, 679
expanding, 33, 219, 327
exporting considerations, 310
filtering, 99
filtering pivot table, 284
fine-tuning printouts, 114
formatting for reports, 312-313
freezing, 92, 94, 114
hiding, 91, 92, 114, 187
labels and, 340
layouts and, 377-378
moving, 90
naming, 34
ordering for reports, 296
picking right groups, 271
pivot charts and, 286
pivot table fields, 276
rearranging, 89, 90
rearranging for queries, 187
removing filters from, 98
resizing, 90-91
resizing for reports, 295
resizing in forms, 362
shrinking, 327
SQL Server database tables, 656
unfreezing, 94
unhiding, 92
Columns button, 305
Combo Box control
description, 395
list controls and, 395
navigating with, 415, 442-445
comma (,), 610
Command Builder wizard, 461, 462, 465
Command Button wizard, 417, 458, 485
command buttons, 417-419, 444
commands
customizing QAT, 697-701
menu, 436-438
comments, 499, 546
compact and repair feature, 590, 591
compacting databases, 40
complex data feature, 13
compound forms, 440, 441
compound indexes, 122-123
Computer Name tab, 649
concurrency, 684
Condition column, 488, 491
conditional formatting, 312, 316-319, 334
conditional macros, 487-493
conditions
building, 488, 489
defined, 488, 526
evaluating, 488, 526
exit, 529
making decisions with, 525-528
validating data with, 489-491
Conditions column, 488
Configure Your Server wizard, 671
configuring
controls, 443
navigation list, 426-429
startup macros, 480
trusted locations, 472
constants, 544
Contacts list (SharePoint), 677
contextual tabs, 16
Continuous Form view, 519
contribute permission, 683
Control Box property, 558
control events, 481
Control wizard
adding controls, 393
creating links, 413
creating lists, 416
drawing buttons on forms, 417
controls
adding, 329-332
adding to forms, 392-395
adding to reports, 331
anchoring, 400-405
arranging on forms, 397-400
automatically resizing, 400-405
bound, 394, 409
configuring, 443
creating, 397
defined, 326
deleting, 398
Design view support, 390
formatting, 326, 333, 394, 451
forms support for, 330, 360, 395-397
gridlines and, 398
icons for, 393
layouts for, 375
methods for, 512
modifying, 398
moving, 397
naming, 516
overlapping, 330, 400
overview, 326-327
programming objects and, 507
properties for, 341, 508
repositioning in tab order, 408
resizing, 398
sizing, 399, 403-405
spacing, 399
subform control, 420-422
tab, 396, 411-413
unbound, 519
useful properties, 343
validation rules in, 409-410
wizards for, 392
converting
information to other types, 600
inner joins to outer joins, 209
macros to VB code, 545, 547
negative numbers, 222
versions of databases, 44
copy-and-paste operation, 38, 601-603
CopyDatabaseFile method (DoCmd), 545
copying
attachments, 74
cells from Excel, 603
data types, 601
database objects, 49
databases, 566, 602, 653
records, 38
SQL Server databases, 654
tables elsewhere, 600-601
as unsafe macro action, 467
Count option (Total), 236
Create tab (ribbon), 7
Created By field (SharePoint), 688
Created Date mode (navigation pane), 427, 429, 430
creating
append queries, 252-254
calculated fields, 214-216
calculated fields in pivot tables, 279-282
controls, 397
crosstab queries, 266-273
custom functions, 530-532
customized subform controls, 422
databases, 26, 26-28, 45
email messages, 628-633
expressions, 410, 411
form layouts, 375-386
forms, 360-364
forms in Design view, 389
forms in Layout view, 389, 390
labels, 337-340
links, 413-415
links in fields, 452
lists, 678-680
lookups, 162
macros, 444, 458-462
make-table queries, 252-254
menu commands, 436-438
mouseover effects, 518-521
one-to-one relationship, 175
primary key field, 78, 79
projects, 651
queries, 185-202
queries via projects, 665
report headers/footers, 334
reports, 302
reports in Design view, 332-334
SharePoint team sites, 672-675
simple reports, 295-297
split forms, 383
SQL Server databases, 647-655
switchboards, 435-439
tables, 31-35, 656-662
tables while importing, 606, 614
totals queries, 234-236
union queries, 201-202
update queries, 245-250
variables, 524
views, 662-665
credit cards, testing, 532-536
Crosstab property, 272, 273
crosstab queries
creating, 266-273
Crosstab Query wizard, 196, 266, 268-271
overview, 263-266
pivot tables and, 276
SQL Server support, 652
totals queries vs., 264, 266
Crosstab Query wizard, 196, 266, 268-271
Ctrl key, 10, 480
Ctrl+" key combination, 37
Ctrl+; key combination, 38
Ctrl+Alt+Space key combination, 38, 118
Ctrl+C key combination, 38
Ctrl+End key combination, 37
Ctrl+F key combination, 102
Ctrl+F1 key combination, 48
Ctrl+Home key combination, 37
Ctrl+K key combination, 72
Ctrl+S key combination, 56
Ctrl+V key combination, 38
Ctrl+X key combination, 38
Ctrl+Z key combination, 37, 367
Currency data type
custom string, 66
description, 58
filtering support, 99
lookup support, 140
overview, 66, 67
sorting options, 95
SQL Server, 658
Visual Basic, 525
Currency option
Format field property, 65, 66
Format( ) function, 226
formatting numeric fields, 314
Query Parameter dialog box, 241
currency symbol ($), 33
Cursor On Hover property, 440
Custom Dictionaries dialog box, 108
Custom mode (navigation pane), 428
custom spell check dictionary, 106, 108
cut-and-paste operation
import/export and, 604
overview, 38
transforming tables with, 602
Windows Explorer and, 653
D[ Top ]
DAO (data access objects)
ADO vs., 562
importing and, 599
overview, 542, 559
data access pages, 582
data corruption
overview, 589-591
shared databases and, 638
split database and, 569
Data Entry property, 385
data fields (pivot tables), 276
Data Link Properties dialog box, 654
Data tab (Property Sheet)
calculations with expressions, 410
configuring controls, 443
description, 341
exporting tables, 624
data types
append query guidelines, 251
considerations when changing, 59
copying, 601
filtering support, 99, 100
identifying for variables, 525
Indexed field property, 120
lookups support, 140
overview, 51, 56-59
Required field property support, 118
SharePoint, 681
SQL Server, 657-658
text-based, 100, 101, 118
validation support, 132
(see also specific data types)
data validation
applying rules, 132, 133
creating table rules, 137, 139
Design view, 115
input masks, 123-131
lookups, 139-143
preventing blank fields, 116-118
preventing duplicate values, 119-123
setting default values, 118-120
writing rules, 133-137
database catalog, 653
database corruption, 638
database design, 79-85
database diagram
building relationships, 660
defined, 153
saving, 155
database indexes (see indexes)
database objects
categorizing, 426
defined, 24
emailing, 475
filtering, 426-428
front-end database and, 568
grouping, 426-428
hiding, 426, 431
managing, 49
naming, 649
objects and, 507
reports as, 293
security considerations, 432
types of, 24
unsafe macro actions, 467
Database Splitter wizard, 570-573
Database Tools tab (ribbon), 7
databases
Attachment data type, 73
benefits of, 4
blocked updates, 250
closing, 591
compacting, 40
consistency and, 123
converting versions of, 44
copying, 566, 602, 653
creating, 26, 26-28, 45
creating in previous versions, 30
customizing, 701, 702
data corruption in, 589-591
defined, 1, 24
deleting, 653
dividing into files, 432
embedding files in, 63
front end to, 360
importing consideration, 599
linking, 63
managing, 653, 654
moving, 653
moving to SharePoint, 688-690
naming, 649
on networks, 566, 567
opening, 26, 41-43
opening in Exclusive mode, 588
opening multiple, 43
opening previous versions, 43-45
password-protection feature, 591, 592
planning, 31
preparing for sharing, 568-582
saving, 39
saving as previous version, 29
saving with different names/formats, 41
security and, 591-595
server-side, 638
showing pictures from, 369
shrinking, 40
switching views, 17
tables and, 30
taking offline, 692
templates for, 26
transferring records to, 252
upsizing, 599, 647-653, 657, 688
using someone else's, 25
(see also back-end databases; front-end databases; shared databases; split databases; SQL Server databases)
Datasheet Find feature, 473
Datasheet option (layouts), 387
Datasheet view
copying rows in, 600
data validation, 133
exporting and, 614
hyperlink support, 71
primary key and, 78
purpose, 31, 32
running queries, 191
switching views, 52, 53
tables and, 52
datasheets
Access Web Datasheet, 684
advanced editing, 104-111
customizing, 88-94
filtering records, 97-101
find-and-replace feature, 103, 104, 245
formatting, 88, 89
forms and, 386
forms vs., 360
moving around, 36, 37
navigating, 94-104
printing, 111-114
query results as, 189
searching records, 101-104
sorting records, 94-97
split forms, 383
Date data type
considerations when changing, 59
filtering support, 100, 101
Visual Basic, 525
date expressions
queries and, 192
validation rules and, 134, 135
date functions, 229-232
Date( ) function
current date and, 119, 120, 135
description, 232
filter condition with, 229
time information and, 231
Date/Time data type
custom formats, 69-70
description, 58
filtering support, 99
lookup support, 140
overview, 67-70
sorting options, 95
SQL Server, 658
date/time information
arranging for reports, 297, 303
calculations with, 231
copying, 601
date components, 230, 231
expressions and, 507
formatting, 33
Group, Sort, and Total pane, 352
input masks for, 127
math operations on, 218
math operations with, 218
sample mask for, 130
Date/Time option (Query Parameter), 241
DateAdd( ) function, 232
DateDiff( ) function, 232
DatePart( ) function, 229, 230, 232
DateSerial( ) function, 232
DAvg function, 550
Day( ) function, 232
DB2, 638
dBase, 605
DCount function, 550
debugging
debug mode, 541
defined, 464
macros, 463-465
single-step, 464, 465, 538-539
VB code, 536-539
decimal numbers, 63, 314
Decimal option (Field Size property), 65
Decimal Places field property, 66
default property, 508
Default Value property, 118, 410
default values
dynamic, 119
setting, 118-120, 410
SQL Server and, 649
default view, 682
Default View property, 344
delete queries
defined, 245
overview, 256-258
saving, 258
Delete Record command, 418
DeleteObject action, 466
deleting
attachments, 73
cascading deletes, 157-159
commands from QAT, 700
controls, 398
database objects, 49
databases, 653
fields, 34, 54
fields from reports, 299-300
label controls, 332
lists, 683
records, 35, 256-258
records from forms, 368
relationships, 162
SQL Server databases, 655
as unsafe macro action, 467
(see also delete queries)
delimited text files, 610
delimiters, 610
Deny settings, 595
Design view
accessing data types, 56
controls and, 326-332, 390
creating crosstab queries, 272, 273
creating forms in, 389
creating queries, 185-195
creating reports in, 332-334
customizing forms in, 389-408
data validation, 115
debugging macros in, 464
defining relationships, 151
defining reports, 302
depicted, 324
field validation rules, 132
for forms, 365
lookup lists, 140
moving fields out of layouts, 327-329
overview, 52-56
purpose, 31
ready-made masks, 125
report sections, 324-325
setting primary key in, 77
SQL Server, 659, 660
switching to, 52, 53, 115, 381
tweaking macros in, 463
updates in, 55
viewing reports in, 323
detail fields (pivot tables), 276, 281
Detail section (Design view), 325
DFirst function, 550
dialog mode, 554
digital certificates, 470
digital signatures, 470
Dim keyword, 524
Disabled Mode, 250
disaster recovery, 245
disconnected changes feature (SharePoint), 692, 693
Display Control setting (SQL Server), 659
Display When property, 344
Dividing Lines property, 382
division
integer, 217
math operations and, 217, 219
Mod operator and, 535
DLast function, 550
DLookup function, 550, 551, 552
DMax function, 550
DMin function, 550
Do/Loop block, 529, 530
DoCmd object
converting macros to VB code, 545, 547
overview, 543-545
document element, 620, 622
document prolog, 618
document workspaces, 672
documents, adding links to, 414
domain functions, 550, 559
dot operator, 508
Double option (Field Size property), 65
double-clicking
collapsing ribbon, 48
defined, 18
Edit mode toggle, 36
expanding columns, 33
launching queries, 190
opening multiple databases, 43
opening tables, 47
selecting fields for queries, 186
Down Arrow key, 37
dragging, 18
drop-down buttons, 17
DSum function, 550
Duplicate Record command, 418
duplicate records
importing considerations, 608, 615
one-to-one relationship and, 175
preventing, 78, 119-123, 166
dynamic default values, 119
E[ Top ]
Edit Hyperlink window, 72
Edit List Items dialog box, 143
Edit Message property, 477
Edit mode, 36
Edit Relationships dialog box, 154, 158
Edit Switchboard Item window, 436
Edit Switchboard Page window, 436
editing
attachments, 74
AutoCorrect feature, 108-109
calculated fields and, 215
conflicts in shared databases, 584-586
datasheets, 104-111
join query limitations, 420
locking records while, 587, 638
macros, 445, 448
overlapping changes, 586-588, 638
records, 35, 37, 583
records in forms, 366-367
relationships, 155
SharePoint data, 691
sorted columns, 96
special characters, 110-111
spell checking feature, 104-108
splitting tables, 586
tables, 35-38
elements
Access rules for, 622
defined, 618
nesting, 619-621
ellipsis ( . . . )
conditions and, 491
Input Mask wizard, 131
ready-made masks, 126
Visual Basic editor and, 501
emailing
collect-by-email feature, 626-636
data, 475-477
exporting data, 598
unsafe macro actions and, 467
embedded macros, 462, 485
empty text, blank values vs., 118
Enable Content button, 468
Enable Referential Integrity option, 167
Enabled property, 409, 487, 510
encryption, 592, 593
End key, 37
End Sub statement, 498
end tags, 618
Enforce Referential Integrity option, 154, 156
Enter Parameter Value dialog box, 241
EOF property, 560
equal sign (=)
assignment statements and, 505, 525
conditions and, 488
expressions and, 345
logical operator and, 526
#Error code, 347
error handling
"file already in use" error, 575
relationships and, 151
types of macro errors, 466
Visual Basic and, 535, 536, 539-541
Visual Basic with, 498
error messages
conditional logic and, 528
corrupt databases and, 589
data validation, 132
debugging code and, 540
debugging macros, 463
SQL Server Express installation, 644
Esc key
adding records to forms, 367
editing records, 37
form controls and, 393
Euro option
Format field property, 65, 66
formatting numeric fields, 314
evaluating the condition, 488, 526
event handlers, 504
Event tab (Property Sheet)
attaching macros to events, 484
connecting macros to forms, 482
creating hyperlinks, 452
description, 341
selecting buttons, 448
events
attaching macros to, 484
control, 481
defined, 507
form, 482, 500-504
macros and, 444
naming, 503
overview, 481-484, 513-514
section, 481
subroutines and, 504
Excel (Microsoft)
Access vs., 3, 4
Attachment data type support, 72
copying cells from, 603
exporting data to, 598, 612
exporting reports to, 310
importing from, 604, 607-610
special character support, 110
exclamation mark icon, 331, 467
Exclusive mode, 575, 587, 588
Execute method (CurrentDb), 560
exit condition, 529
Exit Function statement, 541
Exit Sub statement, 541
explicit relationships, 151
exponents, math operations and, 217, 219
Export As dialog box, 309
Export wizard, 612, 614, 684
exporting
basic steps for, 612, 614
data, 310, 566
file types supported, 611-612
overview, 598, 604
reports, 306-309, 614
reusing settings for, 614-616
tables, 571, 577, 624
tables to SharePoint, 684-686
to XML, 623-625
Expression Builder, 222-225
Expression property, 487
expressions
actions and, 474
applying to different forms, 375
building, 216
calculated fields and, 214
calculations in forms, 410, 411
complex, 506
conditions and, 526
creating, 410, 411
date, 134, 135, 192
defined, 119, 410
domain functions and, 550
errors displayed, 347
Expression Builder, 222-225
filter, 188, 191-193, 196
filtering multiple fields, 372
macros and, 485
manipulating values, 507
operators and, 488
records and, 215
reports and, 345
selecting linked records, 448
SQL Server views and, 662, 664
summary queries and, 239
text, 219, 220
update queries, 246
validating numbers, 134
extensible markup language (see XML)
External Data tab (ribbon), 7
F[ Top ]
F2 key, 36
F5 key, 539
F8 key, 538
Field List pane
adding fields, 299
creating reports, 302, 332
depicted, 299
field properties, 56
Field Properties section
depicted, 54
Lookup tab, 143
setting maximum length, 61
Field Size property
Length column and, 656
manipulating AutoNumber values, 255
Number data types and, 59, 64, 65
Replication IDs and, 76
setting maximum length, 61
fields
adding between existing fields, 54
adding descriptions, 54
adding in forms, 362
adding in Layout view, 390
adding in pivot tables, 279
adding to end of tables, 54
adding via Form wizard, 387
adding/removing from reports, 299-300
append query guidelines, 251
applying indexes to, 120
applying validation rules, 132-133
arranging for queries, 187
breaking down information in, 81
building expressions out of, 216
fields (continued)
collect-by-email feature, 629
as controls, 392
creating links in, 452
creating primary keys, 78, 79
data types and, 56, 57
defined, 31
deleting, 34, 54
domain functions and, 550
filtering multiple, 372
filtering pivot table, 284
hidden, 299, 362, 688
indexing, 195
linking, 151
listing in Design view, 54
locking down, 408, 409
lookups for, 139
matching, 148
moving, 54
moving in forms, 363
moving in pivot tables, 279
moving out of layouts, 327-329
multifield indexes, 122-123
multiple layouts, 377-378
multi-value, 170-171, 191, 652
naming, 33, 34, 80, 81
one-to-one relationship, 166
order of, 54
organizing/describing, 53-54
overlapping, 329
picking for grouping, 271
pivot table, 276, 281
pointing to files, 63
preventing blank, 116-118
reading/writing on forms, 505, 507
removing from pivot tables, 279
renaming in queries, 217
required, 629
selecting for queries, 185-186
setting default values in, 118-119, 410
sorting on multiple, 96-97, 321
spell checking, 104
swapping content of, 524
timestamp, 650
update queries, 246
writing validation rules, 133-137
(see also blank fields; calculated fields)
"file already in use" error, 575
file extensions, 27
file management tools, 653
File New Database dialog box, 28, 29
file paths
adding links to documents, 414
Shell function and, 543
spell checker and, 107
UNC, 572
file types, 604, 653
files
dividing databases into, 432
embedding in databases, 63
fields pointing to, 63
Windows security, 594-595
filter by condition, 100-101, 371
filter by form feature, 367, 372-374
filter by selection, 99, 100, 371
filter expressions
building, 191-193
Criteria box and, 188
for duplicated values, 196
lookups and, 192
for unlinked records, 196
filter fields (pivot tables), 276
Filter On Load property, 384
Filter property, 384
filtering
crosstab queries, 272, 273
database objects, 426-428
date functions and, 229, 230
defined, 94
delete queries and, 256, 257
DoCmd methods and, 544
domain functions and, 550
Expression Builder support, 223
filter by condition, 100-101, 371
filter by selection, 99, 100, 371
fine-tuning printouts, 114
forms and, 367, 371-375
functions and, 220
indexed fields and, 195
labels, 339
linked records, 446
multiple fields, 372
navigation pane and, 429-431
null values and, 233
pivot charts and, 286
pivot tables, 276, 283-285
queries vs., 102
query parameters and, 240
Query wizard and, 198
quick filters, 98, 371
records in queries, 188
records in tables, 97-101
removing, 99, 450
reports, 319, 321
saving operations, 374-375
SQL Server views and, 662, 664
switching on/off, 100
top/bottom, 284-285
totals queries and, 235, 236
for unmatched records, 209
validation rules and, 193
Find Duplicates Query wizard, 196, 608
Find feature, 102
Find Unmatched Query wizard, 196
find-and-replace feature
action queries vs., 245
Find and Replace dialog box, 103, 104
FindNext method (DoCmd), 545
FindRecord action
example, 473, 474
reading arguments from forms, 486
FindRecord method (DoCmd), 545
firewalls, 646
First option (Total), 236
Fix( ) function, 227
Fixed option
Format field property, 65
Format( ) function, 226
formatting numeric fields, 314
fixed-width text files, 610
folders
backups and, 39
changing access uses, 44
collect-by-email feature, 630
selecting, 28
trusted locations, 43
(see also shared folders)
FontBold property, 510
FontItalic property, 510
FontName property, 510
fonts, 110, 114
FontSize property, 507, 510
footers
form, 391
Group, Sort, and Total pane, 352
page, 325, 391
(see also report footers)
For/Next block, 529, 530, 534
Force New Page property, 345, 353, 354
ForeColor property, 510
form events, 482, 500-504
Form Operations category, 418
Form view, 365
Form wizard, 386-388, 436
Format field property
Currency data type and, 66
Date/Time data type and, 67, 69
Number data type, 65, 66
Format Operations category, 444
Format Painter, 313
Format property, 344
Format tab
button properties, 419
control properties, 343
pivot tables, 281
Property Sheet, 317, 341, 343
Format( ) function
description, 227, 232
overview, 225
"place an order" task, 549
FormatNumber( ) function, 226
FormatPercent( ) function, 226
formatting
alternate rows, 314
AutoFormat wizard, 311
borders, 315
columns/column headers for reports, 312-313
conditional, 312, 316-319, 334
controls, 451
controls and, 326, 333, 394
custom currency string, 66
custom number string, 66
datasheets, 88, 89
Date/Time data type, 67-70
Design view and, 329
exporting considerations, 310, 613, 614
fine-tuning printouts, 114
Format field property and, 65, 66
Format Painter tool, 313
forms and, 363
gridlines, 315, 316, 317
Memo data type and, 61, 63
numbers, 225, 226
numeric fields, 314
pivot table fields, 281
Property Sheet settings and, 342
reports, 296, 310-318
forms
adding controls, 392-395
adding fields, 362
adding records to, 367
anchoring controls, 400-405
arranging controls, 397-400
attaching macros to, 462
automatically resizing controls, 400-405
AutoNumber fields in, 365
calculations with expressions, 410, 411
changing properties, 487
closing with buttons, 439
collect-by-email feature, 627
command buttons, 417-419
compound, 440, 441
connecting macros to, 481-487
continuous, 519
controlling controls, 408-419
controls supported, 330, 360, 395-397
creating, 360-364
creating layouts for, 375-386
customizing in Design view, 389-390
datasheets vs., 360
defined, 24, 282, 359
forms (continued)
deleting records, 368
different parts of, 391
drawing buttons on, 417
embedded macros in, 462
filtering, 371-375
finding/editing records, 366-367
formatting, 363
front-end database and, 568, 639
hidden fields, 362
hyperlinks in, 330, 415
input masks and, 128
junction tables and, 172
layouts and, 375
linked records in separate, 446-450
linked tables and, 420-422
linking, 413-415
locking down fields, 408, 409
locking down front end, 579
navigating with lists, 415-417, 442-445
navigation controls, 366, 367
navigation features in, 435-445
ordering tabs, 406-408
picture backgrounds for, 440
pivot tables in, 282
preventing corruption, 590
printing records, 368, 370
properties, 384, 385
putting code in, 500-507
reading arguments from, 484-487
reading/writing fields on, 505, 507
reference values on, 652
saving, 364
SharePoint support, 688
showing multiple records, 380-382
showing pictures, 369
shrinking, 381
sizing controls, 399, 403-405
sorting, 370
split, 382, 384, 386, 420
startup, 438, 439, 581
subform control, 420-422
switchboard alternatives, 439-445
switchboards and, 435-439
tab control, 411-413
table relationships and, 420
text field size considerations, 362
types of, 386
validation rules, 409, 410
viewing modes, 364, 365
free-floating windows, 385, 467
freezing columns, 92, 94, 114
FROM statement (SQL), 200, 443
front-end databases
creating different, 569
defined, 568
distributing, 573
locking down, 579-580
passwords and, 593
for SharePoint, 668
SQL Server and, 639
full control permission, 683
functions
blank values and, 232, 233
calling, 531
conditions and, 488, 489
converting macros to VB code, 547
creating custom, 530-532
date, 229-232
defined, 135, 220, 530
domain, 550, 559
Expression Builder, 222-225
finding, 224
formatting numbers, 225, 226
mathematical, 226-227
nested, 222
overview, 221-222
pivot tables and, 281
SQL Server, 665
string manipulation, 230
summary, 264, 273, 277
testing credit cards, 532-536
text, 228-230
user-defined, 662
G[ Top ]
General Date format (Date/Time data type), 67
General Number option (Format property), 65, 314
getting started
creating databases, 26-28
creating tables, 31-35
editing tables, 35-38
Getting Started page, 24-26
understanding tables, 30-31
Getting Started page
creating databases, 26-28, 45
creating SQL Server databases, 655
customizing, 26
depicted, 25
live content from Web, 27
opening databases, 26, 42
.gif file extension, 419
globally unique identifier (GUID), 77
Go To First Record command, 418
Go To Last Record command, 418
Go To Next Record command, 418
Go To Previous Record command, 418
Go to Switchboard command, 438
GoToControl action
finding records, 473
reading arguments from forms, 486
GoToControl method, 547
GoToRecord action, 461
GoToRecord method (DoCmd), 545
greater-than sign (>), 488, 526
gridlines
controls breaking free from, 398
creating reports and, 315-317
Group By option (Total), 236
group names, 594
Group, Sort, and Total pane, 349-354
groups/grouping
adding levels in pivot tables, 279
crosstab queries and, 264-266
custom, 432-434
database objects, 426-428
defined, 646
filtering pivot table, 284
Group, Sort, and Total pane, 351
hiding, 433
join queries and, 267
macros and, 477-479
manipulating, 432
multiple, 354-355
picking fields for, 271
pivot charts and, 286-287
pivot tables and, 273, 276
queries and, 346
records, 234, 235
reports and, 346-355
SharePoint, 674
totals queries, 236-238
GUID (globally unique identifier), 77
H[ Top ]
headers
form, 391
Group, Sort, and Total pane, 352
orphaned, 352
page, 324, 329, 391
(see also report headers)
Height property, 510
hiding
columns, 91, 92, 114, 187
database objects, 426, 431
groups, 433
pivot table details, 283
pivot table groups, 273
queries, 258
Home key, 37
Home tab (ribbon), 7
Hour( ) function, 232
Hourglass method (DoCmd), 545
HTML
clipboard and, 600
collect-by-email feature, 628
exporting considerations, 306, 310, 612
importing considerations, 605
Hyperlink control, 396
Hyperlink data type
description, 58
filtering support, 99
lookup support, 140
overview, 71-72
pointing to files, 63
sorting options, 95
SQL Server, 658
hyperlinks
forms and, 330, 415
Hyperlink control, 396
launching programs with, 543
to related data, 450-453
SQL Server conversion and, 652
hyphen (-), 283, 619
I[ Top ]
icons
Access-specific, 27
AutoFilter, 285
for controls, 393
exclamation mark, 331, 467
Message box window, 461
navigation pane, 430
paper clip, 73
pencil, 514
union queries, 202
Wingdings font, 110
ID numbers
AutoNumber and, 149, 629
database design principles, 84
forms and, 365
linking tables with, 148
lookups and, 163
preventing duplicate values with, 119
related tables and, 204
SQL Server and, 658
(see also primary keys)
Identity Increment property, 658
Identity Seed property, 658
If block structure, 526-528
Image control, 331, 396
Immediate window (VB editor), 497
implicit relationships, 151
Import wizard
depicted, 576, 577, 605
importing from text files, 611
saving settings, 614
splitting databases, 578
importing
basic steps for, 605-608
from Excel files, 607-610
file types supported, 604
from text files, 610-611
from XML files, 605, 625, 626
linked tables vs., 685, 686
overview, 598-599, 604
reusing settings for, 614-616
SharePoint data, 685-687
tables, 577, 590
Increment option (New Values field property), 76
Indexed field property, 120
indexes
compound, 122-123
defined, 120
multifield, 122-123
performance and, 121
preventing duplicate values, 119-123
speeding up searches, 195
SQL Server and, 649
Indexes window, 122
InfoPath (Microsoft Office), 628
inheritance, file security and, 595
inner joins, 207, 209
Input Mask property
controls and, 410
prebuilt masks and, 125, 128
Input Mask wizard, 126, 128-130
input masks
creating, 128-130
mask list, 131
overview, 123-125
placeholders for, 129, 130
ready-made, 125-128
special characters for, 129
Insert Hyperlink dialog box, 413
Insert Rows command, 478
installing
SharePoint, 671
SQL Server Express, 641-647
Instr( ) function, 230
Int( ) function, 227
integer division, 217
Integer option
Field Size property, 64
Query Parameter dialog box, 241
IntelliSense (Visual Basic), 509, 512
Internet Explorer browser, 667, 668
IP (Internet Protocol) address, 130
Is Hyperlink property, 451, 452
IsNull( ) function, 489
Item property, 487
ItemsSelected property, 511
J[ Top ]
Jet engine, 13
Join Properties dialog box, 209
join queries
editing limitations, 420
functionality, 149
groupings and, 267
inner joins, 207, 209
modifying information via, 207
multiple joins, 210-212
outer joins, 207-209
overview, 203
parent-child relationships and, 202, 207
relationships vs., 205
tables in, 204-207
update queries and, 246
.jpeg file extension, 419
junction tables
multiple joins and, 210
multi-value fields and, 170, 172
overview, 168-169
Justified option (layouts), 335, 387
K[ Top ]
Keep Together property, 345
keyboard accelerators, 8-10
keyboard shortcuts (see shortcut keys)
keystrokes
assigning macros to, 479, 480
running macros via, 462
unsafe macro actions and, 467
KeyTips feature
commands and, 698
overview, 9
L[ Top ]
label controls
adding, 331, 392-394
deleting, 332
description, 330, 395
expressions and, 346
Label wizard, 306, 336-341
labels, creating, 337-340
.laccdb file extension, 43, 587
Landscape option (Page Layout), 113
landscape orientation, 113, 304, 336
Last option (Total), 236
Layout view
adding fields in, 390
creating forms in, 389, 390
for forms, 361, 365, 377, 378
for reports, 300, 311, 328-329
layouts
adding fields, 390
anchored controls and, 403
automatic, 13
control limitations in, 413
creating for forms, 375-386
defined, 375
Form wizard options, 387
grouping and, 349
moving fields out of, 327-329
page, 113, 306
report options, 335, 336
tabular, 378-380, 386, 406
using multiple, 377-379
LCase( ) function, 228
Left Arrow key, 37
Left property, 510
Left( ) function, 228, 229, 230
Len( ) function, 228, 229, 534
length, text field, 60
less-than sign (<), 488, 526
Like operator
filtering and, 223
validating text, 136
line continuation character, 506
Line control, 331, 396
Link Child Fields property, 421, 441
Link Master Fields property, 421, 441
linked records
cascading updates and, 159
controlling display of, 422
in separate forms, 446-450
Linked Table Manager window, 574
linked tables
bad links in, 574
complex data feature, 13
creating while importing, 606
forms and, 361, 420-422
ID numbers in, 148
importing vs., 685, 686
parent-child relationship and, 206
projects vs., 653
with relationships, 145-179
SharePoint and, 688
split databases and, 573-575
SQL Server and, 651, 654
summary queries and, 239
union queries and, 202
linking/links
adding to documents, 414
creating, 413-415
databases to separate documents, 63
fields with relationships, 151
forms, 413-415
limitations of, 413
to related data, 446-453
reports and, 450-453
List Box control, 395, 442-445
list controls
combo boxes and, 395
navigating with, 415-417, 442-445
List wizard, 442
lists
building, 678-680
deleting, 683
modifying settings for, 682
permissions for, 683
setting options, 690
SharePoint, 670, 688
Lists section (SharePoint), 678
literals
input masks and, 129
math operations and, 218
Locked property, 409
locks
bypassing, 588
editing records and, 587, 638
file security and, 595
for front-end database, 579
for overlapping edits, 586-588
logical operators, 526
logos
arranging for reports, 297, 303, 330
forms and, 391
labels and, 340
Long data type (VB), 525
Long Date format (Date/Time data type), 67
Long Integer option (Field Size property), 64, 76, 255
Long Time format (Date/Time data type), 67
lookup lists
adding values to, 143
populating, 166
relationships and, 162
sorting, 164
Lookup tab (Field Properties), 143
Lookup wizard
lookups with fixed values, 140-143
lookups with related tables, 163, 415
setting allowed values, 57
lookups
creating, 162
data validation, 139-143
domain functions and, 550
filter expressions and, 192
with fixed values, 140-143
linking tables with, 149
primary key and, 163
refreshing, 166
relationships and, 162, 415
lookups (continued)
reports and, 348
SQL Server and, 659, 660
table-based, 142
loops
Recordset object and, 560
repeating actions with, 529, 530
Lotus 1-2-3, 605
Luhn algorithm, 533
M[ Top ]
macro builder, 452, 458
macro groups, 478-479
Macro tab (Action Arguments section), 460
macros
assigning to keystrokes, 479, 480
attaching to events, 484
changing form properties, 487
conditional, 487-493
configuring startup, 480
connecting to forms, 481-487
converting to VB code, 545, 547
creating, 444, 458-462
debugging, 463-465
defined, 24, 444, 457
disabled, 468
editing, 445, 448
emailing data, 475-477
embedded, 462, 485
FindRecord action, 473, 474
front-end database and, 568, 639
managing, 477-481
naming, 462, 480
printing reports, 474, 475
privacy options, 471
Quick Access toolbar and, 700, 702
reading arguments from forms, 484-487
running, 462, 463
running automatically, 462
saving, 462
security and, 465-472
setting up trusted locations, 471-472
triggering, 444
trust center and, 469-471
types of errors, 466
unsafe actions, 465-469
VB programming and, 458
viewing list of actions, 466
mail merge feature, 337, 598
make-table queries, 245, 252-254
Manage Data Tasks dialog box, 615, 616
many-to-many relationship, 168-172, 210
MAPI (Messaging Application Programming Interface), 475
mapped network drives, 572
mapping SQL Server data types, 657
Margins option (Page Layout), 113
margins, report, 306
mask list, 131
masks (see input masks)
matching fields, 148
math operations
building expressions out of, 216
with numeric fields, 217-219
order of, 218
mathematical functions, 226-227
Max option (Total), 236
maximum length
guidelines for, 62
Memo data type and, 60
text fields, 60, 61
.mdb file extension, 30, 171
Medium Date format (Date/Time data type), 67
Medium Time format (Date/Time data type), 67
Memo data type
description, 57
filtering support, 99
lookup support, 140
overview, 60-63
sorting options, 95
SQL Server, 658
validation support, 132
menu commands, 436-438
menu shortcuts, 10
message bar, 471
Message box window
condition example, 492
debugging changes and, 539
depicted, 469
error handling with, 540, 541
MsgBox action, 461
MsgBox command and, 499
Message Text property, 477
Messaging Application Programming Interface (MAPI), 475
meta-language, 617
methods
control, 512
defined, 507
DoCmd object, 544-545
overview, 511-513
Microsoft Access (see Access)
Microsoft Corporation, 640, 641
Microsoft Excel (see Excel)
Microsoft Office Developer Center, 543
Microsoft Office InfoPath, 628
Microsoft Office Security Options dialog box, 250, 469, 500
Microsoft Office SharePoint Portal Server (SPS), 670
Microsoft Office SharePoint Server (MOSS), 670
Microsoft Outlook (see Outlook)
Mid( ) function, 228, 229, 230
Min option (Total), 236
minibar, 63
Minute( ) function, 232
Miscellaneous category, 419
Mixed mode authentication
overview, 645
security and, 645
SQL Server databases, 646, 655
Mod operator, 535
modal dialog, 386
Modified By field (SharePoint), 688
Modified Date mode (navigation pane), 428, 429, 430
modules
adding, 497-498
calling code in, 503-505
custom functions in queries, 532
defined, 24, 496
front-end database and, 639
functions and, 530
modulus, math operations and, 217
monitor resolution, 402
Month( ) function, 232
MonthName( ) function, 232
MOSS (Microsoft Office SharePoint Server), 670
mouseover effects, 518-521
Move method, 512
MoveNext method (Recordset), 560
moving
columns, 90
controls, 397
databases, 653
databases to SharePoint, 688-690
fields, 54
fields in forms, 363
fields in pivot tables, 279
fields out of layouts, 327-329
Web Parts, 676
MsgBox action, 461, 491, 492
MsgBox command, 499
MsgBox( ) function, 492, 552
MSysObjects system table, 442, 443
multifield indexes, 122-123
multiple joins, 210-212
Multiple-Record view, 382
multiplication, math operations and, 217, 219
multi-value fields
overview, 170-171
queries and, 191
SQL Server conversion and, 652
My Computer icon, 649
N[ Top ]
Name field (system tables), 442, 443
Name property, 341, 394, 485
named range, 608
naming
back-end databases, 572
buttons, 419
controls, 516
database objects, 649
databases, 649
events, 503
fields, 33, 80, 81
macros, 462, 480
queries, 197, 216
reports, 308
subroutines, 502
tabs, 412
views, 433
XML tags, 619
navigating
around forms, 366, 367
datasheets, 94-104
designating startup forms and, 438, 439
form tab order and, 406-408
forms capable of, 435-445
form-to-form, 446-453
linking to related data, 446-453
with lists, 415-417, 442-445
print preview, 113
relationships, 159, 161
switchboard alternatives, 439-445
with Tab key, 406-408
Navigation Buttons property, 385, 418
Navigation Options dialog box
depicted, 429
manipulating groups, 432, 433
renaming views, 433
showing system tables, 442
navigation pane
browsing tables with, 47, 48
categorizing, 426-428
configuring navigation list, 426-429
congestion on, 425
creating forms, 361
custom groups, 432-434
depicted, 29
filtering and, 429-431
form modules and, 502
hiding objects, 431
navigation pane (continued)
macro groups and, 479
managing database objects, 49
organizing queries, 190
printing reports from, 304
resizing, 46
running macros in, 462
running queries in, 250
searching navigation list, 433, 434
sort options, 429
tweaking macros, 463
union queries, 202
view options, 429
Navigation section (SharePoint), 673, 675
negative number values
converting, 222
data validation for, 133
Number data type, 63
nested functions, 222
nesting XML elements, 619-621
.NET Framework, 642
networks
corrupt databases and, 591
databases on, 566, 567, 570
distributing front-end databases, 573
mapped drives, 572
placement of back-end databases, 570
SQL Server on, 646
New Database dialog box, 655
New Label dialog box, 338
New Label Size dialog box, 338
New Query dialog box, 662
New Values field property, 76
Normal mode, 36
Not operator
conditions and, 488, 489
filtering and, 223
Notepad, 618
Now( ) function, 135, 229-232
null values (see blank values)
Number data type
considerations when changing, 59
Currency data type and, 66
description, 58
Field Size property, 59, 64
filtering support, 99
Format field property, 65, 66
manipulating AutoNumber values, 255
parent-child relationships and, 152
sorting options, 95
SQL Server, 658
numbers
building expressions out of, 216
copying, 601
decimal, 63, 314
expressions and, 507
formatting, 225, 226
negative, 63, 133, 222
random, 227
rounding, 221
spell checker and, 107
in text fields, 96, 125
validating, 134
numeric data types
filtering support, 100, 101
setting default values, 118
validating, 118
validation support, 132
numeric fields
formatting, 314
Group, Sort, and Total pane, 352
simple math with, 217-219
Nz( ) function, 217, 233
O[ Top ]
Object Dependencies box, 202, 203
object model, 541, 542
Object Type mode (navigation pane)
customizing, 430
description, 427
filtering and, 429
modules in, 498
objects
adding to SQL Server databases, 655-665
database objects and, 507
overview, 507-514
Visual Basic and, 541-547
working with, 514-521
(see also database objects)
ODBC, 605
Office menu
overview, 11, 17
Recent Document list, 42
OLE Object data type
description, 58
Indexed field property, 120
lookup support, 140
validation support, 132
On Before Update event, 528
On Change event
control events, 481
example, 551
macro programming, 483
timing of, 555
On Click event, 444, 481-484
On Close event, 482, 483
On Current event, 482, 483, 527
On Dirty event, 483, 516
On Enter event, 483
On Error statement, 540, 541
On Key Press event, 513
On Load event, 483
On Mouse Move event
example, 514, 520
macro programming, 483
mouseover effects, 518
section events, 481
On Not In List event, 483, 552, 553, 555
On Open event, 482
On Undo event, 483, 518
On Update event handler, 541
OnClick property, 445
OnError action, 466
one-to-many relationships, 150, 169
one-to-one relationships
cautions for, 167
creating, 175
overview, 166
Open Recent Databases section, 26, 42
Open Report command, 418, 444, 452
OpenArgs property, 554
OpenForm action
child forms and, 448
description, 418
finding records, 473
launching forms with, 452
listing forms, 444
OpenForm method and, 544
OpenForm method (DoCmd)
description, 545
example, 543, 547, 553
OpenForm macro and, 544
opening
databases, 26, 41-43
databases in Exclusive mode, 588
multiple databases, 43
multiple subdatasheets, 160
previous version databases, 43-45
queries, 190
tables, 47, 190
Zoom box, 62, 63, 219
OpenQuery method (DoCmd), 545
OpenRecordset method (CurrentDb), 560
OpenReport action, 474
OpenReport method (DoCmd), 545, 558
OpenTable action
arguments to, 460
creating macros, 458
debugging, 463, 464
finding records, 473
OpenTable method (DoCmd), 545
operators
arithmetic, 217
dot, 508
expressions and, 488
logical, 526
(see also specific operators)
Option Button control, 396
Option Compare Database instruction, 498
Option Explicit instruction, 498
Option Group control, 396
Or operator
conditions and, 489, 527
field validation, 137
filtering and, 223, 372, 373
Oracle, 638
Order By On Load property, 385
Order By property, 384
ORDER BY statement (SQL), 200
order of operations, 218
Other tab (Property Sheet), 341, 394, 438
outer joins, 207-209
Outlook (Microsoft), 605, 627-636
out-of-stock orders tutorial, 258-261
overlapping
changes, 584-586
controls, 330, 400
edits, 586-588, 638
fields, 329
windows, 392
P[ Top ]
Page Break control, 331, 396
page breaks
forcing, 349
Page Break control, 331, 396
preventing, 352
setting up, 351
Page Down key, 37, 113
Page Footer property, 344
page footers
description, 325, 344
forms and, 391
Page Header property, 344
page headers
column headers in, 329
description, 324, 344
forms and, 391
page layouts, print preview, 113, 306
page numbers (reports), 297, 304
Page Up key, 37, 113
paper clip icon, 73
paper orientation
changing, 306
landscape, 113, 304, 336
portrait, 113, 304
paper size, 305
Paradox, 605
parameters
action queries and, 244, 652
defined, 514
DoCmd methods, 544
parameters (continued)
domain functions, 550
update queries and, 247
user-defined functions and, 662
(see also query parameters)
parent-child relationships
exporting tables and, 624, 684
join queries and, 202, 207
junction tables and, 169
navigating, 159
overview, 150-152
SQL Server, 660
subform controls, 421
parentheses ( )
function and, 221
function parameters and, 531
math operations and, 218
subroutines and, 513
passwords
password-protection feature, 591, 593
sample mask, 130
split databases and, 593
SQL Server and, 655
Paste Special dialog box, 10
Path property, 520
patterns, validating text, 136
PDF files
exportable file type support, 612
exporting considerations, 310
exporting reports as, 306
overview, 307
saving reports as, 308-309
pencil icon, 514
Percent option
Format field property, 65
Format( ) function, 226
formatting numeric fields, 314
percent symbol (%), 218
performance
indexes and, 121
indexes speeding up searches, 195
projects and, 653
split databases and, 568
SQL Server and, 640, 644
period (.)
modules and, 504, 531
XML tags and, 619
Permissions section (SharePoint), 673
permissions, SharePoint list, 683
phone number mask, 127, 129, 130
Picture Alignment property, 440
Picture property, 419, 440, 510
Picture Size Mode property, 440
Picture Tiling property, 440
pictures
adding to reports, 300
buttons with, 419
clipboard support, 600
controls and, 326
form backgrounds and, 440
image controls and, 331
showing on forms, 369
pivot charts
forms and, 386
overview, 286-287
printing, 288-289
types of, 287
pivot tables
building, 275-278
creating calculated fields, 279-282
features of, 273-275
filtering, 283-285
forms and, 386
forms for, 282
hiding/showing details, 283
manipulating, 279-280
overview, 263
pivot charts, 286-289
PivotTable Field List window, 276-277, 284
placeholders
Expression Builder and, 224
input masks and, 125, 128, 129, 130
plus sign (+), 283, 665
point-in-time data, 178, 551
Pop Up property, 438
Portrait option (Page Layout), 113
portrait orientation, 113, 304
pound sign (#), 136
PowerPoint (Microsoft), 310, 598
Preview Report command, 418
previewing reports, 304, 306
primary keys
design guidelines for, 85
duplicate records and, 78, 121, 166
importing Excel files, 609
lookups and, 163
one-to-one relationships and, 175
preventing duplicate values with, 119
setting, 77-79
SQL Server tables, 656
Print Current Form command, 418
Print Preview mode
changing page layouts, 113, 306
forms and, 369
labels and, 340
overview, 112-113, 304-306
reports and, 296, 302
Print Record command, 418
Print Report command, 418
printing
changing settings for, 302
datasheets, 111-114
fine-tuning printouts, 114
pivot charts, 288-289
print preview feature, 112-113
records from forms, 368, 370
relationships, 176
reports, 304-310
reports with macros, 474, 475
as unsafe macro action, 467
PrintOut action, 467, 474
PrintOut method (DoCmd), 545
private subroutines, 502, 505
procedures, stored, 662
Project window (VB editor)
adding code to forms, 501
depicted, 497
overview, 496
projects
creating, 651
creating queries via, 665
defined, 651
linked tables vs., 653
SQL Server conversion and, 652
properties
bound control, 409
button control, 440
changing form, 487
control validation rules, 410
default, 508
defined, 507
DoCmd object and, 543
field, 56
fine-tuning reports with, 341-345
form, 384, 385
overview, 508-511
pictures on forms, 440
split form, 383, 384
SQL Server, 654
subform controls, 421, 441
tab order, 406, 407
Properties window
relationships and, 660
Visual Basic editor, 497
Property Sheet
adding form controls, 394
attaching macros to events, 484
closing, 139
configuring controls, 443
connecting macros to forms, 482
creating expressions, 410
creating hyperlinks, 452
depicted, 342
exporting tables, 624
fine-tuning reports, 341
functionality, 342
gridlines, 317
property names on, 509
purpose, 53
selecting buttons, 448
split form settings, 383
subdatasheet settings, 162
table validation and, 138
tweaking settings with, 382
public subroutines, 505
Publish As PDF dialog box, 309
Q[ Top ]
queries
analyzing, 199-201
append, 245, 251-256
based on queries, 198
calculated fields, 213-220
creating via projects, 665
custom functions in, 532
defined, 24, 183
delete, 245, 256-258
Design view and, 185-195
domain functions and, 550
filters vs., 102
finding unmatched records, 209
flagging out-of-stock orders, 258-261
front-end database and, 568
grouping and, 346
hiding, 258
inner joins, 207, 209
join, 420
launching, 190
make-table, 245, 252-254
multiple joins, 210-212
naming, 197, 216
opening, 190
outer joins, 207-209
overview, 184
pivot table limitations, 276
projects and, 652, 653
Query wizard, 185, 194-198
reference values on forms, 652
renaming fields in, 217
reports based on, 297
running in navigation pane, 250
saving, 189
saving filters with, 374-375
select, 184, 243, 248
SharePoint views and, 682
SQL Server and, 662
SQL view and, 185, 198-202, 652
summary, 184, 234-240
synchronizing, 216
for system tables, 442
queries (continued)
totals, 234-240, 263, 264, 266, 550
transferring tables and, 649
union, 201-202, 652
update, 245-250
viewing top records, 192-195
way to create, 185
(see also action queries; crosstab queries; join queries)
query functions, 220-233
query parameters
action queries, 244, 652
overview, 240-242
update queries, 247
Query Parameters dialog box, 241
Query wizard, 185, 194-198
question mark (?), 136
Quick Access toolbar
customizing commands, 697-701
customizing databases, 701, 702
depicted, 9
overview, 12, 13
Save button, 56
Undo command, 39
quick search feature, 101
Quit action, 467
Quit Application command, 418
Quit method (DoCmd), 545
quotation marks ("), 191, 547
R[ Top ]
random numbers, 227
Random option (New Values field property), 76
Ray, Erik, 617
RDBMS (relational database management system), 151
read permission, 683
reading
arguments from forms, 484-487
fields on forms, 505, 507
shared databases while, 567
Recalc method (Form), 513, 551
Recent Document list (Office menu), 42
Record Locks property, 588
Record Navigation category, 418
Record Operations category, 418
Record Selectors property, 385, 514
Record Source property, 384, 394
records
action queries and, 244
adding to forms, 367
appending, 251-256
AutoNumber data type in, 75
blank values for unlinked, 157
changing in shared databases, 582-589
changing SharePoint, 691
collect-by-email feature, 627
copying in one step, 38
corrupted, 590
defined, 31
deleting, 35, 256-258
deleting from forms, 368
editing, 35, 37, 583
exporting selected, 613
expressions and, 215
filtering in tables, 97-101
finding unmatched, 209
finding/editing in forms, 366-367
FindRecord action, 473, 474
grouping, 234, 235
identifying before deleting, 257
indicating changes in, 514-518
inserting, 35
linked, 159, 422, 446-450
locking while editing, 587, 638
many-to-many relationships, 168
one-to-one relationships, 166
pivot tables and, 276
printing from forms, 368, 370
searching, 101-104
setting default values, 119
showing multiple, 380-382
sorting in queries, 188
sorting in tables, 94-97
transferring, 252
updating, 245-250
viewing top, 192-195
(see also duplicate records; queries)
Recordset object, 560
Rectangle control
adding, 331
description, 331, 396
formatting, 395
Redo command, 12
redundant data, 84, 146-148
references
adding, 542
circular, 216
References dialog box, 542
referential integrity
relationships and, 154, 156-159, 165
SharePoint support, 690
Refresh All command, 583, 691
refresh interval, 583, 584
Refresh method, 512, 513
refreshes, automatic, 583
Regional and Language Options dialog box, 69
related data, redundant data vs., 146-148
relational database management system (RDBMS), 151
relationship diagram, 155
Relationship window, 155
relationships
Chocolate Store practice, 175-179
defining, 151-155
defining between tables, 204
deleting, 162
editing, 155
error handling and, 151
explicit, 151
forms and, 420
implicit, 151
join queries vs., 205
linking with ID column, 148
lookups with related tables, 162-165
many-to-many, 168-172, 210
matching fields, 148
Music School practice, 172-176
navigating, 159, 161
one-to-many, 150, 169
one-to-one, 166, 167, 175
printing, 176
redundant vs. related data, 146-148
referential integrity, 154, 156-159, 165
SQL Server and, 650, 660-662
(see also parent-child relationships)
Relationships tab
defining relationships, 152, 155-156
one-to-one relationships, 166
Remove Filter command, 99
renaming
database objects, 49
fields in queries, 217
repetition, spell checker and, 107
Replication ID option (Field Size property), 76
replication IDs, 76, 77
report footers
creating, 334
description, 325
setting up, 351
report headers
creating, 334
description, 324
setting up, 351
Report Name property, 452
Report Operations category, 418, 444
report title, 297, 303, 330
Report view (reports), 295, 300, 311
Report wizard, 334-336
reports
adding controls to, 331
adding pictures to, 300
adding/removing fields, 299-300
adjusting settings, 342
arranging, 297, 298
creating, 295-297, 302
creating in Design view, 332-334
defined, 293
exporting, 306-309, 614
expressions and, 345
filtering, 319, 321
fine-tuning with properties, 341-345
formatting, 296, 310-318
front-end database and, 568, 639
grouping, 346-355
hidden fields in, 299
improving in Design view, 323-334
labels as, 340
links in, 450-453
locking down front end, 579
naming, 308
preventing corruption, 590
previewing, 304, 306
printing, 304-310
printing with macros, 474, 475
saving, 297, 334
saving as PDF files, 308-309
SharePoint support, 688
sorting, 321
views for, 300-301
Requery action, 450, 453
Requery method, 507, 511, 513
Require Variable Declarations option, 498
Required field property
Allow Nulls column and, 656
empty values and, 116, 117
manipulating AutoNumber values, 256
support for, 118
resizing
columns in forms, 362
controls, 398
controls automatically, 400-405
report components, 325, 332, 333, 334
rows and columns, 90-91
resolution, monitor, 402
result, function, 530
RGB function, 517
ribbon (Access 2007)
Alt key and, 480
collapsing, 48
creating controls, 397
creating split forms, 383
customizing, 697
formatting datasheets, 88, 89
keyboard accelerators, 8-10
menu shortcuts, 10
overview, 6-8
Print Preview mode, 304
removing filtering, 450
screen sizes and, 15
viewing submenus, 12
Visual Basic editor, 496
rich text, 61, 600
Right Arrow key, 37, 41
Right( ) function, 228, 229
Rnd( ) function, 227
root element, 620, 622
Round( ) function
calculated fields and, 221
depicted, 225
description, 227
formatting numbers, 225
nesting and, 222
rounding numbers, 221
row fields (pivot tables), 276
Row Source property, 443, 659
Row Source Type setting (SQL Server), 659
rows
arranging for reports, 297
copying elsewhere, 600
filtering pivot table, 284
fine-tuning printouts, 114
formatting alternate, 314
picking right groups, 271
pivot charts and, 286
pivot table fields, 276
resizing, 90-91
shrinking, 91
Run dialog box, 110
Run Macro command, 419
Run Query command, 419
RunApp action, 466
RunCommand method (DoCmd), 545, 557
RunMacro action, 480, 493
RunMacro method (DoCmd), 545
RunSQL method (DoCmd), 545
S[ Top ]
safe actions, 466
Save As dialog box, 580
Save As menu command, 41
Save button (Quick Access toolbar), 56
Save command, 12
Save Record command, 418
Save Splitter Bar Position property, 384
saving
changes to clipboard, 585
database diagram, 155
databases, 39
databases as previous version, 29
databases with different names/formats, 41
delete queries, 258
export settings, 614
filters, 374-375
forms, 364
import settings, 614
macros, 462
queries, 189
report export settings, 308
reports, 297, 334
reports as PDF files, 308-309
tables, 35, 55
as unsafe macro action, 467
update queries, 249
scheduling tools, 39, 568
schemas, 621, 625
scientific notation, 314
Scientific option
Format field property, 65
Format( ) function, 226
formatting numeric fields, 314
scrolling through tabs, 413
searching
defined, 94
filter by form feature and, 372
forms and, 366
indexes speeding up, 195
navigation list, 433, 434
quick search feature, 101
records, 101-104
Second( ) function, 232
section events, 481
security
Access support, 14
database objects and, 432
databases and, 591-595
macros and, 465-472
manually splitting databases, 576
opening databases, 42
SQL Server and, 640, 644-646
VB code and, 500
Windows, 594-595
workgroup, 592
select queries
action queries vs., 248
defined, 184, 243
disaster recovery and, 245
user-defined functions and, 662
SELECT statement (SQL)
bound controls and, 394
example, 200
querying system tables, 443
SQL Server and, 659
semicolon (;)
email addresses and, 477
input masks and, 130
SQL and, 200
SendObject action, 475-477
server-side processing
databases and, 638
shared databases and, 570
SQL Server and, 5, 566, 637
service packs, 646
Set Control Defaults button, 395
SetFocus method, 512
SetProperty action, 487
SetValue action, 487, 491
shared databases
approaches for, 565, 566
changing records, 582-589
data corruption and, 589-591
editing conflicts, 584-586
"file already in use" error, 575
overview, 566-568
preparing for, 568-582
security and, 591-595
without Access, 580-582
Shared Documents folder, 570
Shared Documents section (SharePoint), 677, 689
shared folders
permission considerations, 575
shared databases and, 566, 570
SharePoint
connection options, 692
creating team sites, 672-675
customizing sites, 675-677
editing data, 691
exporting tables to, 684-686
front end for, 668
fun features, 677
hidden fields, 688
importable file type support, 605
importing data from, 685-687
installing, 671
integration feature, 14
moving databases to, 688-690
multi-value fields and, 171
offline changes, 691-693
overview, 599, 668-671
sharing data with, 566
transferring data in/out, 668
WSS version, 670
SharePoint Portal Server (SPS), 670
Shell function, 543
Shift key, 480
Shift+F2 key combination, 62, 63, 91, 219, 477
Shift+Tab key combination, 37, 408
Short Date format (Date/Time data type), 67, 68
Short Time format (Date/Time data type), 67
shortcut keys
clipboard, 599
collapsing ribbon, 48
Edit Hyperlink window, 72
editing forms, 367
editing records, 37
Find feature, 102
keystroke combinations, 18
moving around datasheets, 36, 37
opening Zoom box, 63
setting default values, 118
tabular layouts, 379
Show Dialog box, 210
Show Table dialog box
adding tables, 153
creating append queries, 252
creating crosstab queries, 272
creating queries in Design view, 185
creating queries with parameters, 240
creating totals queries, 234
creating update queries, 245
depicted, 186
Queries tab, 198
relationships and, 152
ShowAllRecords method (DoCmd), 545
Simple Query wizard, 195
Single data type (VB), 525
Single option (Field Size property), 64
Single-Record view, 382
single-step debugging, 464, 465, 538-539
Site Administration section (SharePoint), 672
Site Users Web Part, 679
Size option (Page Layout), 113
Snapshot Viewer program, 306, 310
sorting
crosstab queries, 272, 273
defined, 94
fine-tuning printouts, 114
forms, 370
Group, Sort, and Total pane, 350
groups in pivot tables, 279
indexes and, 121
lookup lists, 164
navigation pane options, 429
pivot table support, 274
random numbers and, 227
records in queries, 188
records in tables, 94-97
reports, 321
SQL Server views and, 662, 663
tables, 32
Source Object property, 420, 422
spaces
adding between report rows, 298
control anchors and, 402
controls and, 399
labels and, 339
naming conventions, 80
property names and, 508
sort order of, 96
special characters
Access support, 27
calculated fields and, 216
input masks and, 128, 129
overview, 110-111
in text fields, 96
spell checking, 104-108
Spelling window, 105-106
split databases
corrupt data and, 590
linked tables and, 573-575
manual process, 576-579
overview, 568-573
passwords and, 593
security and, 576
Split Form Datasheet property, 384
Split Form Orientation property, 383
Split Form Printing property, 384
Split Form Size property, 383
Split Form Splitter Bar property, 384
split forms
overview, 382, 386
properties for, 384
relationships and, 420
SPS (SharePoint Portal Server), 670
SQL (Structured Query Language)
analyzing queries, 199-201
DAO and, 560
functions and, 220
text commands in, 198
union queries, 201-202
unsafe macro actions and, 467
validation rules and, 131
SQL Server
Access vs., 5
firewalls and, 646
installing, 641-646
managing databases, 653, 654
multi-value fields and, 171
networks and, 646
security and, 592, 596
sharing data with, 566
switching considerations, 637-641
upsizing databases, 599, 647-653, 657
Web pages and, 582
SQL Server Agent, 654
SQL Server Books Online, 643, 665
SQL Server databases
adding objects to, 655-665
backing up, 654
copying, 654
creating, 647-655
creating tables, 656-662
deleting, 655
restoring, 654
transferring, 654
SQL Server Express
downloading, 643
installing, 641-647
limitations of, 640, 641
Microsoft and, 640, 641
SQL Server Management Studio tool, 643, 647, 653
SQL view, 185, 198-202, 652
Sqr( ) function, 227
square brackets []
assignment statements and, 506
functions and, 225
spaces in field names, 80, 216
stacked bar charts, 287, 288
stacked column charts, 287, 288
Standard option
Format field property, 65
Format( ) function, 226
formatting numeric fields, 314
start tags, 618
startup forms
Access runtime engine, 581
designating, 438, 439
startup macros, 480
StDev option (Total), 236
StopMacro action, 493
stored procedures, 662
storing
information in variables, 524, 525
SharePoint sites and, 671
String data type (VB), 525
string manipulation functions, 230
Structured Query Language (see SQL)
Sub statement, 498, 537
Subdatasheet Expanded setting, 162
Subdatasheet Height setting, 162
Subdatasheet Name setting, 162
subdatasheets
adding and linking support, 178
changing settings, 162
defined, 160
opening multiple, 160
subform control
description, 396
linked records and, 446
overview, 420-422
subreports, 347
subroutines
calling, 531
calling code in modules, 504
defined, 498
events and, 504
functions and, 530
naming, 502
parentheses in, 513
private, 502, 505
public, 505
putting code in forms, 500-507
writing simple, 498-500
subtotals
calculating, 236
placing orders task, 548-551
setting up, 351
subtraction, math operations and, 217, 219
Sum option (Total), 236
summaries
crosstab queries and, 196, 263-273
pivot charts and, 287
pivot tables and, 276
queries performing, 184, 234-240
summary functions
crosstab queries and, 264, 273
pivot tables and, 277
Switchboard Items table, 439
Switchboard Manager, 436, 437, 439
switchboards
alternatives for, 439-445
building, 435-439
defined, 425, 435
synchronizing
databases in SharePoint, 689
queries, 216
syntax, 221, 665
system tables, 442, 443
T[ Top ]
Tab (or Enter) key, 37, 406-408
tab character, 611
tab control, 396, 411-413
Tab Index property, 407
Tab Order dialog box, 407
tab order, setting on forms, 406-408
Tab Stop property, 406
table relationships (see relationships)
tables
adding fields to end of, 54
append query guidelines, 251
appending data to, 606, 614
back-end database and, 568
browsing, 47, 48
bypassing locks, 588
Chocolate Store practice, 176
choosing subdatasheets, 161
clipboard support, 600
collect-by-email feature, 627, 628
combining related, 184
copying elsewhere, 600-601
corrupted records in, 590
creating, 31-35, 656-662
creating shortcuts to, 49
creating validation rules, 137, 139
creating while importing, 606, 614
customizing, 31, 32
data types and, 51, 56-77
database design principles, 79-85
Datasheet view, 52
defined, 24
defining relationships between, 204
depicted, 30
Design view, 52-56
domain functions and, 550
duplicate information in, 82, 83
editing, 35-38
exporting, 571, 577, 624
exporting portions of, 612
exporting to SharePoint, 684-686
filter by selection, 99
importing, 577, 590
indexes and performance, 121
joining in queries, 204-207, 238-240
junction, 168-169, 170, 172, 210
lists and, 671
lookups based on, 142
lookups with related, 162-165
make-table queries, 245
many-to-many relationships, 168
multiple joins, 210-212
Music School practice, 172
naming, 34, 80
one-to-one relationships, 166, 167
opening, 47, 190
ordering records, 96
outer joins, 207-209
overview, 30-31
picking right groups, 271
pivot table limitations, 276
point-in-time data, 551
primary key, 77-79
printing wide, 111, 112
redesign cautions, 202
relationship diagram and, 156
Relationships tab and, 152, 153
removal restrictions, 157
reports based on, 297
required fields in, 629
saving, 35, 55
shared databases and, 567
sorting, 32
splitting, 586
switchboard, 439
system, 442, 443
timestamp fields, 650
transferring to databases, 649
union query and, 201, 202
validation rules for, 137-139
(see also columns; linked tables; pivot tables; rows)
Tables and Related Views mode (navigation pane)
compound forms and, 441
customizing, 430
description, 426
filtering and, 429
modules in, 498
saving macros and, 462
tabs
Access Options window, 45
basic ribbon, 7
collapsing ribbon, 48
contextual, 16
naming, 412
setting order on forms, 406-408
tabular layouts, 378-380, 386, 406
Tabular option (layouts), 335, 387
tags, 618, 619
tasks
adding products during orders, 552-556
assigning, 677
data operations and, 547
getting pricing information, 551
grouping around, 432-434
managing order fulfillment, 556-559
objects and, 507-514
placing orders, 547, 548-551
putting code in forms, 500-507
shipping orders, 547
stored procedures and, 662
updating stock numbers, 559-562
using objects, 514-521
Visual Basic editor, 496-500
Tasks link (SharePoint), 677
Team Discussion Web Part, 679
team sites (SharePoint)
creating, 672-675
customizing, 675-677
Template section (SharePoint), 674
templates
compound forms, 441
creating databases, 26
overview, 26
testing
action queries, 244
assumptions, 537
conditions, 527
credit cards, 532-536
recommendations, 117
Text Align property, 343
Text Box control
anchoring, 403
depicted, 342
description, 330, 395
expressions in, 346
manipulating, 329
removing borders, 411
Text data type
considerations when changing, 59
description, 57
filtering support, 99
length considerations, 60, 61
mask support, 124-128
overview, 58-60
pointing to files, 63
sorting options, 95
text files
exportable file type support, 612
importable file type support, 605
importing from, 610-611
Text option (Query Parameter), 241
Text property, 487, 510
text-based information
controls and, 326, 330
copying, 601
expressions with, 219, 220
filtering support, 100, 101
functions manipulating, 228-230
input masks for, 125-128
numbers in, 96, 125
setting default values, 118
special characters in, 96
validating, 136-137
3-D charts, 288, 289
time information (see date/time information)
timestamp fields, 650
Title and Description section (SharePoint), 673
Title column (SharePoint), 681
To Widest command, 399
Toggle Button control, 395
Top property, 510
top/bottom filtering, 284-285
Total property, 272, 273
totals queries
creating, 234-236
crosstab queries and, 264, 266
domain functions and, 550
grouping, 236-238
joins in, 238-240
overview, 263
transactions, SQL Server and, 640
TransferSpreadsheet action, 464
TransferText action, 463
Trim( ) function, 228
trust center, 14, 469-471
trusted locations
configuring, 472
defined, 43
locking down front ends, 579
macros and, 470
setting up, 471-472
VB code and, 500
trusted publishers, 469, 470
Try It text box, 126
tutorial, out-of-stock orders, 258-261
Type column (SharePoint), 679
Type field (system tables), 442, 443
U[ Top ]
UCase( ) function, 228
unbound controls, 519
Unbound Object Frame control, 396
UNC (Universal Naming Convention), 414
UNC path, 572
underscore (_)
line continuation character, 506
XML tags and, 619
Undo command
automatic save feature and, 39
cascading deletes and, 158
form changes and, 367
moving fields between layouts, 378
Quick Access toolbar, 12
Undo method, 512
Undo Record command, 418
unfreezing columns, 94
Unhide Columns dialog box, 92
unhiding columns, 92
UNION ALL statement (SQL), 201
union queries
creating, 201-202
SQL Server support, 652
UNION statement (SQL), 201
universal dates, 134
Universal Naming Convention (UNC), 414
Unrelated Objects category, 498
unsafe macro actions, 465-469
Up Arrow key, 37
UPDATE command (SQL), 562
update queries, 245-250
updates
blocked, 250
cascading, 158
Design view and, 55
linked tables, 574, 575
modifying records, 245-250
shared databases and, 567, 568, 582-589
upsizing databases
SharePoint and, 688
SQL Server and, 599, 647-653, 657
Upsizing wizard, 647-653, 655
Use Control Wizards button, 392
Use Trusted Connection checkbox, 649
User Information List table, 688
user names, 594
User Tasks Web Part, 679
user-defined functions, 662
V[ Top ]
Val( ) function, 227
Validation Rule property, 132, 410
validation rules
conditional logic and, 527
conditions and, 489-491
credit card example, 532-536
date expressions, 134, 135
expressions for numbers, 134
field, 132-137
filters and, 193
for forms, 409, 410
manipulating AutoNumber values, 256
overview, 131
SQL Server and, 649
table, 137-139
for text, 136-137
Validation Text property, 132, 410
Value property, 509
Var option (Total), 236
variables
creating, 524
debugging, 539
defined, 524
identifying data types for, 525
parameters as, 514
storing information in, 524, 525
typos in, 498
VBA (Visual Basic for Applications), 542
Vieira, Robert, 640
views
creating, 662-665
for forms, 364, 365
naming, 433
navigation pane options, 429
queries as, 191
reports and, 300-301
SharePoint, 682
split forms, 382
SQL Server and, 662
switching, 17
Visible property, 487, 510
Visual Basic editor, 496-500
Visual Basic for Applications (VBA), 542
Visual Basic IntelliSense, 509, 512
Visual Basic language
converting macros, 545, 547
debugging with, 536-539
error handling with, 498, 539-541
functions and, 220
Visual Basic language (continued)
macros and, 458
objects and, 507-514, 541-547
overview, 523-536
putting code in forms, 500-507
reference help, 500
running businesses with, 547-562
types of errors, 535, 536
unsafe macro actions and, 467
using objects, 514-521
W[ Top ]
Web Parts
adding, 677, 679
changing, 677, 678
defined, 675
minimizing, 676
moving, 676
removing, 676
Web Site Address section (SharePoint), 673
Webb, Jeff, 677
WeekdayName( ) function, 232
Where Condition property, 448, 449, 452
WHERE statement (SQL), 200, 443
Width property, 510
width, column, 91
windows
free-floating, 385, 467
overlapping, 392
unsafe macro actions with, 467
Windows authentication mode, 645
Windows Calculator, 543
Windows environment
buttons face-lift, 397
monitor resolution, 402
regional settings, 68
Windows Explorer, 653
Windows file security, 594-595
Windows SharePoint Services (WSS), 670
Windows Task Scheduler, 39, 568
Windows Update, 645
Windows Vista
SQL Server and, 642, 646
Windows XP facelift and, 397
XPS format, 307
Windows XP, 397
Wingdings font, 110
wizards
acwztool project, 497
for controls, 392
moving databases to SharePoint, 688
splitting databases, 569, 570-573
(see also specific wizards)
Word (Microsoft)
Attachment data type support, 72
exportable file type support, 612
exporting reports to, 306, 307, 310
launching, 543
mail merge feature, 337, 598
special character support, 110
spell checking in, 105
workbooks, 608
workflow, 446
workgroup security, 592
worksheets (Excel), 608
workspaces, document, 672
writing fields on forms, 505, 507
WSS (Windows SharePoint Services), 670
X[ Top ]
XML (extensible markup language)
Access support, 616, 621-623
exporting to, 612, 623-625
importing from, 605, 625, 626
overview, 616, 617
rules of, 618-621
schemas and, 621
.xml file extension, 621
XPS format
exportable file type support, 612
exporting reports as, 306
overview, 307
.xsd file extension, 623
.xsl file extension, 623
Y[ Top ]
Year( ) function, 232
Yes/No data type
description, 58
filtering support, 99
lookup support, 140
overview, 71
setting values for, 507
sorting options, 95
SQL Server, 658
Yes/No option (Format), 226
Z[ Top ]
zip code mask, 130
Zoom box
displaying column contents, 91
opening, 62, 63, 219
zoom feature, 113, 305
Return to Access 2007: The Missing Manual
©2009, O'Reilly Media, Inc. (707) 827-7000 / (800) 998-9938 All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners. |
About O'Reilly
Academic Solutions Authors Contacts Customer Service Jobs Newsletters O'Reilly Labs Press Room Privacy Policy RSS Feeds Terms of Service User Groups Writing for O'Reilly |
Content Archive Business Technology Computer Technology Microsoft Mobile Network Operating System Digital Photography Programming Software Web Web Design |
More O'Reilly Sites
O'Reilly Radar Ignite Tools of Change for Publishing Digital Media Inside iPhone O'Reilly FYI makezine.com craftzine.com hackszine.com perl.com xml.com Partner Sites InsideRIA java.net O'Reilly Insights on Forbes.com |