C

calculated values

in reports, 830–840

adding print date and page numbers, 830–833

adding values across group, 837–838

avoiding #Error in calculated control, 836

performing calculations on detail line, 834–837

storing in table, 1114

calculating

data on detail line, 833–837

grand total using percentages, 840, 843–844

stored value, 1114

totals on expressions, 838

totals and filtering, 441–442

calendar

ActiveX Calendar control, 729–730

creating SharePoint list from Access for, 1199–1203

providing Visual Basic, 1063–1069

Calendar Active X control, 729–730

call stack, 959–960

Call Stack dialog box, 960

Call statement, 1017

callbacks

creating for Ribbon in VBA, 1296–1297

Ribbon control, 1288

RibbonX, 1290–1292

calling

functions

domain, 1057

syntax for, 1542

MsgBox function in macro, 911

candidate keys, A20

Can Grow property, 1119

Caption property

displaying, 586, 587

for fields, 377

renaming fields and changing, 216

captions

correcting PivotCharts data field, 482

displaying property for, 586, 587

setting label properties for form, 585–586, 587

capturing point-in-time data, A29-A31

caret (^)

arithmetic operator, 365

operator precedence for, 367

testing position of characters in project file, A49

Cartesian product

defined, 414

queries returning, 498

Cascade Delete Related Fields check box (Edit Relationships dialog box), 184, 185

Cascade Update Related Fields check box (Edit Relationships dialog box), 184, 185

cascading updates/deletes support, 1218

case

indicating upper- and lowercase characters, 655

insensitivity of character string comparisons, 170

categories

Access Options dialog box, 87–97

Add-Ins, 95

Advanced, 93

Current Database, 88–89

Customize, 94

Datasheet, 90

Object Designers, 90–91

Popular, 87–88

Proofing, 92

Resources, 96–97

Trust Center, 96

macro action

building custom menu and executing menu commands, A112

controlling display and focus, A113-A114

displaying messages or beeping, A115

executing queries, A106-A107

handling errors, A118

modifying objects, A115-A117

opening/closing Access objects, A102-A104

overview, A101

printing data, A105-A106

running another application, A118

searching for data, A111

setting values, A109

testing conditions and controlling action flow, A107-A108

Trusted column, A101

using temporary variables, A117

Navigation Options dialog box

customizing, 58

display order for, 61

working with groups in custom, 59–63

Navigation Pane

displaying single, 81

hiding custom groups on, 67–69

Object Type, 61, 82, 105–106, 114, 115

Tables And Related Views, 49–52

Unrelated Objects, 51–52

using custom, 53–55

viewing categories from submenus, 77

CategoryDescription field, 604–605

cells, setting options in Datasheet Formatting dialog box for, 1146

Change event

description of, 872

preventing endless loop with, 873

characters.

See also placeholder characters;

and Symbols and numbers section

fill character embedded in format string, 653

formatting

Date/Time, 658–660

Number and Currency, 653–654

Text data type, 655–656

input mask

list of, 170–171

listing of, 170–171

missing, 174

storing formatting characters with data, 173

using to separate table and field names, 372, 373

valid field name, 159

Visual Basic data-typing, 962

wildcard characters for string comparisons, A49

check boxes.

See also specific Access check boxes

about, 526–527

changing Yes/No field to, 605–607

graphic image size unchanged in, 615

option buttons and toggle buttons vs., 606

check constraints

adding, 1478–1480

allowing Null values with, 1482

creating multiple, 1481–1482

defined, 1477–1478

deleting, 1480

maintaining special unique value constraint, 1077–1080

Check Constraints tab (Properties dialog box), 1478

child tables, 185

Choose Builder dialog box, 905, 906

Choose Name dialog box, 1466–1467

chtProductSales form, 538

Class Module button, 1330

class modules, 1009–1016

defined, 943, 1009

form and report, 944

Private statement in, 972–973

Property Get procedure, 1010–1012

Property Let procedure, 1012–1014

Property Set procedure, 1014–1016

Public statement in, 973–974

