MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)

tab controls, 719–723

about, 530

controlling tabbing on multiple-page form, 1080–1082

property settings for, 723

setting Page Index property, 721

working with, 720–722

Tab Index property, 663

Tab key, 393

tab order for form controls, 662–663

Table Analyzer Wizard, 234–240

choosing recommendations by, 236

creating new query for modified table, 238–239

eliminating duplicate lookup values, 238

final page of, 239

opening pages of, 235

selecting table to analyze, 235–236

table definitions

limitations on changes to, 205

updating with AutoCorrect smart tag, 233

Table Design button, 155, 1458

table templates, 151–155, 1260–1265

about, 1260–1261

adding field to, 1261–1264

creating and modifying, 153

maximum length of text fields in, 1263

modifying field in, 1264–1265

types of, 152–153

Table Templates command, 153–154

Table window, 107–109

Table Wizard, 153

TableDefs collection, 984

tables.

See also designing, tables;

fields;

relationships, between tables

about, 99

accessing shortcut menu commands for, 106–107

appending

data from other, 502–507

rows with INSERT statement, A73-A75

applying filters to data, 178

avoiding outer joins between two, 185

basing forms on queries of multiple, 686–691

before modifying design of, 204–205

creating

quick create form from, 590–593

quick datasheets for, 592–593

snapshot, A31

with template, 151–155

update queries with multiple, 493–495

Datasheet view for, 109–110

deleting, 209–210

Design view for, 107–108

designating as embedded subform source, 706–707

designing, 142

concept for, A17

from subject worksheets, A16

displaying list of, 105–106

editing in SQL Server, 134–135

eliminating duplicate lookup values, 238

entering data in first, 149–151

exporting

data to SharePoint Services from, 1182–1186

XML, 1246–1250

extra rows in, 111

fields

defining, 156–157

deleting, 225

displaying all in field list, 563–564

editing on one side of join, 419

inserting in, 220–222

moving in, 217–220

omitting embedded spaces in names, 159, 216

selecting all, 352, 353

selecting from multiple, 778

fully qualifying names of fields and, 372

function of, 4

generating with make-table queries, 485, 495–502

importing

source data into SharePoint database, 1187–1188

from XML file, 1255–1256

input masks for data, 170–174

integrity checks in design of, 413

limitations of, 201

linked

indicator for SharePoint Services, 1193

modifying, 302–303

with other Access database table, 295–298

unlinking, 303

using, 1320–1328

looking up values with combo box, 602–604

make-table queries, A75-A76

making backup copies of, 206–207

matching relationships unnecessary for joined, 416

modifying imported, 292

MSys system, 1267

normalizing data for joined, 414

opening in Design view, 155–156, 188

primary keys for, 174–175

printing definitions of, 199–200

project

about, 133–135

adding column definitions, 1475–1477

adding with queries, 1495–1496

creating in Design view, 1457–1459

defining relationships with database diagrams, 1485–1488

illustrating relationships and specifying joins for, 1500–1503

indexes in, 1471–1475

overview, 1447

setting design options, 1488–1490

relations stored in, 5

relationships

between, 181–188

to other Access objects, 100–101

to queries and forms, 560

relinking when going back online, 1232–1233

renaming, 211–212

reversing changes to design, 234

saving, 151

searching for and filtering data in, 405–412

selecting for inner joins, 415

separating names of SQL fields and, 373

setting datasheet format for in Datasheet view, 1145

setting design options for, 191–195

similarity of SharePoint lists and Access, 1169

single-field indexes for, 188–189

storing calculated value in, 1114

subdatasheet property settings for, 178, 179, 180

switching views with buttons or tabs, 109

testing

for related records when deleting record, 1074–1075

validation rules for, 382–384

using Find Unmatched Query Wizard with, 433

USysRibbons, 1266–1269

validation rules for, 175–178

viewing relationships defined for, 187

working with Visual FoxPro, 268

Tables And Related Views category (Navigation Pane), 49–52

adding groups to, 57

filtering objects in, 52–53

groups included in, 57

