Package Solution wizard, 1340
packaging and signing database, 1336–1339
Page Header section, 845
Page Index property, 721
page numbers
inserting in controls, 806
report, 773–774, 830–833
Page Numbers dialog box, 832–833
Page Order dialog box, 721
page orientation
printing reports in landscape or portrait, 751, 752, 755
selecting Report Wizard report, 781–782
Page property, 832–833
Page Setup dialogbox, 751–755
Columns tab, 558, 753–754
Page tab, 558, 751–753
Print Options tab, 752
saving specific printer settings, 753
Page tab (Page Setup dialog box), 558, 751–753
pages.
See also Web pages
Switchboard Manager, 1307
Paint, 535
panes.
See also Navigation Pane
Group, Sort, And Total, 761–769, 818
Object Dependencies, 209
query designer, 1497–1498
about, 1497–1498
diagram, 1497, 1499, 1500–1503
grid, 1497, 1500, 1503–1507
SQL, 1498
Paradox
exporting Access data to, A80
importing files from, 265–266
linking files to Access databases, 293, 298–299
specifying source for tables with IN clause, A46, A47
parameter queries
parameter resolved from value chosen from another form, 1565, 1576
PARAMETERS declarations in, A52–A54
prompting users for parameters for, 1572–1573
setting parameters with Input Parameters property, 1574–1575
parameter settings
OpenRecordset method, 994–995
RecordSetObject.Open, 1002–1003
parameters
adding to application shortcut target file name, 1332
basing stored procedure filters on, 1493
declaring as filter for in-line function query, 1515, 1516
defined, 1005
descriptive names for functions and stored procedures, 1517
filtering report on opening using date, 1121–1124
function and subroutine, 1005
prompting users to enter, 1572–1573
setting input, 1561–1565
setting for parameter query with Input Parameters property, 1574–1575
SQL and Access equivalent data types for, A53
using report input, 1572–1577
PARAMETERS declaration, A52-A54
parent tables, 185
parentheses ( )
adding to expressions, 370
as characters in format string, 653, 655
expression evaluation within, 367
scalar functions in, 1542
Partially Enclosed option (Access Options dialog box), 612
partitioning data in crosstab queries, 447–449
pass-through queries, 463
passwords
encrypting database with, 1334–1336
entering for database with linked SharePoint tables, 1198
logging in on SharePoint Services Web site, 1213–1214
Paste Table As dialog box, 207
pasting
copied data in Datasheet view, 394–396
copied fields, 223–224
copied tables, 207
message box confirming, 396
pencil icon, 390, 1229
percentage sign (%), 654
percentages, calculating grand total using, 840, 843–844
performance
effect of linked data on, 293–295
form fields defined as lookup values, 324
improving for critical tasks, A29
indexes and database, 1472
limiting rows fetched for project forms, 1548–1549
linked table, 1323
loading time of form and report modules, 944
PivotTable and PivotChart, 470
reconstructing queries for, A66
reducing network traffic with server filters, 1572
speeding up code with Me objects, 990
storing calculated value in table, 1114
subdatasheet properties with large tables, 179
Performance Analyzer, 1302–1303
period (.)
indicating decimal in numbers and currency, 653
rules for using with names, 990
separating table and field names with, 372, 373
permissions
Create Database, 1452
read, 273
SharePoint Services lists, 1197
photographs
deleting and updating image path for, 1070–1071
displaying in forms, 9,
embedding in reports, 744–745
working with linked, 1069
PivotChart forms, 730–734
building, 731–732
designing, 536–539, 730
embedding linked PivotChart, 730, 733–734
PivotChart view, 479, 873
PivotCharts.
See also PivotChart forms
adding display labels, 480–483
building query for report, 860–861
correcting data field caption for, 482
defined, 470
designing, 478–483
embedding in report, 861–863
event properties detecting changes to, 870, 878–880
illustrated, 480, 483
including axis on, 481
performance of, 470
PivotTable design window
drop zones, 475, 476
illustrated, 474
Ribbon tools for, 475–476
PivotTable view, 873
PivotTables
about, 468–469
building query for, 470–473
changing field captions for, 477
crosstab queries vs., 444, 469–470
designing, 473–478
event properties detecting changes to, 870, 878–880
field list for, 474
filtering data in, 470
forms using, 536–539, 730
performance of, 470
setting up queries for, 469–473
placeholder characters
@, 655
0 and # used as, 653
choosing input mask, 172
rules for filling, 655–656
underscore (_) as default, 171
plus sign (+)
adding expressions with, 365
as character in format string, 653, 655
concatenating project file expressions with, A40
expanding subdatasheets, 387–390, 461
operator precedence for, 367
polishing applications. See finishing touches for applications
Pop Up property, 672
Popular category (Access Options dialog box), 87–88
pop-up forms
defining properties for, 672–673
synchronizing with another form, 1101–1104
working with, 523–524
portrait orientation for reports, 751, 752
pound sign (#) wildcard, 169, A49
precedent tasks, All
predicates
BETWEEN, A35
comparison, A37–A38
EXISTS, A38–A39
IN, A47–A48
LIKE, A48–A49
NULL, A50
quantified, A54-A55
prefixes on form names, 525
presentation (layout) document (.xsl)
about, 1237, 1241–1243
Report ML version of, 1250, 1252
presentation layout extension documents (.xsx), 1237, 1243
Presentation tab (Export XML dialog box), 1248, 1249
preventing
Ctrl+F4 with AutoKeys macro, 1315–1316
data modification directly on datasheet, 413
design changes in applications, 1311
endless loop with Change event, 873
forms from opening in Design or Layout view, 671
Layout view, 1304
Navigation Pane or built-in Ribbon access in runtime mode, 1328–1329
special keys use, 1312
use of Module and Class Module buttons, 1330
user’s access to window controls, 675
previewing
forms before printing, 557–558
reports
before printing, 124–125
for sample application, 738
table definitions, 199–200
primary key index, 189, 190
primary keys
artificial, 1071–1072
AutoNumber fields as, 549
changing, 248–250
defined, 99
defining
for database tables, 174–175, 181, 182
for projects, 1470–1471
finding in SharePoint list, 1219
foreign keys and, 416
functional dependence of fields on, A21–A23
including in data collection, 310
providing tables with unique identifiers, A20–A21
query update limitations for, 469
setting
for imported spreadsheet data, 278–279
in Table Analyzer Wizard, 237
SharePoint Services and, 1218
troubleshooting duplicate, 512
value changed in parent table, 185
Print Options tab (Page Setup dialog box), 752
Print Preview contextual tab, 739
Print Preview feature, 737–739
Print Preview Zoom button, 124
printer fonts, 649
printers
adjusting default margins automatically, 736
saving specific report settings for, 753
printing
event properties for, 881
forms, 518, 557–558
macro actions for data, A105–A106
mailing labels
company and contact, 754
with data from multiple tables, 463–468
reports, 750–755
in landscape or portrait orientation, 751, 752, 755
previewing first, 124–125, 737–739, 750–751
from Relationships window, 188
table definitions, 199–200
privacy
Microsoft privacy statement, 19
options for, 38–39
Privacy Options check box (Trust Center dialog box), 38–39
Privacy Options dialog box, 19–20
Private statement, 972–973
procedural statements
Const, 965
Dim, 966–969
DoCmd and RunCommand, 1026–1028
flow-control
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
While…Wend, 1025
With…End, 1025–1026
functions and subroutines
about, 1005
Function, 1006–1007
Sub, 1007–1009
Option Explicit, 963
Property Get, 1010–1012
Property Let, 1012–1014
Property Set procedure, 1014–1016
Set, 991–993
procedural vs. object-oriented programming, 560–562
Procedure list (Visual Basic Editor), 946–947
procedures
class module
Property Get, 1010–1012
Property Let, 1012–1014
Property Set, 1014–1016
compiling errors with procedures within, 947
examining call sequence for, 959–960
stored, 1519–1524
about SQL, 1493, 1519
choosing combo box values resolved with, 1564, 1575–1576
defined, 136
defining properties for columns, 1523–1524
displaying in text editors, 1525
filtering, 1493
inserting parameters in append values queries, 1520–1522
naming parameters for, 1517
server filters unavailable for, 1558
text stored, 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
user-defined stored, 1533
process-driven design, A7
processing e-mail replies
accepting multiple replies, 314
allowing multiple rows per reply, 314–315
automatically in Outlook, 312–313, 325–326
choosing method for, 331–332
discarding replies, 314
manually, 321–322, 341–343
setting date to stop, 315
processor requirements, 1341
product key for Microsoft Office system, 1342
Products form
justified format for, 597
modifying, 598–601
Project Explorer window (VBE)
about, 946
illustrated, 945
project files
concatenating expressions with plus sign, A40
wildcard characters for string comparisons in, A49
project forms, 1547–1565
applying server filters, 1558–1561
changing Default Max Records property, 1555–1557
differences in, 1547–1554
fetching recordsets, 1548
properties for, 1552–1554
setting
input parameters, 1561–1565
options for, 1549–1551
recordset type, 1554–1555
similarities with database forms, 1547
unsupported in Layout view, 1552
project queries, 1491–1545
building in query designer, 1495–1524
adding tables, views, and functions, 1495–1496
grid pane of, 1497, 1500, 1503–1507
Group By options in, 1506
in-line functions, 1513–1519
panes in, 1497–1498
specifying query properties, 1507
SQL pane, 1498
stored procedures, 1519–1524
types of views created in, 1495
using views, 1507–1513
working with columns for, 1499–1450
working in diagram pane, 1497, 1499, 1500–1503
building with text editor, 1524–1545
selecting option for, 1524–1525
text scalar functions, 1541–1543
text stored procedures, 1526–1541
text table-valued functions, 1543–1545
saving in SQL Server before running, 1510
viewing, 10–11, 1492–1494
project reports, 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
projects.
See also .adp files
about, 1447
compiling
after completing each code section, 950
errors with procedures within procedure, 947
creating new project file, 1448–1457
building new SQL Server database, 1448–1451
connecting to existing SQL Server database, 1452–1457
errors creating new database, 1452
naming SQL Server databases, 1451
Navigation Pane for new project, 1457
setting up data link properties, 1454–1457
creating table in Design view, 1457–1459
wizards for .accdb applications unavailable, 1457
databases vs., 1450
defining check constraints, 1477–1481
adding, 1478–1480
constructing multiple constraint expressions with, 1481
defined, 1477–1478
expressions to validate dates, 1481
types of constraint expressions, 1481–1482
defining columns, 1459–1470
adding column definitions, 1465–1467
SQL Server column data types, 1461–1464
sql_variant data type, 1465
understanding column properties, 1467–1470
user-defined data types, 1465
defining primary key, 1470–1471
defining relationships for tables, 1482–1488
in Design view, 1482–1485
using database diagrams, 1485–1488
designing forms for, 1547–1565
forms
applying server filters, 1558–1561
changing Default Max Records property, 1555–1557
differences in, 1547–1554
fetching recordsets, 1548
properties for, 1552–1554
setting input parameters, 1561–1565
setting options for, 1549–1551
setting recordset type, 1554–1555
similarities with database forms, 1547
unsupported in Layout view, 1552
improved reliability of Visual Basic, 950
including reference to ADODB or ADOX models in, 987
queries in, 1491–1545
building in query designer, 1495–1524
building with text editor, 1524–1545
types of objects used as, 1493
viewing, 10–11, 1492–1494
working with sample project files, 1494–1495
reports, 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
SQL Server version required for, 1448
tables
adding sample, 1475–1477
exploring, 133–135
indexes for, 1471–1475
setting design options for, 1488–1490
upsizing applications to, 533
Proofing category (Access Options dialog box), 92
properties.
See also Format property;
query properties
Allow Additions, 674, 1554–1555
Allow Deletions, 674, 1554–1555
Allow Edits, 674, 1554–1555
Allow Layout View, 589
Auto Expand, 605
Auto Resize, 622
Can Grow, 1119
Caption, 216, 377, 586, 587
changing field, 232–233
control
Enabled and Locked, 584–585, 662
listed on All tab, 666–669
customizing query, 452–463
Cycle, 726, 1080
Default Max Records, 1550, 1555–1557
DefaultView, 746
defining default template form, 682–683
displaying
project query view, 1510–1513
subdatasheet, 388
Dockable, 946
effect of Format property on data in underlying table, 654
event
changing data, 871–873
detecting focus changes, 874–875
detecting form and report filtering, 876
detecting PivotTable and PivotChart changes, 870, 878–880
detecting timer expiration, 882
names of, 870
opening and closing forms and reports, 870–871
printing, 881
trapping errors, 882
formatting control, 651–661
Date/Time formats, 657–660
numbers and currency, 652–654
text, 655–656
Yes/No fields, 660
forms, 670–680
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
project, 1552–1554
restricting views for users, 670–671
setting navigation options, 671–672
HasModule, 944
Hide Duplicates, 840–841
Input Mask, 1063
Input Parameters, 1574–1575
Link Child Fields, 705, 706
Link Master Fields, 705, 706
linking fields with subform control, 705
Lookup, 240–245
macros referencing
form and report, 919–920
form and report control, 920
making grid dots invisible with Grid X and Grid Y, 587
Max Records, 462, 1550, 1555
Min Max Button, 675
modifying Report Wizard control, 787, 788
Multi Select, 1084–1085, 1086
multiple-page forms with Cycle, 726
ODBC Timeout, 462
Page, 832–833
project form, 1552–1554
project report, 1569–1570
Record Lock, 462
Records Selectors, 671
Recordset, 1104
RecordsetType, 1554–1555
report
about, 822
list of available, 823–830
project, 1569–1570
property sheet for, 819, 822–823
Running Sum, 845–848
section
list of available, 820–822
viewing, 819
Server Filter, 1570, 1571
Server Filter By Form, 1559
setting
field, 161–165, 353–355
for form objects, 574–578
input parameter, 1562–1563
label, 585–586
for Multi-Value Lookup Field, 247
SharePoint column, 1176, 1177
tab’s Page Index, 721
text box, 584–585
Show Date Picker, 1063
sorting number values of Lookup, 381
Source Connect Str, 462
Source Database, 462
specifying query, 1507
SQL Server table column, 1467–1470
Subdatasheet, 178
tab control, 723
Tab Index, 663
Top Values, 453–454
Unique Records, 454–458
Unique Values, 454–458
Validation Rule and Validation Text, 670
viewing those created by Combo Box Wizard, 604–605
Properties dialog box
adding detail to PivotCharts, 480–482
Check Constraints tab, 1478
Hidden check box in, 70, 75
property settings for application shortcut, 1331–1332, 1334
Properties window
Function Parameters tab, 1518, 1519
illustrated, 1511
In-Line Function tab, 1518
options on View tab for, 1512–1513
Stored Procedures tab, 1523–1524
Properties window (Visual Basic Editor), 945, 946
Property Get procedure, 1010–1012
Property Let procedure, 1012–1014
Property Set procedure, 1014–1016
property sheet
All tab
listing of control properties, 666–669
listing of form properties, 676–680
assigning alias to query fields with, 496, 497
illustrated, 575
listed embedded macros in events, 902
opening
and closing in Design view, 576
and customizing, 116–117
replacing field list with, 565
report, 819, 822–823
setting for, 123
resizing controls using, 633
section, 819
setting form’s object properties from, 574–578
tabs on, 575–576
Property Sheet button, 176, 476
Property statement, 947
protocols
TCP/IP, 1137
types of network, 1138
Public statement, 973–974
public variables and constants, 963–964
Publish To Web Server dialog box, 1210, 1221, 1222
publishing data on Web.
See also HTML (Hypertext Markup Language)
creating static HTML documents, 1140–1158
customizing appearance, 1143–1148
designing and using HTML templates, 1148–1151
exporting database as HTML file, 1140–1143
generating HTML page from Access report, 1151–1154
development tools required for, 1127–1128
dynamic Web pages, 1158–1161
delivering dynamic query results, 1158–1160
processing live data with HTML forms, 1160
using ASP for fetching data on, 1158
using Visual Studio .NET and ASP.NET, 1160–1161
sharing data with SharePoint Services, 1161–1163
static Web pages
understanding, 1137–1139
viewing, 1139–1140
XML overview, 1135–1137
publishing database to SharePoint site, 1208–1211
about, 1208
.accdb file format required, 1210
downloading SharePoint databases to trusted locations, 1225
moving vs., 1207
publishing changes to migrated database, 1217
republishing after changing database, 1207, 1219–1222
selecting and publishing file, 1210–1211
purchasing digital certificates, 1336