signaling declared events in, 1022–1023

understanding, 1009–1010

using Event statement in, 971–972

clean-up code for Contacts.aacdb, 1312–1315

Clear All Sorts button, 402

Clear MacroError action, 916

CLI (Common Language Interface), 254

client computers

advantages of local copies of applications, 1320

browsers executing scripts on, 1138

setting up desktop database for, 1319, 1320–1328

client/server applications

building, 137–138

designing data fetching for, 1321

setting up desktop database for, 1319, 1320–1328

Clipboard

copying and pasting fields, 222–224

cutting and moving objects to, 210

Office vs. Windows, 395

pasting text into Page Header section, 845

Close Button property, 675

closing

Access objects with macro, A102

database and compacting, 251

events for form and report, 870–871

property sheets in Design view, 576

SQL designer, 1530

windows without Close button, 675

clustered indexes, 1471–1472, 1474

code.

See also complex Visual Basic code examples

ASP script generated in VBScript, 1158

AutoKeys macro, 1316

book’s convention for displaying long lines of, 925

disabled in databases downloaded to untrusted locations, 1225

hexidecimal code for color names, A93-A99

HTML tag coding, 1129–1131

indenting Visual Basic, 948–949

sample database exit, 1312–1315

viewing executed code in Call Stack dialog box, 959–960

Code window (VBE), 945, 946–947

Collapse Field button, 476

collapsing

fields, 476

Navigation Pane

to desired width, 47

group in, 49

hides contextual tab, 739

Ribbon, 45

Collect Data Through E-Mail Messages wizard. See collecting data via e-mail

Collecting Data Using E-Mail Options dialog box, 313–315

collecting data via e-mail, 304–345.

See also HTML forms;

InfoPath forms

about, 304–305

allowing multiple rows per reply, 314–315

collecting new or updated data, 308–309, 329–330

data types unavailable, 311

e-mail addresses

finding in associated table, 317–318

selecting source for, 315–316, 333–334

selecting table containing, 316–317, 332–333

HTML forms

filling out, 322–325

InfoPath forms vs., 327

selecting recipients for, 320–321

using, 306–308

Hyperlink data types in, 321

including primary key in data collection, 310

InfoPath forms

filling out, 337–341

HTML forms vs., 327

using, 328–329

warning messages for, 335–336

writing message for, 334–335

instructing recipient to click reply, 319

messages

customizing, 318–319, 334–335

managing and resending, 343–345

moving HTTP-based messages to Outlook Inbox, 325

previewing in Outlook, 321–322

sending, 336–337, 340–341

replies

accepting multiple, 314

date to stop processing, 315

discarding, 314

manually processing, 321–322, 341–343

processing automatically, 312–313, 325–326

specifying data to collect, 309–310, 330–331

wizard’s summary of process, 320

collections

about, 978

Access architecture and, 980

established by DBEngine object, 983

QueryDefs, 984

referencing Forms and Report, 919

referencing objects within, 987–988

Report Design Tools, 759, 761

structure of DAO, 983, 984

TableDefs, 984

TempVars, 991, A117

color

Button Face, 642

customizing input form, 587–589

formatting control border, 583–584

highlighting form items with, 645–646

names and codes for, A93-A99

report background, 788–789

selecting, A93

setting

3-D object, 565

default datasheet, 1144

form grid dot, 564–565

system, 647

Column Fields drop zone, 474

column headings for crosstab queries, 445–446

Column Width lookup property, 244

Column-Name clause, A35-A37

columns

about, 99

adding labels in simple reports, 770–771

aligning stacked control layout, 637–638

attributes stored in, 5,

data types in SQL Server, 1461–1464

Datasheet view of, 385

defined, 4

defining properties for stored procedure, 1523–1524

moving or deleting, 150

project

adding definitions to, 1465–1467

allowing Null values with check constraints, 1482

database fields vs., 1458

defining, 1459–1465

defining as primary key, 1470–1471

defining for tables, 1475–1477

properties not visible in SQL Server 7.0, 1470

properties of SQL Server table, 1467–1470

