F

Feature Selection page (Microsoft SQL Server Installation Wizard), 1352, 1353

Featured Online Templates (Getting Started screen), 21, 22

feedback on application design, A6

field independence, A24–A26

field list

displaying all table fields in, 563–564

dragging fields to form design grid from, 578–579

illustrated, 564

placing bound controls on form from, 573–574

replacing with property sheet, 565

selecting all fields in, 578

undocking, 122

Field List button, 476

field names

AutoCorrect options for, 192–193

brackets around, 362

changing, 212–217

characters for separating table and, 372, 373

duplicate, 836

fully qualifying, 372

including spaces in XML, 1263

long names truncated when exported to dBASE or Paradox, A80

omitting spaces in, 159, 216

renaming in queries, 419

requirements in expressions, 362

separating names of SQL tables and, 373

specifying in Import Text Wizard, 289

using in simple queries, 377–378

valid characters for, 159

fields.

See also field names;

selection criteria

Access project columns vs., 1458

adding to custom form, 610–611

adding to table template, 1261–1264

adjusting width of in Layout view, 802, 804

attributes stored in, 5,

bug causing incorrect positioning on form grid, 627

captions

changing PivotTable, 477

as column header for data exported to spreadsheet, A80

changing, maximum length of data, 230–231

concatenating with & character, 362

conditional formatting for form, 716–719

converting spreadsheet Zip, 275

copying, 222–224

creating relationship on multiple, 186–187

data types

conversions affecting contents of, 226

listing of, 157–161

unable to change if joining tables, 204

unavailable for data collection, 311

defined, 4,

defining, 156–157, 166–167

order of table, 178

deleting, 225

design concept of, A17

displaying Null value in text, 657

dragging and dropping

into Query window, 352

in simple input form, 578–580

editing on one side of join, 419

entering selection criteria for, 355–356

formatting Rich Text form, 324

generating error messages for, 168

inserting, 220–222

hyperlinks in, 398–400

keyboard shortcuts for, 542–543

linking tables with field definitions, 181–183

list of PivotTable, 474

modifying

data selected in, 392

sequence of in table, 219–220

in table template, 1264–1265

moving, 217–220

Multi-Value Lookup, 245–248

Null, 166

outputting all in query, 452–453

positioning in Layout view, 802

presetting values to ensure data integrity, 935–938

properties

for captions, 377

changing, 232–233

defining Lookup properties, 353

linking with subform control, 705

Lookup tab, 242

setting, 161–165

setting Description, 156

setting query, 353–355

viewing, 107–108

viewing for bound combo box, 604–605

viewing in Datasheet view, 354

query updating multiple, 491–493

referencing update query expressions, 492

renaming template, 154

report groupings effect on, 818

Rich Text Format support for SharePoint text, 1170–1171

selecting

all in field list, 578

all table, 352, 353

in Form Wizard, 593–594

multiple, 579

as report group, 762–763

Report Wizard, 778

as source for stored e-mail address, 315–316

SharePoint Services, 1218, 1219

sorting query recordsets by, 378–380, 421

status set with toggle buttons, check boxes, and option buttons, 605–607

unable to set default in date/time, 1063

uniqueness of, A19–A20

update limitations in queries, 468–469

uses of, 99

using in report calculations, 834

USysRibbons table, 1266

validation rules for, 165, 168–170, 381–382

viewing form’s, 115–116

File Location tab (Microsoft Office dialog box), 1345, 1348

File New Database dialog box

building new SQL Server database from, 1448, 1449

opening, 144

selecting folder to save database, 146

using existing SQL Server database, 1452–1453

File Open dialog box, 262

file system functions, A90–A91

File Transport Protocol (FTP), 1138

files

.accdb

about, 6,

backing up, 205–207

compiling, 272

components of applications, 978

DAO model with desktop applications, 981

databases vs. projects, 1450

packaging and signing, 1338

publishing or moving to SharePoint Services, 1210, 1212

return DAO recordset for Recordset property, 1104

user-level security unsupported for, 12

.accde, 1330

.accdr, 1329

Access, 6,

.accfl, 153

.adp

about, 132–133, 1448

application engine used by, 978–979

cautions viewing project recordsets for action queries, 1523

connecting to new SQL Server database, 1448–1451

creating, 1448–1457

installing SQL Server 2005 Express Edition to build, 1349

projects vs. databases, 1450

using with SQL Server, 6,

views, functions, and stored procedures in, 135–137

changing to test runtime mode, 1329

dBASE

exporting Access data to, A80

importing, 260–264