maximizing search for objects from, 82

switching to, 49–50

unable to reorder display, 61

Unrelated Objects category in, 51–52

Tables object type, 48

table-valued functions, 1493

tabs.

See also contextual tabs;

tab controls

Create, 24, 42–43

Database Tools, 24, 44–45

displaying or hiding document, 85–86

External Data, 24, 43–44

about, 24, 43–44

illustrated, 43, 261

import and export commands on, 261, 1183

SharePoint List button on, 1183, 1184

Home, 24, 42

main Ribbon, 41, 104

Ribbon contextual, 41

Tabular button, 800–801

tabular control layouts

converting, 798–800, 801

defined, 626, 797

removing, 632–635, 800

tag members, 1131–1134

tags

browser rendering of, 1131

common HTML coding, 1129–1131

creating members for, 1131–1134

defined, 1128

members of commonly used, 1133

XML, 1236, 1238, 1240, 1243

tasks

analyzing application’s, A7-A10

automating complex, 1105–1114

calculating stored value, 1114

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

triggering data task from related form, 1105–1109

charting flow of, A4

completing design worksheet for, A9, A10, A12

data as output of, All

identifying application, A4

organizing, A11-A13

relationships among database, A13

selecting data to perform, All

Tasks template, 152

TCP (Transmission Control Protocol), 1137

TCP/IP (Transmission Control Protocol/Internet Protocol), 1137

templates

creating

custom default, 195–199

database with, 142–147

for reports outputting to HTML, 1151–1152

SharePoint list from Access using existing, 1199–1203

defining for form, 682–683

downloading online, 145–147

exporting report with, 1152–1154

HTML

customizing data output with, 1147

designing and using, 1148–1151

modifying XML view files of, 153

preserving sunken text box control effect, 610

renaming default as Normal, 682

saving in Windows Vista, 197

selecting database, 143–145

table, 151–155, 1260–1265

about, 1260–1261

adding field to, 1261–1264

creating and modifying, 153

modifying field in, 1264–1265

types of, 152–153

Table Wizard replaced with table, 153

tokens for HTML, 1149

used for IssuesSample.accdb database, 54

temporary variables

about, 907

compatibility of .mdb databases with, 910

creating, 908–909

macro actions using, A117

passing information between linked forms with, 934–935

referencing in macros, 991

removing, 991

removing and deleting, 909–910

sample macro using, 933–934

syntax for, 909

TempVars collection, 991, A117

testing

application design, A6

code for related records when deleting record, 1074–1075

conditions using macro actions, A107-A108

custom Ribbon, 1270, 1272–1273

data updates with select queries, 486

database in runtime mode, 1329

in-line functions, 1516–1517

macros, 894–895

Null values with macro, 927

rows

affected by delete queries, 507–510

existence in subquery, A38, A39

table validation rules, 382–384

text stored procedure variables, 1531, 1532

text

changing maximum length of, 230–231

defining Lookup properties for, 353

embedding with double quotation marks, 653

encoding options for exported, 1248, 1249

formatting properties for, 655–656

report grouping options for, 818

text box controls

activating Date Picker for, 727

placing on form design grid, 578–579

resizing in Layout view, 797–798

sunken effect for, 596–597

text boxes

changing to combo box manually, 602

conditional formatting of, 716

duplicate names for report fields and, 836

formatting properties for data display, 651–661

overlaying to display filtered data, 1096

setting properties for input form, 584–585

sizing controls for reports, 772–773

width unadjusted with Size To Fit command, 614

Text data type

about, 158–159

converting, 226

formatting characters for, 655–656

text editor, 1524–1545

backing up text queries, 1526

building text scalar function, 1541–1543

building text stored procedure, 1526–1541

about, 1526–1527

adding control-of-flow statements, 1531–1535

grouping multiple statements with transactions, 1535–1541

re-creating stored procedure from query designer, 1529–1530

starting new procedure, 1527–1529

building text table-valued function, 1543–1545

text expressions, 362–364

text fields

maximum length in table templates, 1263

