R

RaiseEvent statement, 1022–1023

randomly loading data, 1030–1044

building procedure to, 1030–1032

code listing, 1032–1040

explanation of code listing, 1041–1044

rbnCSD ribbon code listing, 1292–1296

RDBMS (relational database management system)

Access as, 6–13

data manipulation in, 9–11

data sharing and control in, 12–13

database capabilities of, 5–6

defined, 4,

sequence of fields in databases, 217

read permissions, 273

read-only database, 1224

read-only forms, 1554–1555

Ready To Install page (Microsoft SQL Server Installation Wizard), 1355–1356

recipients

customizing data collection messages for, 318–319, 334–335

filling out HTML forms, 322–325

inserting data in InfoPath forms, 337–341

instructing to click Reply, 319

selecting e-mail, 316–318, 320–321

Reconnect function, 1323

record indicators in Datasheet view, 390

Record Lock property, 462

record locking settings, 294, 295

Record Number box, 385

record sources

binding to table, 609–610

building multiple-table query to serve as, 686–688

choosing query used as report’s, 1572

creating query on form property’s, 576–578

defined, 566

in-line functions as report, 1513

query views as, 1507

selecting report’s, 791–792

specifying, 566–567

subquery providing search criteria from underlying, 1093

using field in calculations from specified report, 834

records

about, 99

action query errors with locked, 512

adding

in forms, 543–548

totals to report, 804–807

design concept of, A17

form properties controlling addition of, 673–674

keyboard shortcuts for, 542–543

linking between forms and subforms, 548

locking those retrieved in queries, 462

project rows vs., 1458

querying across date spans, 428

receiving new data via e-mail, 309, 329

size limitations for, 201

verifying prerequisite before saving, 1075–1077

Records Selectors property, 671–672

Recordset object variable, 994–997

Recordset property, 1104

Recordset Type property, 1554–1555

RecordSetObject.Open parameter settings, 1002–1003

recordsets

adding records at end of edited, 544–545

ADO, 1001–1005

deleting rows in, 1004

difficulties using, 982

inserting rows in, 1005

moving to specific records, 1003

opening, 1001

RecordSetObject.Open parameter settings, 1002–1003

searching and finding rows in, 1003–1004

changing field names for, 377–378

choosing type of project query for updatable, 1514

conserving server resources when downloading, 1551

DAO, 994–997

defined, 347, 560, 686

dynaset and table, 996

fetching partial sets for project report, 1568

filling empty cells with zero values, 446–447

filtering before grouping with totals queries, 440–441

finding unmatched records, 426–430, 432–434

inserting row in, 997

limiting returned records for queries, 410

locating rows in form, 1104

methods of fetching project form, 1548

navigation buttons in Datasheet view, 386

organizing with query designer views, 1507

relative record number of, 384

removing with delete queries, 507–511

returning unique records and values for queries, 454–458

sorting rows in, 378–380, 421

specifying fields for, 351–353

using server filters for, 1558

using updatable, 1554–1555

viewing joined data from two tables, 417–419

recovering deleted macros, 907

Rectangle tool, 642

rectangles, 644–645

RecycleBin, 1179–1182, 1197

ReDim statement, 974–975

referencing

collections, 987–989, 991

controls for forms and reports, 920

methods, in variables created by macro, 991

objects

form and report objects, 919

in variables created by macro, 991

within collections, 987–988

properties, 987–989

form and report, 919–920

in variables created by macro, 991

subforms and subreports, 920–921

temporary variables, 991

referential integrity

defining relationships for project tables, 1482–1488

enforcing in relationships, 184, 185

ensuring business rules in queries for, 417–419

maintaining application’s data integrity, 413

not enforced by SharePoint Services, 1195

presetting field values to ensure data integrity, 935–938

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

refining application design, A6

Refresh Pivot button, 476

Registration Information page (Microsoft SQL Server Installation Wizard), 1352–1353

relation, 5,

relational database management system. See RDBMS (relational database management system)

relational databases, 4–5

relationships

among database tasks, A13

efficiency in database, A27-A28

creating linking tables, A28

foreign keys, A27

one-to-many and one-to-one, A28

many-to-many

about, 5,

defining linking table to create, 1077–1078

embedding subforms to reflect, 693

order-entry examples demonstrating, A8

one-to-many

database efficiency and, A28

defining, 185–186

linking table fields for, 184

between tables, 181–188

avoiding outer joins between two tables, 185

creating relationship on multiple fields, 186–187

defining fields linking tables, 181–183

defining for project tables, 1482–1488

deleting, 210

editing from Design contextual tab, 184

setting up first relationship, 183–184

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

undoing deleted, 211

viewing, 187

Relationships tab (Properties dialog box), 1483–1485

Relationships window

database diagrams vs., 1485

embedding subforms to reflect many-to-many relationships, 693

illustrated, 187

