Programming in Microsoft Access using VBA

1. Programming in Microsoft Access using VBA Using VBA to add functionality

2. Introduction • Writing Visual Basic for Applications (VBA) code helps to automate your database and makes your database more powerful. The following notes assume that you have a basic knowledge (gained from the three open learning Access books) of creating simple databases with related tables, queries and forms.

3. Outcomes • At the end of this session you will understand: • The role of objects in the Access GUI • Event-driven programming • Introductory programming concepts • How to use VBA to add functionality to a form

4. Objects • Physical control objects, like textboxes and command buttons are physical entities that have properties and events associated with them. In VBA programming you have to ensure that the focus of the programming sequence is on the object you wish to manipulate. The SetFocus function does this and is assigned to the object (in this case a textbox) in the following way: • TextBox1.SetFocus

5. Event-driven programming • Unlike procedural programming languages where the code is read and executed from top to bottom, apart from loops and other constructs, Access VBA is an event-driven language. • This means that the events are executed when the user interacts with the Graphical User Interface (GUI) • For example, a message appears when the user clicks on a button

6. Introduction to Programming • Programming allows you to: • Issue commands to make the system do something • Control how and when these commands are issued • In general, each line in a program consists of a single command or control

7. Programming concepts:Variables A variable holds a value in memory for temporary storage for the time that the block of code is being executed. For example you could assign a variable with the name Surname, data type: String This stores the value of a surname that can be input into a text box for example. The physical object that is used to manipulate the variable in this case is the textbox.

8. Objects • Physical control objects, like textboxes and command buttons are physical entities that have properties and events associated with them. In VBA programming you have to ensure that the focus of the programming sequence is on the object you wish to manipulate. The SetFocus function does this and is assigned to the object (in this case a textbox) in the following way: TextBox1.SetFocus

9. Declaring variables • Any variables created in VBA programming must be declared as follows: • Dim Surname as String • Dim Num as Single • Dim BirthDate as Date Variable Data type

10. VBA statements and procedures • When you combine constants, variables, objects, operator symbols, properties and methods in a single operation, for a set purpose, you produce a statement. When you VBA programme in VBA, you put together the statements in a particular order, following specific syntax rules, into a procedure.

11. Function procedure • A function procedure is a particular form of procedure that performs a task that may return a value. This returned value is computed in the procedure and assigned to the function name as one of the procedure’s statements. There are several useful in-built functions such as MsgBox function, InputBox function

12. Variables to store data • When you want to write a procedure or function you will need to declare any variables to hold temporary data. • For example if you want to write a procedure to multiply 2 numbers that will be input by the user then you will first need to put two input boxes on a form, a command button to trigger the event and a text box for display.

13. Control structures • VBA executes a procedure’s statements in sequence beginning with the first statement. • It reads the code left to right and then top to bottom-this is called sequential flow. • Control constructs influence the execution of a program so that if you want one set of statements to be executed rather than another i.e you want to change the order then you can use constructs to do this. • You can use decision structures to test conditions and then perform a particular statement or set of statements depending on the outcome. Or you can use loop structures to execute a set of structures repetitively.

14. Making decisions • If ……..then • The words in italics are the condition , words in bold are the construct commands, words in normal text arethe statement • If you are feeling too hot then • Take some of your clothes off • End if • (The above algorithm is a form of pseudocode-like a recipe to do something in plain English and not in a programming language

15. Programming an activity

16. Simple interaction with the user • Open a new form and put a command button on the form and name it cmdAnswer, a label with the caption “The best football team!”, and a textbox called txtTeam with the label “Which is the best team?”. • In design view open the code dialog box and type the following in the section for the object cmdAnswer and event

17. Writing the code Note the object box And this is the event box So when the button is pressed (object button triggers click event) the code is executed and a message box appears.

18. Summary • Access is an even-driven programme • When you interact with the interface objects, events happen • You can add programming constructs using VBA • The tutorial is Exercise 10. • The tutorial covers sessions 15 and 16.