Codey Kolasinski 2012

Visual Basic Access (VBA) Visual Basic Editor How to open the editor Click on the ‘Create’ tab at the top of the Access window and all the way on the right is the ‘Macro’ icon. Click on the bottom half of the icon and it will give you a list of options. Click on ‘Module’ and it will bring up the Visual Basic Editor. This procedure will be slightly different for Access 2003. Important aspects of the Editor o It automatically checks for errors so don’t be afraid of the red markings and popup windows as they are entirely unavoidable even for experts. o To run the code ‘as-is’ there is a play, pause and stop button on the toolbar at the top and they are useful tools as you run through the rest of this tutorial. o Scripts can be saved, imported and exported as well as copied and pasted in this window

Comments Comments are very useful for explaining your code to others. It is done simply by beginning a sentence or note with an apostrophe. The comments will turn green after you have completed the comment and leave the line. These notes can be written at the beginning of a line or at the end of a line of code but a comment cannot be started then a line of code as the line will not be read or executed as code. ‘Create Base Account String Base = [Forms]![custhist]![BaseAccount] Zeros = 0 Nines = 99 Base00 = Base & Zeros & Zeros ‘Add Zeros Base99 = Base & Nines ‘Add Nines ForSQL = "Between " & Base00 & " and " & Base99 Comments are also very useful for ‘commenting out’ code in order to test, rewrite or clean up code.

Variables and Message Boxes Creating Variables

Variables are the same in programming as they were in high school algebra. Variables are declared and values are assigned. A declaration occurs when the computer is told that you want a variable named “X” to exist. Variables can be named anything that you want as long as they start with a letter. A value assignment occurs when you give that variable a value.

Codey Kolasinski 2012 A variable is declared (created) like this: Dim Dim X A variable is assigned (given a value) like this: VariableName = Value X = 4 Just as in algebra, you can add, subtract, multiply and divide variables. Accessing/Using Variables: X = 4 Y = 3 Z = X + Y MsgBox (Z) Here Z is 4 and the ‘MsgBox’ function will create a popup window and display whatever Z is.

X = “Hello” Y = “ World” Z = X + Y Here Z is “Hello World” Variable Data Types Variables can have many different data types including numbers, text, dates, combinations of text and numbers, et al. There is a great resource on data types here: http://msdn.microsoft.com/enus/library/47zceaw7(v=vs.80).aspx. For the most part, VBA will automatically assign a data type of either a number or a letter depending on what it sees. It usually depends on whether or not it sees quotations, text, numbers or some combination. X X X X

= = = =

4 “4” -1 Date()

‘Number ‘Text ‘Variant (data type used with check boxes) ‘Date

The above examples are implicit data type assignment because you do not explicitly tell the computer what data type the variable should be. Variable data types can be explicitly declared so there is no confusion by the following formula Dim As Dim X as Double Dim Today as Date

Codey Kolasinski 2012

Immediate window Start by going to ‘View’ in the VB Editor and then click ‘Immediate Window’. Enter the following: ? Date() or ? Format(Date, "YYYYmmdd") Upon pressing enter the editor will bring up the current date. From here, even with dates, you can add, subtract, divide and multiply variables to see what happens. It is a very useful tool not only to test your code but also to find and repair bugs in the system.

Using Forms and Variables Together Access Object Model (AOB)

Everything in Access is an object from the tables and queries to the VBA scripts and functions. These objects are placed in a hierarchical folder format that can be accessed or called quickly and easily once the names, locations and objects/functions are understood. Here is an example of what the AOB looks like:

Codey Kolasinski 2012 Access Form s FormName Field1 Field2 Field3

FormName2 Field1 Field2 Field3

Queries Query1 Query2

Module s Tables Reports This hierarchy is very much like the windows file system; for example, if you want to access the pictures on your hard drive you need to go through the following folders on Windows XP: C:/Documents and Settings//My Documents/My Pictures. Accessing an object in the Access/Visual Basic hierarchy is done in the following way: [Object Category]![Object Name]![Field Name] By typing the following line into the immediate window, you ask the computer to count the number of forms in the current database by accessing the correct objects in the AOB. ? CurrentProject.AllForms.Count Access Forms

Access forms can be created automatically in Access 2007 but it can be useful to create them by hand for use with VBA. In forms we create objects to help us interact with the computer. Of these objects, textboxes are the most common form object and easy to use and they can be filled with either text or numbers when the form is filled out. Textboxes are created by clicking on the textbox button on the

Codey Kolasinski 2012 form design ribbon and clicking and dragging to the size needed in the form itself. itself A name can then be assign by right clicking on the new textbox, selecting ‘properties’ in the resultant box, then clicking on the ‘other’ tab in the properties box and at the top of the list is the ‘Name’ field. Once the textbox is created and named we now have a way of ent entering ering a value and sending it to a VBA script using a few more steps.

Create a form and call it ‘FirstForm’ and d create a textbox and name it ‘BaseAccount’.