sql_variant data type, 1465

user-defined data types, 1465

selecting query designer, 1499–1450

SharePoint list

about, 1168

adding, 1175–1179

browser viewing of custom, 1205

setting properties for, 1176, 1177

sizing for combo box, 603

specifying query options for ordering and sorting, 1503–1504

SQL Server naming conventions for, 1460

Columns tab (Page Setup dialog box), 558, 753–754

COM add-ins, 95

Combo Box button, 569

Combo Box Wizard, 602, 604–605

combo boxes

about, 528

advantages of using, 601

allowing for vertical scroll bar in, 244

associating macro with, 923

building, 602–605

choosing values resolved with stored procedure parameter, 1564, 1575–1576

conditional formatting of, 716

defined, 240

designing for InfoPath data entry, 337–338

filtering one list with another in, 1095–1097

formatting properties for data display, 651–661

illustrated, 529

keyboard shortcuts for, 543

manually changing text boxes to, 602

retrieving fields from current row of, 1055

sizing columns for, 603

viewing properties created by Combo Box Wizard, 604

watching in Datasheet view, 241–242

width unadjusted with Size To Fit command, 614

comma (,) as thousands separator, 653

command buttons

defined, 517

placing in forms, 535–536, 537

command-line options for application shortcuts, 1333–1334

commands

Back Up Database, 205

Compile, 1317

control padding, 638–640

Copy, 207

Count Values, 804–805

Cut, 210, 394–395

Datasheet, 592–593

executing

Access, 1027–1028

with DoCmd object, 1005

menu commands with macro action, A112

from table’s shortcut menu, 106–107

Visual Basic in Immediate window, 952–953

ExportXML, 1256

External Data tab import and export, 1183

found on Design contextual tab for update queries, 488

ImportXML, 1256

Microsoft Office Button, 25–27

Multiple Items, 592

New Item, 1169

Option Button, 714

Quick Access Toolbar, 27–30

adding to from Ribbon, 34

inserting separators on, 33

revising order on, 32

quick create

forms built with Form Wizard vs., 593

input forms, 590–593

location on Ribbon, 590

Report, 775–777

RunMacro, 939

screen resolution and alignment, 611

ScreenTips for, 104

SharePoint

Create Column, 1176

Modify This View, 1176, 1177

Size To Fit, 612–616

SQL underlying Access database, A33

Table Templates, 153–154

Update Status Bar Text Everywhere ContactID Is Used, 233

used in SQL data definition queries, 463

comments, 1528

COMMIT TRANSACTION statements, 1535

Common Language Interface (CLI), 254

compacting databases, 250–251

companion CD

ContactsDataCopy.accdb sample database, 305

ContactsSQL.mdf file, 1456

database files not digitally signed on, 35

sample files

modifying table design for, 204

on, 23, 103

working with project files, 1494–1495

TasksEmailCollection.aacdb sample database, 305

Visual Basic functions on, 371

comparison operators

Between, In, and Like operators in, 360–361

criteria selection with AND and OR, 357–360

comparison predicate, A37-A38

comparison values in field validation expressions, 168

Compatibilities tab (Properties dialog box), 1334

Compile command, 1317

Compile On Demand check box (Options dialog box), 949–950

compiling

application before production, 1316–1317

errors with procedures within procedures, 947

flagging undeclared variables during, 963

setting Visual Basic options for, 949–950

version of .mdb or .accdb files, 272

Completing Microsoft SQL Server 2005 Setup page (Microsoft SQL Server Installation Wizard), 1357–1358

complex data

about, 8,

manipulating data types with DAO, 997–1000

Multi-Value Lookup Fields and, 245

complex queries, 413–483

about, 413

assigning data types for parameters, 451

building query on query, 421–424

controlling output of, 452–454

creating in Query Wizard, 431–434

customizing properties, 452–463

data definition queries, 463

defining subdatasheets, 458–462

designing PivotCharts, 478–483

filtering, 440–442

including parameters in, 449–452

joins

inner, 414–421

outer, 425–431