opening tables in Design view in, 188

printing reports from, 188

relative record number, 384

Relink Lists To New Site dialog box, 1197

relinking tables when going back online, 1232–1233

Remove button, 633

removing

automatic sorting of Navigation Pane objects, 78

control layouts, 800

database passwords, 1335

form control layouts, 598–599, 632–635, 640–642

form grid dots, 564–565

items from Quick Access Toolbar, 28–29, 33, 34

joins for project query tables, 1501

temporary variables, 909–910, 991

trusted publishers, 37

renaming

field names in queries, 419

navigation groups, 59, 60

object shortcuts, 70–72

objects with macro action, A116

tables, 211–212

template fields, 154

replacing data in Datasheet view, 394

replies. See processing e-mail replies

Report command, 775–777

Report Design button, 759–760

Report Design Tools collection, 759, 761

Report Design Tools contextual tab, 121–122, 761

report footers. See footers

report modules

breakpoints ineffective if not open in Visual Basic Editor, 957

module objects vs., 944

report properties

about, 822

list of available, 823–830

property sheet for, 819, 822–823

rules for referencing, 919–920

report sections. See section properties

Report view, 745–750

about, 127–128, 745–746

defining Default View property to open reports in, 746

filtering data in, 747–748

properties

for detecting filters applied to reports, 876

for trapped keyboard and mouse events, 876–878

responding to control events in, 749–750

Report window

designing report in, 759–761

Layout view for, 125–127

Print Preview for, 124–125

Report view for, 127–128

Report Wizard, 777–790

about, 777

adding

date and time to reports in, 830–831

grouping levels and intervals, 779–780

report title, 782–783

choosing fields from multiple tables or queries, 778

creating structure for complex reports in, 813–815

illustrated, 778, 779

layout style and page orientation, 781–782

modifying reports from, 784–790

accenting detail with background color, 788–789

changing control properties, 787, 788

resizing columns and fields, 784–785, 786

resizing and repositioning controls, 785–787

previewing report in, 783–784

selecting fields, 778

specifying sorting criteria, 780, 781

summary options for, 780–781

verifying grouping criteria, 779

ReportML style specification, 1250, 1252

reports

about, 120–121

Access architecture and, 980

advanced design, 811–863

adding PivotChart to report, 860–863

adding print date and page numbers, 830–833

adding values across group, 837–838

calculating percentages, 840, 843–844

calculating totals on expressions, 838

concatenating text strings, 841–842

conditional formatting, 848–851

creating and embedding subreport, 741–744, 851–859

creating grand total, 839–840

defining grouping and sorting criteria, 816–818

hiding redundant values, 840–841, 842

numbering report lines, 845–848

performing calculations on detail line, 833–837

queries for complex reports, 812–813

report properties, 822–830

section properties, 819–822

structuring basics in Report Wizard, 813–815

automating, 1114–1124

drawing border on report, 1118–1121

dynamically filtering on opening, 1121–1124

using up partial page of mailing labels, 1115–1118

blank, 760

building from scratch

adding column labels, 770–771

adding new grouping level on grid, 764

changing grouping and sorting priorities, 768–769

choosing to use or omit headers, 767

data types unavailable in Group, Sort, And Total pane, 763

defining groupings, 761, 762–763

designing, 759–760

entering titles, 766–767

formatting title for, 769

group interval options, 765–766

grouping specifications and data types, 765

line control in, 771, 773

opening Group, Sort, And Total pane for, 761–762

page numbers for, 773–774

printing options, 768

selecting totals options in, 766

sizing text box controls for, 772–773

sort order and options in, 764–765

using or omitting footers, 767–768

building query for, 757–759

canceling moves in large, 740

changing definition in Design view, 121–124

creating snapshot data for, A31

customizing width and height, 753–754

defined, 100

Detail section of

embedding PivotChart in, 861–863

expression values used with conditional formatting, 849

illustrated, 740

numbering lines in, 846–848

performing calculations on, 834–837

events

detecting applied filters, 876

opening and closing, 870–871

trapped keyboard and mouse, 876–878

exporting XML, 1250–1254

filtering to link to, 1099–1101

generating HTML Web page from, 1151–1154

creating template for report, 1151–1152

headers, footers, and groups in, 739–741

interactive Report view for, 127–128, 745–750

Layout view, 790–809

adding totals to records, 804–807

applying AutoFormat, 807–809

grouping and sorting in, 792–796

modifying Report Wizard reports in, 784–790

selecting record source, 791–792

starting with blank report, 790–792

usefulness of, 797

working with control layouts, 797–804

look of printed sample, 736

modifying, 212

objects embedded in, 744–745

opening

from Navigation Pane, 736, 737

in Report view or Print Preview, 746

printing, 750–755

adjusting Page Setup options, 751–755

previewing before, 124–125, 738, 750–751

project, 1567–1577

