Transcript Document

© Paradigm Publishing, Inc.
1
Access 2010
Level 2
Unit 2 Advanced Reports, Access Tools,
and Customizing Access
Chapter 7 Automating, Customizing, and
Securing Access
© Paradigm Publishing, Inc.
2
Automating, Customizing, and Securing
Access
Quick Links to Presentation Contents










Create a Macro
Edit and Delete a Macro
Create a Command Button to Run a Macro
Create a Navigation Form
CHECKPOINT 1
Customize the Access Environment
Customize the Ribbon
Create an ACCDE Database File
View Trust Center Settings for Access
CHECKPOINT 2
© Paradigm Publishing, Inc.
3
Create a Macro
 A macro is a series of instructions stored in sequence
that can be recalled and carried out whenever the
need arises.
 Macros are generally created when a specific task is
carried out frequently and consistently.
 The macro object stores a series of instructions (called
actions) in the order in which they are to be
performed.
 Macros appear as objects within the Navigation pane.
© Paradigm Publishing, Inc.
4
Create a Macro…continued
To create a macro:
1.
2.
3.
4.
5.
6.
7.
8.
9.
Click the Create tab.
Click the Macro button in
the Macros & Code group.
Click the Add New Action
list arrow.
Click the desired action.
Enter the arguments as
required in the Action
Arguments section.
Click the Save button.
Type the name for the
macro.
Click OK.
Repeat Steps 3-6 as
needed.
© Paradigm Publishing, Inc.
Macro Builder
Window
5
Create a Macro…continued
 The OpenForm action is used to open a form (similar
to double-clicking a form name in the Navigation
pane).
action arguments for
OpenForm action
© Paradigm Publishing, Inc.
6
Create a Macro…continued
 Use the Filter Name or Where Condition arguments to
restrict the records displayed in the report.
 The Data Mode argument is used to place editing
restrictions on records while the form is open.
 The Window Mode argument is used to instruct Access
to open the form in Normal mode (how you normally
view forms in the work area), Hidden mode (the form
is hidden), Icon mode (the form opens minimized), or
Dialog mode (the form opens in a separate window
that is similar to a dialog box).
© Paradigm Publishing, Inc.
7
Create a Macro…continued
 To create a macro with multiple actions, add the
second instruction in the Add New Action list box that
appears below the first action.
 The GoToControl action is used to activate a control
within a form or report and the RunMenuCommand
action is used to execute an Access command.
 As you add actions to the Macro Builder window, you
can expand and collapse the Action Arguments section
as needed.
© Paradigm Publishing, Inc.
8
Create a Macro…continued
To create a macro by
dragging and dropping an
object:
1. Open the database.
2. Click the Create tab.
3. Click the Macro button
in the Macros & Code
group.
4. Drag the object to the
Add New Action list box.
5. Save the macro.
6. Click OK.
© Paradigm Publishing, Inc.
Add New Action
list box
9
Create a Macro…continued
To run a macro:
1. Click the Macro
Tools Design tab.
2. Click the Run button
in the Tools group.
© Paradigm Publishing, Inc.
Run
button
10
Edit and Delete a Macro
To edit a macro:
1. Right-click the
macro name in
the Navigation
pane.
2. Click the Design
View option at
the shortcut
menu.
3. Edit as desired.
4. Save the revised
macro.
© Paradigm Publishing, Inc.
Design
view
11
Edit and Delete a Macro…continued
To delete a macro:
1. Right-click the macro
name in the
Navigation pane.
2. Click the Delete option
at the shortcut menu.
3. At the Microsoft
Access dialog box, click
Yes.
© Paradigm Publishing, Inc.
Delete
option
12
Create a Command Button to Run a Macro
To create a command
button in a form:
1. Open the form in
Design view.
2. Click the Button button.
3. Drag to create the
button.
4. Click the Miscellaneous
option.
5. Click the Run Macro
option.
6. Click Next.
Run Macro
option
continues on next slide…
© Paradigm Publishing, Inc.
13
Create a Command Button to Run a Macro…continued
7. Click the desired
macro name.
8. Click Next.
9. Click the Text option.
10. Select the current
text in the Text text
box.
11. Type the text you
want to appear on
the button.
12. Click Next.
Text
text box
continues on the next slide…
© Paradigm Publishing, Inc.
14
Create a Command Button to Run a Macro…continued
13. Type a name for the
command button.
14. Click Finish.
name
© Paradigm Publishing, Inc.
15
Create a Command Button to Run a Macro…continued
 An embedded macro is a macro that is stored within a