linking to Access databases, 293, 298–299

delimited text, 282, 283–284, 288

digitally signed, 1336

displaying file name extensions, 102

file system functions, A90–A91

HTML, 1147–1148

importing

Paradox, 265–266

XML, 1254–1260

inserting in attachment fields, 546

linking, 292

.mdb

about, 6,

Access architecture and compatibility with, 979

compatibility with temporary variables, 910

creating compiled version of, 272

embedded macros incompatible with, 907

user-level security support for, 12

.mdf, 1455–1456

.msi, 1340

Paradox

exporting Access data to, A80

importing, 265–266

linking to Access databases, 293, 298–299

sample

location of, 23, 103

modifying table design for, 204

working with project files, 1494–1495

selecting multiple noncontiguous, 1082–1086

text, 282–291

automating import specification of fixed-width, 291

data setup for fixed-width, 284–285

delimited or fixed-width, 282

exporting data to, A81

fixing errors in imported, 290

importing, 285–290

linking to Access databases, 300–301

preparing to import, 282

setting up delimited data, 283–284

XML, 1237–1245

about, 1237

.htm, 1243–1245

.xml, 1237, 1238–1239

.xsd, 1237, 1239–1241, 1255

.xsl, 1241–1243

.xsx, 1243

fill characters embedded in format string, 653

Fill/Back Color button, 587, 588

filling

empty recordset cells with zero values, 446–447

placeholder characters, 655–656

filling out

application design worksheet, A9, A10, A12

HTML forms, 322–325

InfoPath forms, 337–341

Filter button, 385, 402

Filter By Form feature

illustrated, 555

searching for states with, 410–411

using custom Query By Form vs., 1086

Filter Fields drop zone, 474

filter icons, 408

Filter Selection button, 385

Filter window, 408–410

filtering.

See also server filters

append query records by date/time, 503–504

building query on query, 421–424

data in Report view, 747–748

database object lists, 49, 50, 52–53

Filter By Form feature, 410–411

form field data, 551, 554–556

form objects, 114, 115

form properties controlling, 673–674

in-line functions, 1515, 1516

limiting returned records for, 410

linking

forms and subforms with, 1098–1099

to report using, 1099–1101

modules, 130–131

objects with Search Bar, 79–80

one list with another, 1095–1097

PivotTable data, 470

report on opening based on parameters, 1121–1124

saving and reusing filters, 412

selections, 407–408

stored procedures, 1493

table data, 178

Find And Replace dialog box

form searches with, 552–553

searching data with, 405–406

Find button, 385

Find method, 1104

Find Unmatched Query Wizard, 432–434

FindFirst method, 1104

finding. See searching

finishing touches for applications, 1283–1317

controlling starting and running, 1310–1316

setting database startup properties, 1310–1312

starting and stopping application, 1312–1315

customizing Ribbons, 1283–1302

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

dynamically updating elements, 1297–1299

hiding options on Microsoft Office Button, 1300–1301

loading images into custom controls, 1299–1300

loading XML for, 1284–1287

setting focus to tab, 1301–1302

using RibbonX attributes, 1287–1296

XML code listing for, 1292–1294

disabling Layout view, 1304

fine-tuning with Performance Analyzer, 1302–1303

performing final Visual Basic compile, 1316–1317

switchboard forms for, 1305–1310

checking for duplicate keyboard shortcuts, 1305–1306

designing in Switchboard Manager, 1306–1310

illustrated, 1305

First function, 437

fixed-width text files

about, 282

automating import specification of, 291

setting up data for, 284–285

flow control

designing application task flow, A4

forms for, 517–518

macro actions for, A107-A108

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

focus

controlling with macro actions, A113–A114

events detecting changes in, 874–875

setting to contextual tabs, 1301–1302

folders

Access Data Collection Replies, 331

designating as trusted location, 37–38

save new database to selected, 146

saving template files in appropriate, 197–199

Font group

buttons of, 582–583

illustrated, 582, 761

Font Name combo box, 648

fonts

aligning, 583

changing size for Query window, 467

creating shadowed effect behind label, 650

customizing form, 648–650

Font group buttons on Design tab, 582–583

guidelines for using, 650

meanings of font name icons, 649

printer, screen, and TrueType, 649

setting default datasheet options for Web, 1144–1145

footers

form, 518–519

report, 740–741

defining report with or without, 767–768

displaying page numbers in, 832–833

grand total in, 839–840, 843–844

For...Next statement, 1018–1019

For Each...Next statement, 1019–1020

foreign keys

defined, 415

efficiency and, A27