SharePoint’s conversion of line characters in, 1218

showing Null value in, 657

text files, 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

text scalar functions, 1541–1543

text stored procedures, 1526–1541

about, 1526–1527

adding control-of-flow statements, 1531–1535

comment blocks in, 1528

declaring variables, 1531–1532

grouping multiple statements with transactions, 1535–1541

starting new, 1527–1529

testing variables in, 1531, 1532

text strings, concatenating, 841–842

text table-valued function, 1543–1545

themes applied on forms, 559, 596, 692

thickness, control line, 583–584

thousands separator character (comma), 653

3-D object color, 565

time.

See also Date/Time data type

cautions building criteria on date and, 357

checking for overlapping, 1078–1080

filtering records for Append query by, 503–504

functions for date and, 375–376

prevented entry of values with input mask, 361

using in selection criteria, 356–357

timer detection, 882

titles

adding Report Wizard report, 782–783

entering in Group, Sort, And Total pane, 766–767

PivotChart, 480–481

To Grid button, 618

toggle buttons

about, 526–527

check boxes and option buttons vs., 606

setting field’s status with, 605–607

Toggle Filter button, 402

tokens

defined, 1149

HTML template, 1149

toolbars. See Quick Access Toolbar

TOP clause, 1511

TOP keyword, A62

TOP n PERCENT clause, 1510, 1511, A65

Top Values property, 453–454

totals.

See also totals queries

calculating on expressions, 838

counting total events with controls, 804–807

creating report’s grand, 839–840

filtering

groups of totals, 441–442

records before grouping, 440–441

options in Group, Sort, And Total pane for, 766

partitioning sales, 447–449

running, 845–848

Totals button, 435

Totals Fields drop zone, 474

totals queries, 435–449

defining aggregate functions in, 435–436, 437

filtering

before grouping records with, 440–441

groups of totals after calculating, 441–442

partitioning data in, 447

sorting sequence of, 439

unable to change data in, 468

uses for, 435

tracking AutoCorrect information for names, 208

transactions, defined, 12

Transact-SQL

about, 1491

creating text queries with, 1525

finding list of reserved words for, 1460

grouping multiple statements with transactions, 1535–1541

TRANSFORM clauses, A34

TRANSFORM statement, A67-A68

Transmission Control Protocol (TCP), 1137

Transmission Control Protocol/Internet Protocol (TCP/IP), 1137, 1138

trapping

errors

event properties for, 882

in macros, 910–915

in Visual Basic, 1028–1030

keyboard and mouse events, 876–878

troubleshooting

Access won’t use ODBC for all file types, 268

action queries, 512–514

can’t see column properties or create lookups in SQL Server 7.0, 1470

can’t set defined default value in date/time field, 1063

cautions setting VB breakpoints, 957

control validation rules, 670

ensuring correct table used in query designer, 415

errors authenticating SQL Server 2005 Express Edition with Windows Vista, 258

errors creating SQL Server database, 1452, 1457

extra table rows in Datasheet view, 111

incomplete macro actions, 892

link to User Information List deleted, 1194

Maximize/Minimize buttons not in windows, 109

missing records in imported SharePoint list, 1191

missing Web page graphic or background, 1150

project forms and reports unsupported in Layout view, 1552

unable to change published database, 1224

undoing deleted table relationships, 211

TrueType (TT) fonts, 649

truncated data warning message, 230

Trust Center, 34–35

enabling

content by defining trusted locations, 39–40

untrusted database, 35–36

illustrated, 37, 96

opening, 36, 96

selecting privacy options, 38–39

trusted actions, A101

trusted connection for SQL Server databases, 1450–1451

trusted locations

defining, 39–40

designating, 37–38

downloading SharePoint databases to, 1225

illustrated, 40

placing sample files in, 23, 103

trusted macros, 916

trusted publishers, 37

Trustworthy Computing, 34

T-SQL. See Transact-SQL

TT (TrueType) fonts, 649

turning on/off. See disabling; enabling

twips, 1120

Two Pages button, 124, 125

Type statement, 977–978