form, report, or control. An embedded macro runs
when a specific event occurs.
 Clicking the button is the event that causes the macro
action to be performed.
 You can view the embedded macro by opening the
command button’s Property Sheet and clicking the
Event tab.
© Paradigm Publishing, Inc.
16
Create a Command Button to Run a Macro…continued
To view the macro code
for a command button:
1. Open the form in
Design view.
2. Click to select the
command button.
3. Display the Property
Sheet.
4. Click the Event tab.
5. Click the Build button
in the On Click property
box.
© Paradigm Publishing, Inc.
Build
button
17
Create a Command Button to Run a Macro…continued
 Macros enable you to add automation or functionality
without having to learn how to write programming
code.
 In the Microsoft Office suite, Visual Basic for
Applications (VBA) is the programming language used
to build custom applications that operate within Word,
Excel, PowerPoint, and Access.
 A quick way to start a VBA program is to create a
macro and then convert the macro to VBA code.
© Paradigm Publishing, Inc.
18
Create a Command Button to Run a Macro…continued
To convert a macro to
Visual Basic:
1. Open the macro in
Design view.
2. Click the Macro Tools
Design tab.
3. Click the Convert
Macros to Visual Basic
button in the Tools
group.
4. Click the Convert
button.
5. Click OK.
© Paradigm Publishing, Inc.
Convert Macros to
Visual Basic button
19
Create a Navigation Form
 Database files are often accessed by multiple users for
a variety of purposes, such as updating a customer
record or entering details related to a completed work
order.
 A Navigation form with tabs along the top, left, or right
side is used as a menu with which end users can open
the forms and reports they need to update, view, or
print data.
© Paradigm Publishing, Inc.
20
Create a Navigation Form…continued
To create a navigation form:
1. Click the Create tab.
2. Click the Navigation button
in the Forms group.
3. Click the desired form
style.
4. Drag the form or report
name to the [Add New] tab
in the Navigation Form.
5. Repeat Step 4 as needed.
6. Click Save.
7. Type the form name.
8. Click OK.
© Paradigm Publishing, Inc.
21
[Add New]
tab
1) This is a series of instructions that
can be recalled and carried out
whenever the need arises.
a. form
b. macro
c. table
Answer
d. report
3) This button instructs Access to
carry out the instructions in a
macro.
a. Start
b. Execute
c. Go
Answer
d. Run
2) The Macro button is located in
this tab.
a. Home
b. Create
c. External Data
d. Database Tools
4) This is used as a menu with which
end users can open forms and
reports.
a. Navigation form
b. Menu form
c. Object form
d. Find form
Answer
Next Question
Answer
Next Question
Next Question
© Paradigm Publishing, Inc.
Next Slide
22
Customize the Access Environment
 To customize the Access environment, click the File tab
and then click the Options button located near the
bottom of the left pane in the Info tab Backstage view
to open the Access Options dialog box.
 A form can be set to display automatically whenever
the database file is opened. You can also choose to
show or hide the Navigation pane in the current
database.
 Databases can be set to open by default in shared use
or exclusive use.
© Paradigm Publishing, Inc.
23
Customize the Access Environment…continued
To set a startup form:
1. Click the File tab.
2. Click Options.
3. Click the Current
Database in the left
pane.
4. Click the downpointing arrow next
to the Display Form
list box.
5. Click the desired
form.
6. Click OK.
© Paradigm Publishing, Inc.
Display Form
list box
24
Customize the Access Environment…continued
 When you have chosen to secure a database by