relationships between primary and, 416

updating values in child tables, 185

form controls

locking button on, 573

tab order for, 662–663

form design. See advanced form design

Form Design Tools tab

Controls group buttons on, 568–572, 583–584, 645–646

Font group buttons on, 582–583

form grid

horizontal ruler unavailable for, 633

incorrect field positioning on, 627

removing or setting dot color in, 564–565

snapping controls to, 617–619

units of measure in, 566

form modules

breakpoints ineffective if not open in Visual Basic Editor, 957

module objects vs., 944

form properties

border styles, 675–676

controlling filtering and modifications, 673–674

defining pop-up or modal, 672–673

displaying, 586–587

listed on All tab, 676–680

preventing user’s access to window controls, 675

restricting views for users, 670–671

rules for referencing, 919–920

setting navigation options, 671–672

Form view

Auto Resize unavailable in, 622

buttons in, 120

detecting filters applied to forms, 876

reviewing input form in, 589

sizing subform controls in, 704

trapped keyboard and mouse events properties in, 876–878

viewing and editing data in Form window, 119–120

Form window

in Design view, 114–121

in Form view, 119–120

in Layout view, 118–119, 563

sizing to fit form automatically, 622

Form Wizard

building single continuous form in, 697–698

creating basic form, 593–597

picking form style, 594–596

selecting fields, 593–594

illustrated, 689

justified formats for forms in, 597

missing background images in, 595, 690

modifying basic form, 598–601

opening and using, 688–689

quick create command forms vs., 593

working with template as custom style, 692

Format function, 436

Format property

effect on underlying table’s data, 654

settings for Date/Time data type, 657–658

settings for Number and Currency data types, 652

Yes/No data type settings, 660

formatting

column of controls, 630–631

conditional

using in forms, 716–719

using in reports, 848–851

exporting datasheet to HTML with, 1146–1148

Rich Text fields, 324

formatting characters

Date/Time, 658–660

Number and Currency, 653–654

Text data type, 655–656

forms, 517–734.

See also advanced form design;

HTML forms;

multiple-page forms

about, 100

ActiveX objects in, 533–535

adding records in, 543–548

adding smart tags to controls, 663–665

advanced design, 685–734

ActiveX Calendar control, 726–730

basing on multiple-table query, 686–691

conditional formatting, 716–719

displaying values in option group, 714–716

embedded subforms, 692–713

multiple-page forms, 520, 723–726

PivotChart forms, 730–734

working with tab control, 719–723

architecture

in Access, 980

conceptual, 560–561

attachment controls, 530–533

automated processes in, 561, 562

border formatting for, 583–584

bound and unbound, 566

building simple input

adding new query for property, 576–578

contextual Ribbon tabs for, 567–572

creating and setting dimensions, 563–567

customizing colors and reviewing design, 587–589

displaying form properties, 586–587

dragging fields to form, 578–580

font options for, 582–583

Form Wizard, 593–597

label properties for, 585–586

locking control button, 573

modifying Form Wizard forms, 598–601

moving and sizing controls, 580–581

placing bound controls from field list, 573–574

quick create commands for, 590–593

setting object properties, 574–578

setting text box properties, 584–585

specifying record source, 566–567

changing and deleting data on, 550–551

choosing width and height of, 565

command buttons, 517, 535–536, 537

continuous, 520–521, 592

controls in, 526

customizing, 609–683

adding fields, 610–611

adjusting layout of controls, 616–617

cautions setting control validation rules, 670

colors and special effects for, 645–646

completing form design, 640–642

control anchoring, 623–624

control padding, 638–640

creating blank form, 609

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 form and control defaults, 680–683

setting tab order, 662–663

sizing controls to fit content, 612–616

sizing window to fit form, 622

snapping controls to grid, 617–619

defining

default templates for, 682–683

structure in Design view, 114–117

Detail section of

about, 518–519

grid dots in, 564–565

selecting width and height, 566

displaying photographs in, 9,

Edit and Navigation modes for, 541

editing

data in Form view, 119–120

XML USysRibbons table in, 1269

emphasizing fields with rectangles, 644–645

events

detecting filters, 876

opening and closing, 870–871

trapped keyboard and mouse, 876–878

triggering macros, 560

understanding sequence in editing, 882–885

exporting XML, 1250–1254

filtering, 551, 554–556

font guidelines for, 650

formatting properties for, 670–680

border styles, 675–676

controlling filtering and modifications, 673–674

listing of property sheet All tab, 676–680

navigation options, 671–672

pop-up or modal forms, 672–673

