Tutorial 11 12

Tutorial 11 Using Visual Basic for Applications Code (AC 560 - AC 563 & AC 590 - AC 596)

Visual Basic for Applications n

Microsoft Office’s native programming language

n

More powerful and flexible alternative to macros n

Ch 10: attached a macro to a command button

n

Ch 11: attached VB code to a command button

n

n

n

user clicks button à Click event occurs à macro runs user clicks button à Click event occurs à VBA procedure runs

Module n

a container for related VBA procedures n n

n

n

subroutine function

each Form object and Report object can have its own module Standard Module objects contain VBA code independent of a specific form/report

2

1

Events: Review n

A state, condition, or occurrence on/with a certain object for which you can define a response n

most events occur as a result of a user action n

e.g.: a mouse click, a key press, navigate to another record

n

Events provide an opportunity for your application to do something

n

Access has hundreds of events

n

run a macro or a Visual Basic procedure in response to an event

Object

Event

Command Button

Click

Form

Load

Form

Current

Form

Close

Form

BeforeUpdate

Combo Box

AfterUpdate

Occurs When

3

Creating the Procedures for the Queries Dialog Box Form

Private Sub Form_Load() 'Move the focus to the first query

lstQueryList.SetFocus SendKeys "{Down}" End Sub 4

2

Creating the Procedures for the Queries Dialog Box Form n

n

Used Visual Basic code to make the Preview and Display buttons come alive General Process n

n

create Visual Basic procedure that performs the appropriate task(s) attach the procedure to the appropriate event so the code can run when the event occurs

5

Creating the Procedures for the Queries Dialog Box Form

Private Function

basPreviewQuery()

'Open the selected query in Print Preview DoCmd.OpenQuery lstQueryList, acViewPreview End Function

Private Function

basDisplayQuery()

'Open the selected query in Datasheet View DoCmd.OpenQuery lstQueryList, acViewNormal End Function 6

3

Using an Event Procedure to Synchronize a Combo Box n

Practice Time n

in issue25.accdb, open frmWritersAndArticles, use the combo box to navigate to two other writers n n

n

n

which event was used to provide this functionality? what macro action was used to provide this functionality?

now, use Navigation Buttons to view other writers… watch the combo box as you do so… it doesn’t change! when we navigate to another record on the form, we need to have the combo box’s value change to match the writer being displayed n

add VBA code to the form’s Current event to set the combo box value to match the writerid for the writer being displayed Private Sub Form_Current() Me.cboToDisplay = Me.WriterID End Sub

n

test that it works correctly & save frmWritersAndArticles

7

Using an Event Procedure to Track Record Changes n

Practice Time n

modify the Writer table to include two new fields: n n

n

ModifiedDate Date & Format with date and time ModifiedBy Text(40)

modify frmWritersAndArticles n n

add the fields to the form (Locked, Enabled, BorderStyle, BackStyle) place the following VBA code into its BeforeUpdate event procedure Private Sub Form_BeforeUpdate(Cancel As Integer) Me.ModifiedDate = Now() Me.ModifiedBy = OSUser() End Sub

n n

test that it works correctly save frmWritersAndArticles 8

4

Tutorial 12 Securing an Access Database Database Startup Options (AC 639 - AC 647)

Securing an Access Database n

Encryption n

n

Password n

n

to control access to the database and its contents

To have Access encrypt and password protect n n

n

scrambles database data so it is unreadable outside of Access

Office Button | Open | Open Exclusive Database Tools | Encrypt With Password

Practice Time: n

n

encrypt your issue25.accdb and set a database password of artICHoke236%J close issue25 and be sure you can reopen it with artICHoke236%J

10

5

Setting Database Startup Options Actions that take place automatically whenever the .ACCDB file is opened

n

Office Button | Access Options | Current Database

n

11

Setting Database Startup Options Practice Time: set Startup Options to n provide an application title of Trollen Publishers, Inc.

n

disallow special Access keys disable Layout View disable Design Changes display the Navigation Pane not allow full menus

n n n n n

disable shortcut menus close and reopen issue25 to test

n n

n

A user can bypass the StartUp options by holding the [Shift] key as they open the database n n

Demo: close issue25.accdb, then reopen while holding [Shift] A developer can create a database property called AllowByPassKey to take away 12 this capability

6