creating a startup form with access to a limited
selection of objects, you may also want to limit access
to the options in the ribbon and menus.
 Preventing end users from seeing the full ribbon and
all of the shortcut menus allows you to prevent others
from accidentally making changes.
© Paradigm Publishing, Inc.
25
Customize the Access Environment…continued
To hide the navigation pane:
1. Click the File tab.
2. Click Options.
3. Click the Current
Database option in the
left pane.
4. Clear the Display
Navigation Pane check
box.
5. Click OK.
6. Click OK.
© Paradigm Publishing, Inc.
26
Display Navigation
Pane check box
Customize the Access Environment…continued
To customize the
navigation pane:
1. Click the File tab.
2. Click Options.
3. Click the Current
Database option in the
left pane.
4. Click the Navigation
Options button.
5. At the Navigation Options
dialog box, select the
desired options.
6. Click OK.
7. Click OK.
© Paradigm Publishing, Inc.
Navigation Options
dialog box
27
Customize the Access Environment…continued
To customize error checking
options:
1. Click the File tab.
2. Click Options.
3. Click the Object Designers
option in the left pane.
4. Scroll down to the Error
checking in form and
report design view
section.
5. Clear the check boxes as
required.
6. Click OK.
© Paradigm Publishing, Inc.
28
Error checking in
form and report
design view section
Customize the Access Environment…continued
Error checking option
Description
Enable error checking
Turn on or off error checking in forms and
reports. An error is indicated by a green
triangle in the upper left corner of a control.
Check for
unassociated label
and control
Access checks a selected label and text box
control object to make sure the two objects
are associated with each other. A Trace Error
button appears if Access detects an error.
Check for new
unassociated labels
New label control objects are checked for
association with a text box control object.
continues on next slide…
© Paradigm Publishing, Inc.
29
Customize the Access Environment…continued
Error checking option
Description
Check for keyboard
shortcut errors
Duplicate keyboard shortcuts or invalid
shortcuts are flagged.
Check for invalid
control properties
Invalid properties, formula expressions, and
field names are flagged.
Check for common
report errors
Reports are checked for errors such as invalid
sort orders or widths larger than the selected
paper size.
Error indicator color
A green triangle indicates an error in a
control. Click the Color Picker button to
change the triangle to a different color.
© Paradigm Publishing, Inc.
30
Customize the Ribbon
To customize the ribbon:
1. Click the File tab.
2. Click the Options
button.
3. Click the Customize
Ribbon option in the
left pane.
Customize Ribbon
option
© Paradigm Publishing, Inc.
31
Customize the Ribbon…continued
To create a new tab and
group:
1. Click the File tab.
2. Click the Options
button.
3. Click the Customize
Ribbon option in the
left pane.
4. Click the name of the
tab that will precede
the new tab.
New Tab
button
5. Click the New Tab
button.
© Paradigm Publishing, Inc.
32
Customize the Ribbon…continued
To rename a tab or group:
1. Click the File tab.
2. Click the Options button.
3. Click the Customize
Ribbon option in the left
pane.
4. Click the tab or group to
be renamed.
5. Click the Rename button.
6. At the Rename dialog box,
type the new name.
Rename
7. Click OK.
dialog box
© Paradigm Publishing, Inc.
33
Customize the Ribbon…continued
To add buttons to a
group:
1.
2.
3.
4.
5.
6.
7.
Click the File tab.
Click the Options button.
Click the Customize Ribbon
option in the left pane.
Click the group name in
which to insert the new
button.
Change the Choose
commands from option to
the desired command list.
Scroll down and click the
desired command.
Add
button
Click the Add button.
© Paradigm Publishing, Inc.
34
Customize the Ribbon…continued
To restore the ribbon:
1. Click the File tab.
2. Click the Options
button.
3. Click the Customize
Ribbon option in the
left pane.
4. Click the Reset button.
5. Click the Reset all
customizations option
at the drop-down list.
6. Click Yes.
7. Click OK.
© Paradigm Publishing, Inc.
Reset all customizations
option
35
Create an ACCDE Database File
 In an ACCDE file, end users are prevented from making