differences between database and, 1567–1570

input parameters, 1572–1577

server filters in, 1570–1572

setting properties for, 1569–1570

similarities between database and, 1567

unsupported in Layout view, 1552, 1569

property sheets for, 123, 819

quick, 775–777

reducing redundancy in, 833

relationship to other Access objects, 100–101

Report Wizard, 777–790

adding date and time, 830–831

adding grouping levels and intervals, 779–780

adding report title, 782–783

choosing fields from multiple tables or queries, 778

illustrated, 778, 779

layout style and page orientation, 781–782

modifying reports from, 784–790

previewing report in, 783–784

selecting fields, 778

sorting criteria, 780, 781

summary options for, 780–781

verifying grouping criteria, 779

subreports, 741–744

using, 735–736

viewing

in Layout view, 125–127

in Print Preview, 736, 737–739

Visual Basic report modules, 944

Reports collections.

See also reports

referencing objects in, 987–988

Reports object type, 48

republishing shared databases after changing, 1207, 1219–1222

Require Variable Declaration check box (Options dialog box), 963

Required field property messages, 168

resending data collection messages, 343–345

resizing

controls

in Layout view, 631–632

using property sheet, 633

Report Wizard columns and fields in Layout view, 784–785, 786

Report Wizard controls and repositioning, 785–787

text box controls in Layout view, 797–798

Resolve Conflicts dialog box, 1231–1232

resolving changes in shared data, 1231

Resources category (Access Options dialog box), 96–97

restoring

defaults for Quick Access Toolbar, 33

Hourglass after running macros, 897

Recycle Bin items, 1179–1182

Ribbon, 45

rows replaced in error, 224

Retry All My Changes button, 1232

RETURN statements, 1529, 1531

return values for MsgBox function, 933

reverse order sorting, 403

reversing changes to table design, 234

reviewing application design, A6

Ribbon, 41–45.

See also Design contextual tab;

External Data tab

adding command to Quick Access Toolbar from, 34

collapsing, 45

contextual tabs

about, 41

adding built-in groups to, 1276–1279

creating custom, 1274–1275

found on Form Design Tools, 567

Print Preview, 739

Report Design Tools, 121–122, 761

setting focus to, 1301–1302

Controls group

border formatting with, 583–584

buttons on, 568–572, 645–646

illustrated, 568–572, 761

Option Button command, 714

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

customizing, 1266–1279, 1283–1302

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

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

displaying errors, 1274

dynamically updating elements, 1297–1299

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

tabs, 1274–1275

testing, 1270, 1272–1273

using RibbonX attributes, 1287–1296

XML code listing for, 1292–1296

getting accustomed to, 21

illustrated, 24,41

main tabs, 41

Create, 24,42–43

Database Tools, 24, 44–45

displaying ScreenTips for, 104

External Data, 24, 43–44

Home, 24, 42

not used in Visual Basic Editor, 945

PivotTable tools on, 475–476

quick create commands on, 590

sort buttons in Datasheet view, 402

unable to select from when modal form displayed, 673

used with sample reports, 811

Rich Text fields, 324

Rich Text Format support for SharePoint text fields, 1170–1171

right arrow button (>), 593

Right Arrow key, 392

right joins, 1502

RIGHT [OUTER] JOIN statement, A42

ROLLBACK TRANSACTION statements, 1535

Row Fields drop zone, 474

row selector, 385

row source queries, 1054–1055

@@ROWCOUNT system variable, 1531, 1532, 1534, 1535

rows

about, 99

adding to project query views, 1510

appending with INSERT statement, A73-A75

copying and pasting multiple, 224

counting with stored procedures, 1524

Datasheet view of, 385

deleting

datasheet, 396

and updating ADO recordset, 1004

entering data in table, 150

extra table, 111

filtering in Filter window, 408–410

finding differences in, 369–370

inserting, 220–222

in ADO recordset, 1005

on InfoPath forms, 338

new index, 190

limiting returned records for queries, 410, 462

locating rows in form recordset, 1104

moving with mouse, 218

multiple rows per e-mail replies, 314–315

project, 1458

queries for deleting, 485, 507–511

searching for in ADO recordset, 1003–1004

selecting

for copying or cutting, 395

to view property settings, 107–108

sorting

changing sequence by cutting or, 396

in recordsets, 378–380

updating with query, 486–495

ruler, 633

rules.

See also validation rules

breaking design, A28-A31

capturing point-in-time data, A29-A31

creating report snapshot data, A31

improving performance of critical tasks, A29

business

ensuring in queries for data integrity, 417–419

verifying prerequisite data to satisfy, 1075–1077

SQL Server Rules for Identifiers, 1460

table design, A27

Run All From My Computer option (Microsoft Office dialog box), 1344

RunCommand method, 1027

RunMacro command, 939

Running Sum property, 845–848

runtime mode execution, 1328–1329