Connecting Forms to VBA Variables VBA and forms work well together; we just need to tell the VBA script where to find the information we want to include in the script using the Access Object Model and the formula for accessing it. it Start by opening your ‘FirstForm FirstForm’ form created in the last section. Using the [Object Category]![Object Name]![Field Name] formula, we can take a value from a field in a form and attach it to variable: X = [Forms]![ [Forms]![FirstForm]![BaseAccount] For example, using the he form ‘FirstForm’, create a script with the following code: code BaseAcc = [Forms]![ [Forms]![FirstForm]![BaseAccount] MsgBox(BaseAcc) Box(BaseAcc) VBA will automatically assign the data type and the new variable ‘‘BaseAcc’ BaseAcc’ and it will contain the value entered into the form ‘BaseAccount’ field in the ‘FirstForm’ form form. It will also print out the value of the variable BaseAcc using the MsgBox command. Once this works a large number of opportunities are revealed using forms and VBA. VBA

Using a ‘GO’ button

Have you ever used a ‘Submit’ Button on a website like this:

Access forms can have submit buttons too which makes for a better form when looked at from a usability stand point.. Just like we created a text box, go to the design view of a form and click on the ‘Button’ option at the top on the design ribbon and click and drag a button to create it. Next, right click on the newly created ed button and click on ‘Build Event’ all the way at the top. This will open the t Visual Basic Editor with the basic information already included. Between the ‘Private ‘ Sub’ and ‘End Sub’ will be the place that you can put your code linking to the form us using ing the usual formula.

Codey Kolasinski 2012 Once you enter code ‘into the button’ the only way to access it will be through the button itself by opening the form in design view and right clicking on the button and then ‘Build Event’ at the top.

Control Structures The computer runs the code from top to bottom which is not always the best way to perform a task. Control Structures limit what code is executed depending much on the values of variables.

‘If’ statements Most used and most simple structure: Sub IfTest() X=[Forms]![CustHist]![BaseAccount] If X > 10000 Then MsgBox (“This is a Newer Account”) ElseIf X < 10000 Then MsgBox (“This is an Older Account”) Else MsgBox (“Something Bad Happened”) End If End Sub The ‘If’ statement reads the value of the variable and makes decisions. If the value is less than 10,000 the message box about Newer Accounts is displayed while if below the Older Account. If the number is exactly 10,000 it does not fit either criteria so it reverts to the default which is the “Something Bad” message box. Again, control structures allow the ‘right’ code to execute while preventing others from firing.

Select statements

Sub SelectTest() X = [Forms]![CustHist]![BaseAccount] Select Case X Case 1 MsgBox Case 2 MsgBox Case 3 MsgBox Case 4 MsgBox

("Your response was 1") ("Your response was 2") ("Your response was 3") ("Your response was 4")

Codey Kolasinski 2012 Case Else MsgBox ("What Happened?") End Select End Sub The variable is weighed by the code and if it fits one of the ‘cases’ (if the variable matches the number or string next to the word ‘case’) the code after and before the next ‘case’ is executed. There are a few other control structures including: o Do o For o While

SQL and DoCmd. RunSQL SQL can be placed in a variable: VariableName = “SQL Statement" SQLforCode = “SELECT * FROM YBPDBA_CUSTOMERHOLDINGS WHERE (((YBPDBA_CUSTOMERHOLDINGS.CUSTOMER_NUMBER) Between 104900 and 104999)”

Running SQL using DoCmd.RunSQL You can also insert variables into a string, in this case an SQL statement. We are inserting a base account string into the SQL statement so that it is flexible and able to pull in any holdings data that we want by entering a different base account number into the same field in the form ‘FirstForm’. Base = [Forms]![FirstForm]![BaseAccount] Zeros = 0 Nines = 99 Base00 = Base & Zeros & Zeros Base99 = Base & Nines ForSQL = "Between " & Base00 & " and " & Base99

SQLforCode = “SELECT * FROM YBPDBA_CUSTOMERHOLDINGS WHERE (((YBPDBA_CUSTOMERHOLDINGS.CUSTOMER_NUMBER) " & ForSQL & "))” RunCmd.RunSQL = SQLforCode In the SQLforCode variable we have inserted the string into another string. The first quote ends the code and the first ampersand appends the ForSQL variable to the string. The second ampersand tells the computer to append the rest of the following code and the next quote restarts the computer reading the string in the normal way.

Codey Kolasinski 2012

Other Topics Subs vs. Functions Functions and Subs differ in only one respect. Functions inherently have the ability to return values while subs only perform tasks. Function Testing(X As Double) As Double Testing = X + 3 End Function A function is a prewritten script that performs a specific function. A great example is the Date() function in that it returns the current date. Try this in the Immediate Window: ? Date() Advanced Form Objects

Start by opening a new form in design view to go through these form objects:

Check Boxes: Check boxes are great for yes or no questions. If the check box is checked the value that can be sent to a script will be -1 and can be accessed using the same [Forms]![CustHist]![BaseAccount] function before. If the checkbox is not checked the value will be 0 (zero). The data type for this particular variable must be Variant and declared before the variable value is assigned. Check boxes work in the same way textboxes to only there is only a Yes or No response. However, you still assign a name and use the same formula to access the form’s value. Drop Boxes: Drop Boxes are useful because they are created by filling in options into the drop box which cannot be changed. This formula is perfect for the Select control structure which requires certain amount of regularity to work. Drop Boxes require one extra step after creation and naming in the properties section. After assigning the name, click on the ‘data’ tab change the “row source type” to “value list” and enter the values you want to display in the ‘row source’. They are read using a tab delimited system so if you want ebrary, EBL and EBSCOhost in one list you must list them like this: ebrary;EBL;EBSCOhost

Codey Kolasinski 2012 Operators = < >=

Equals Not Equal Less Than Less Than or Equal To Greater Than Greater Than or Equal To