changes to the design of objects.
 An Access database stored as an ACCDE file is a lockeddown version of the database that does not provide
access to Design view or Layout view.
 In addition, if the database contains any Visual Basic
for Application (VBA) code, that code cannot be
modified or changed.
© Paradigm Publishing, Inc.
36
Create an ACCDE Database File…continued
To make an ACCDE file:
1.
2.
3.
4.
5.
6.
7.
8.
Open the database.
Click the File tab.
Click the Save & Publish
tab.
Click the Make ACCDE
option.
Click the Save As
button.
Navigate to the
required drive and/or
folder.
Type the name in the
File name text box.
Click the Save button.
© Paradigm Publishing, Inc.
Make ACCDE
option
37
View Trust Center Settings for Access
 The Trust Center maintains a Trusted Locations list.
Content stored within these locations can be
considered a trusted source.
 You can add a path to the trusted locations list and
Access will treat any files opened from that drive or
folder as safe.
 Databases opened from trusted locations do not
display the Security Warning in the message bar and
Access will not block their content.
© Paradigm Publishing, Inc.
38
View Trust Center Settings for Access…continued
 Before macros can be enabled in a database, the Trust
Center checks for a valid and current digital signature
signed by an entity that is stored in the Trusted
Publishers list.
 The Trusted Publishers list is maintained by you on the
computer you are using.
 A trusted publisher is added to the list when you
enable content from an authenticated source and click
the option to Trust all content from this publisher.
© Paradigm Publishing, Inc.
39
View Trust Center Settings for Access…continued
To view the Trust Center
options:
1.
2.
3.
4.
5.
6.
7.
Click the File tab.
Click Options.
Click the Trust Center
option in the left pane.
Click the Trust Center
Settings button.
At the Trust Center dialog
box, click the desired Trust
Center category in the left
pane.
View and/or modify
required options.
Click OK twice.
© Paradigm Publishing, Inc.
Trust Center
dialog box
40
View Trust Center Settings for Access…continued
Macro Setting
Description
Disable all macros without
notification
All macros are disabled; security alerts will not
appear.
Disable all macros with
notification
All macros are disabled; security alert appears
with the option to enable content if you trust
the source of the file. This is the default setting.
Disable all macros except
digitally signed macros
A macro that does not contain a digital
signature is disabled; security alerts do not
appear. If the macro is digitally signed by a
publisher in your Trusted Publishers list, the
macro is allowed to run. If the macro is digitally
signed by a publisher not in your Trusted
Publishers list, you receive a security alert.
Enable all macros
All macros are allowed; security alerts do not
appear. This option is not recommended as it
can allow dangerous code to run.
© Paradigm Publishing, Inc.
41
1) To hide the Navigation pane, click
the Options button in this tab.
a. File
b. Home
c. Create
d. External Data
Answer
3) Restore the original ribbon by
clicking this button.
a. Restore
b. Reset
c. Default
d. Standard
Answer
Next Question
2) You can customize the ribbon by
adding this.
a. a tab
b. a form
c. a table
d. an option
Answer
Next Question
4) In this type of file, end users are
prevented from making changes
to the design of objects.
a. AACDE
b. ABCDE
c. ACCDE
d. ACDDE
Answer
Next Question
© Paradigm Publishing, Inc.
Next Slide
42
Automating, Customizing, and Securing
Access
Summary of Presentation Concepts
 Create, run, edit, and delete a macro
 Assign a macro to a command button on a form
 View macro code for a command button created in a form’s
Property Sheet
 Convert macros to Visual Basic
 Create and edit a navigation form
 Change database startup options
 Show and hide the navigation pane
 Customize the navigation pane by hiding objects
 Define error checking options
 Customize the ribbon
 Create an ACCDE database file
 View Trust Center settings
© Paradigm Publishing, Inc.
43