locking records in, 462

protecting data integrity, 413

saving results in temporary table, 496

setting up for PivotTables, 469–473

types of

crosstab queries, 442–449

pass-through queries, 463

totals queries, 435–449

union queries, 463–468

update limitations for fields in, 468–469

working with unique records and values, 454–458

complex task automation, 1105–1114

calculating stored value, 1114

linking to related task, 1098–1099, 1109–1114

triggering data task from related form, 1105–1109

complex Visual Basic code examples, 1030–1050

examining all error codes, 1044–1050

code listing, 1045–1047

explanation of code listing, 1047–1050

randomly loading data, 1030–1044

code listing, 1032–1040

explanation of code listing, 1041–1044

computer requirements for Microsoft Office, 1341

concatenating

desktop database expressions with & character, A40

example of concatenated text fields, 363

fields or strings with & character, 362, A40

Null values in expressions, 364, 420

project file expressions with+character, A40

text strings, 841–842

Condition column (Macro window), 891, 921, 922

conditional expressions

macros use of, 900–901

using DLookup and IsNull functions in macros, 930–931

conditional formatting

using in forms, 716–719

using in reports, 848–851

Conditional Formatting dialog box, 717, 848

Configuration Options page (Microsoft SQL Server Installation Wizard), 1354, 1355

confirmation dialog boxes

append query, 507

confirming deletion, 396

delete query, 510

Microsoft Office Genuine Advantage, 145, 146

pasting, 396

update query, 489, 491

verifying rows pasted into new table, 500

Connect dialog box, 1214

Connect property, 297

connections

choosing server, 1450–1451, 1454

security for SQL Server, 1450–1451, 1455

startup code to verify and correct linked table, 1323–1328

Conrad Systems Contacts sample database. See Contacts.accdb database

Const statement, 965

constants

declaring with Const statement, 965

double or single quotation marks enclosing text string, 362

Visual Basic, 961–978

built-in for macro actions, 1028

data types supported, 961–963

defining scope of, 963–964

storing data as, 961

constraint expressions

adding, 1478–1479

examples of, 1481–1482

multiple, 1481

constructing applications, A5

contact events.

See also events

defined, A10

contact labels. See mailing labels

contacts, defined, A10

Contacts template, 152

Contacts.accdb database

about, A7

exploring tables in, 133–135

main switchboard form for, 535–536, 537

migrating to SQL Server with, 216

opening project file, 133

Contacts.adp project file

connecting to database, 1492

opening, 133

steps required to work with, 1494–1495

ContactsDataCopy.accdb database, 305

ContactsSQL.mdf file, 1456, 1492

ContactTracking.accdb, 204

content

allowing browsers to view blocked, 1245

blocking harmful, 35–36

controls sizing form to fit with, 612–616

disabled in databases downloaded to untrusted locations, 1225

enabling for databases, 916

macros and security, 34–35

contextual tabs.

See also specific tabs

adding built-in groups to, 1276–1279

Arrange, 564, 618

creating for custom Ribbon, 1274–1275

Design

editing relationships from, 184

illustrated, 122, 564

query commands on, 488, 509

running reports from, 121

disabling unused, 1283–1284

found on Form Design Tools tab, 567

hidden when Navigation Pane collapsed, 739

Print Preview, 739

report, 761

Report Design Tools, 121–122, 761

Ribbon, 41, 104

setting focus to, 1301–1302

continuation indicator (...), 130

continuous forms

building in Form Wizard, 697–698

example of, 520–521

showing all fields in, 591–593

Continuous Forms view

advantages of, 696

filtering one list with another in, 1095

viewing subform in, 700–701

Control Alignment group

aligning control layouts from, 637–638

snapping items to grid from, 618, 619

control IDs for built-in controls, 1277

Control Layout group, 617, 618

control layout indicator, 599

control layouts

advantages of, 626

converting, 798–800, 801

illustrated, 599

moving controls within, 626–630

removing, 598–599, 632–635, 640–642, 800

selecting all controls in, 639

stacked and tabular, 626, 797

