Learn Access 2003 VBA with The Smart Method

100

www.LearnAccessVBA.com

Lesson 5-6: Set and retrieve

form control values from

within VBA

Appreciate that all form controls are objects with Properties, Methods

and Events. We are going to refine frmTest so that the user can enter the

numbers to be added into text boxes on the form. We can then pick up

the values by inspecting the Value property of each Text Box object and

will then display the result in a Text Box control on the same form.

1

Add three text box controls and three label controls to the

form so that it looks like this:

2

Name the three Text Box controls txtFirstNumber,

txtSecondNumber and txtResult.

3

Change the appearance of the txtResult text box by setting

the following properties:

Make sure you are in Design View before changing the properties

otherwise you won’t see any effect until the form is closed, saved

and re-opened. Note also that, due to a bug in Access, the Border

Color property must be set first (see sidebar).

Property

Value

Border Color 8421504

(Gray)

This color works well for the

sunken border.

Border Style Solid

The sunken effect needs a border.

Locked

Yes

Stops the user typing into the

control.

Enabled

No

Stops the control having focus.

Tab stop No

Takes the control out of the tab

order.

Back Style Transparent Makes the inside color of the

control the same as the form.

Special Effect Sunken Makes the control appear sunken.

Important

Text Box controls have been

named using the generally

accepted prefix of txt.

In keeping with the cradle to the

grave naming convention the

remainder of the control name

should always be exactly the

same as the relevant caption on

the form.

These naming conventions and

rules are included in Appendix

A: The Rules.

Important

Note that there’s a bug in the

Border Color property as it sets

the Special Effect property to

Flat whenever it is changed.

For this reason it is important

that you set the Border Color

property first.

Session5c

Session Five: Professional Grade VBA

© 2007 The Smart Method Ltd

101

The text box now has an appearance that intuitively tells the user

that it is a read-only control.

Another commonly used style for read-only text boxes is the Flat

effect. Use whichever you like the look of best.

4

Open the VBA Editor.

Right click on the cmdAddTwoNumbers Command Button and select

Build Event from the shortcut menu (select Code Builder from the

Choose Builder dialog if it appears). The code for the Command

Button’s Click event is displayed in the code editor.

5

Modify the code so that the input values are taken from the

relevant Text Box’s Value property and written to the

txtResult control’s Value property.

Note the use of dot notation to reference each object’s properties.

Private Sub cmdAddTwoNumbers_Click()

Dim dblFirstNumber As Double

Dim dblSecondNumber As Double

Dim dblSumOfNumbers As Double

dblFirstNumber = Me.txtFirstNumber.Value

dblSecondNumber = Me.txtSecondNumber.Value

dblSumOfNumbers = AddTwoNumbers(dblFirstNumber, _

dblSecondNumber)

Me.txtResult.Value = dblSumOfNumbers

End Sub

6

Test the command button.

Switch to Form view. Enter two numeric values into the text boxes

and then click the Add Two Numbers command button.

tip

The line

Me.txtFirstNumber.Value

returns the Value property of

the text box named

txtFirstNumber on the current

form.

Even though the Me keyword is

optional it is extremely useful

as typing Me followed by a dot

will display all objects relevant

to this form.

You may, of course, want to

also access properties from

controls on other forms when

the Me keyword would not be

appropriate. We’ll discover

how to do this in a future

session.

Important

Default properties

VBA has a very confusing

“feature" in that every control

has a default property.

Me.txtResult.Value

and

Me.txtResult

…are functionally equivalent

because Value is the default

property of an Access Text Box

control.

The default property feature

has (thankfully) been removed

from the latest versions of

stand-alone VB (VB.Net and VB

2005) showing that Microsoft

also agree that it isn’t the best

feature in the world.

Never use default properties in

your VBA code as they make

the code less readable and more

prone to error (as the actual

property being manipulated

must remain in the

programmer’s memory).

This information is provided so

that you will understand code

samples and code within

existing applications as many

non-professional programmers

are extremely fond of using

default properties.