restricting views for users, 670–671

window controls, 675

function of, 114

headers and footers in, 518–519

hyperlinks in, 547–548

justified formats for Form Wizard, 597

keyboard shortcuts for, 541–543

linking to subform with filter, 1098–1099

locating rows in recordset, 1104

macros

for opening secondary, 921–924

synchronizing related, 924–928

making design changes on finished, 589

modal, 524–525, 673

modifying, 212

keyboard options for, 392–393

in Layout view, 118–119

moving around, 539–541

navigation buttons and command icons for, 540

opening in Dialog mode, 1058–1059

PivotTables and PivotCharts in, 536–539

pop-up

formatting properties for, 672–673

synchronizing, 1101–1104

working with, 523–524

prefixes in names of sample, 525

printing, 518, 557–558

procedural vs. object-oriented programming of, 560–562

project, 1547–1565

applying server filters, 1558–1561

changing Default Max Records property, 1555–1557

configuring input parameters, 1561–1565

differences in, 1547–1554

fetching recordsets, 1548

properties for, 1552–1554

setting options for, 1549–1551

setting recordset type, 1554–1555

similarities with database forms, 1547

unsupported in Layout view, 1552

property sheets for, 116–117

providing custom query by, 1086–1093

relationship to other Access objects, 100–101

searching for data in, 551–554

setting datasheet format for in Datasheet view, 1145

simplifying data input, 601–607

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

using combo and list boxes, 601–605

sorting field data in, 551, 554

split, 522, 590–591

subforms, 522–523

switchboard, 1305–1310

checking for duplicate keyboard shortcuts, 1305–1306

designing in Switchboard Manager, 1306–1310

synchronizing with class event, 1101–1104

tab controls, 530

testing code for related records when deleting record, 1074–1075

totals queries not useful in, 435

triggering data task from related, 1105–1109

uses of, 517–518

viewing

data in, 539

fields for, 115–116

Visual Basic form modules, 944

Forms collections, 919, 987–988.

See also forms

Forms object type, 48

Formulas button, 475

forward slash (/)

dividing numeric expressions with, 365, 373

operator precedence for, 367

*forward slash (*/), 1528

forward slash* (/*), 1528

four-arrow cross-hair mouse pointer, 581

four-digit year values, 191–192, 651, 1489

FoxPro.

See also Visual FoxPro

exporting data to, A82-A83

specifying source for tables with IN clause, A46, A47

frm prefix, 525

FROM clause, 464, A41-A44

fsub prefix, 525

FTP (File Transport Protocol), 1138

FULL [OUTER] JOIN statement, A42

full outer joins, 1502

Fully Enclosed option (Access Options dialog box), 612

Function Parameters tab (Properties window), 1518, 1519

function queries, 1525

function reference tables, A85-A91

arithmetic, A85–A86

conversion, A86–A87

date/time, A87

logic, A88

string, A89–A90

user interface/system/file system, A90–A91

Function statement

about, 1006–1007

placing in middle of VB procedures, 947

functional dependence, A21–A23

functions

about, 1005

adding

to Expression Builder, 372

to project, 1495–1496

aggregate

about, A35

available as Group By options in query designer, 1506

defining in totals queries, 435–436, 437

limitations updating, 1508

using in SELECT queries, A35

Avg, 437

Count, 437

CurrentDb, 989

Date, 503, 830–831

date and time, 375–376

DateDiff, 365–368, 373

DatePart, 365

declaring, 1006–1007

defined, 1493

defining as method, 1005

domain, 1057

First, 437

Format, 436

in-line, 1513–1519

available properties for, 1517–1519

building, 1514–1516

constructing to create updatable recordsets, 1514

declaring parameter to filter, 1515, 1516

input parameters to control data returned with, 1561–1565

naming parameters for, 1517

reasons for using, 1513

server filters with forms bound to, 1558

testing, 1516–1517

IsNothing, 1056

IsNull, 930–931

Last, 437

logic, A88

Max and Min, 437

MsgBox, 911, 932, 933

Now, 831

project file, 135

querying Access project file with, 1493

Reconnect, 1323

reference tables, A85–A91

arithmetic, A85–A86

conversion, A86–A87

date/time, A87

logic, A88

string, A89–A90

user interface/system/file system, A90–A91

scalar

defined, 1493

including in parentheses, 1542

table-valued functions vs., 1544

text table-valued vs., 1543

StDev, 437

Sum, 437, 446–447

text scalar, 1541–1543

text table-valued, 1543–1545

Var, 437

WriteHTML, 1154–1156