control padding commands, 638–640

Control Padding (Control Layout group), 639

control sources, 566

control-of-flow statements

Call, 1017

Do...Loop, 1017–1018

For...Next, 1018–1019

For Each...Next, 1019–1020

GoTo, 1020–1021

If...Then...Else, 1021–1022

RaiseEvent, 1022–1023

Select Case, 1023–1024

Stop, 1024

text stored procedures, 1531–1535

Visual Basic, 1016–1026

While...Wend, 1025

With...End, 1025–1026

controls

ActiveX Calendar, 726–730

adjusting

Form Wizard, 598–599

layout of form, 616–617

aligning custom form

in Design view, 619–622

in Layout view, 625–626

anchoring in Layout view, 623–624

application’s compatibility with ActiveX, 1068

applying Windows-themed form, 559, 596, 692

arranging in formatted columns, 630–631

associating macro with combo box, 923

avoiding #Error in calculated, 836

bound and unbound, 566

changing content with Change event, 873

command buttons, 517, 535–536, 537

conditional formatting for form, 718

control sources, 566

copying and pasting embedded macros in, 907

counting total events with, 804–807

defined, 526

defining

defaults as AutoFormats, 681

responsive Report view, 749–750

deleting and attaching labels for, 581

dragging to new column, 634–635

editing form inside subform, 709

embedded macros saved with form or report, 904

enabling and locking, 584–585, 662

formatting properties for, 651–661

Date/Time formats, 657–660

displaying Null values, 657

numbers and currency, 652

text, 655–656

Yes/No fields, 660

grouping and moving, 642–643

inserting page numbers in, 806

list and combo boxes, 527–529

loading images into custom Ribbon, 1299–1300

locking button on form, 573

methods for snapping to grid, 618

modifying Report Wizard properties, 787, 788

moving

in Layout view, 118–119

one or more horizontally or vertically, 621

and sizing, 580–581

within control layouts, 626–630

option buttons, check boxes, toggle buttons, and option groups, 526–527

Partially Enclosed vs. Fully Enclosed selection options, 612

placing into control layout, 635–638

programming in Report view, 127–128

property sheets for, 116–117

resizing

in Layout view, 631–632

report’s text box, 797–798

and repositioning Report Wizard, 785–787

using property sheet, 633

Ribbon

attributes for, 1288–1289

list of, 1290–1292

rules for referencing form and report, 920

selecting all

in control layout, 639

in vertical or horizontal area, 588, 600

setting

borders of, 583–584

control padding, 638–640

defaults for form, 680–683

form tab order, 662–663

label properties for, 585–586

other properties for, 666–669

sizing

to fit form content, 612–616

subform, 704

text box controls for reports, 772–773

smart tags added to, 663–665

snapping to grid, 617–619

tab, 719–723

about, 530

property settings for, 723

setting Page Index property, 721

types of, 719

working with, 720–722

toggle buttons, check boxes, and option buttons, 605–607

validation rules for, 670

Controls group

border formatting with, 583–584

buttons on

list of, 568–572

Special Effect, 645–646

illustrated, 568–572, 761

Option Button command, 714

placing bound controls on form from field list, 573–574

conversion functions, A86-A87

Convert Form’s Macros To Visual Basic button, 938–939

converting

Access from previous release, 1358–1360

data types, 226–230

action query data conversion errors, 512

conversion effects on field contents, 226

dBASE-to-Access conversions, 264

dealing with conversion errors, 231–232

Hyperlink, 229–230

limitations on data type conversions, 226–229

Paradox-to-Access conversions, 266

SQL-to-Access conversions, 270

Visual FoxPro-to-Access conversions, 270

macros to Visual Basic, 938–940

queries

fields discarded going from select to action, 490

select to update, 488

stacked and tabular control layouts, 798–800, 801

coordinates for report border, 1120

Copy command, 207, 394

copying

fields, 222–224, 593

objects with macro action, A115

and pasting

in Datasheet view, 394–396

embedded macros, 907

correlated subqueries, A39, A66, A67

Count function, 437

Count Values command, 804–805

counting

rows with stored procedures, 1524

total events with controls, 804–807

Create A New Data Source To SQL Server wizard, 256–258

Create Column command (SharePoint), 1176

Create Database permissions, 1452

Create Digital Certificate dialog box, 1337

Create New Data Source wizard, 256

Create New List wizard

creating and naming custom list, 1203–1204

specifying name for list, 1199–1200

Create Relationship dialog box, 1487

Create tab, 24, 42–43

Create UNIQUE check box (Properties dialog box), 1472, 1473

CROSS JOIN, A43

cross-browser issues, 1131

crosstab queries, 442–449

creating, 442–447

Design view of, 442, 443

displaying multiple value fields in, 444

filling empty cells with zero values, 446–447

function of, 442

GROUP BY clauses in, A44-A45

partitioning data in, 447–449

pivoting with PivotTable vs., 444, 469–470

Query Wizard for creating, 431

sample results desired from, 442, 443

sort order for columns in, 445–446

specifying column headings for, 445–446

unable to change data in, 468

viewing design in Datasheet view, 445

Ctrl key

intercepting Ctrl+F4 combination with AutoKeys macro, 1315–1316

noncontiguous selections with, 579, 1082

currency

formatting characters for, 653–654

formatting properties for, 652

Currency data type

about, 158, 160

converting, 228

displaying Null values for, 657

property for, 162, 652

currency fields, 373

Current Database category (Access Options dialog box), 88–89

custom form controls

aligning in Design view, 619–622

aligning in Layout view, 625–626

custom forms. See customizing, forms

custom groups.

See also groups

creating object shortcuts in, 63–67

dragging and dropping objects into, 66

hiding, 67–69

modifying in custom categories, 59–63

renaming, 59, 60

working with, 53–55

Custom Properties dialog box, 729

custom query by form, 1086–1093

Customer Experience Improvement Program, 19, 20

Customize AutoFormat dialog box, 681

Customize category (Access Options dialog box), 28–29, 94

Customize install option

first-time installations with, 1343–1345

upgrading with, 1348

customizing.

See also custom groups

categories for Navigation Pane, 53–55

forms, 609–683

adding fields, 610–611

adjusting control layout, 616–617

colors and special effects for, 645–646

completing form design, 640–642

control anchoring, 623–624

control padding, 638–640

creating blank form, 609

defining tab order, 662–663

dragging and dropping field in Layout view, 623–624

enabling and locking controls, 584–585, 662

fonts, 648–650

formatting column of controls, 630–631

formatting properties, 651–661

Layout view’s advantages for, 623, 642

lines and rectangles, 642–645

lining up controls, 619–622, 625–626

moving controls within control layout, 626–630

placing controls into control layout, 635–638

properties controlling appearance, 670–680

removing control layout, 632–635, 640–642

scroll bar, 661

setting control validation rules, 670

setting form and control defaults, 680–683

sizing controls to fit content, 612–616

sizing window to fit form, 622

snapping controls to grid, 617–619

property sheets, 116–117

query properties, 452–463

Quick Access Toolbar, 28–29

Ribbon, 1266–1279

adding built-in groups to tabs, 1276–1279

building well-formed XML for, 1270–1271

creating USysRibbons table, 1266–1269

creating VBA callbacks, 1296–1297

customizing tabs, 1274–1275

disabling unused tabs, buttons, and groups, 1283–1284

displaying errors, 1274

dynamically updating elements, 1297–1299

explanation of XML code listing, 1294–1296

hiding existing elements, 1271–1272

hiding options on Microsoft Office Button, 1300–1301

loading images into custom controls, 1299–1300

loading into Access data projects, 1286

loading XML for, 1284–1287

setting focus to tab, 1301–1302

testing, 1270, 1272–1273

using RibbonX attributes, 1287–1296

XML code listing for, 1292–1294

Cut command, 210, 394–395

cutting

changing row sequence by sorting or, 396

and moving data, 210, 394–395

Cycle property, 726, 1080