Visual Basic Code in Access

1. Learn about function procedures, Sub procedures, and modules
2. Review and modify an existing Sub procedure in an event procedure
3. Create function procedures in a standard module
4. Create event procedures
5. Compile and test function procedures, Sub procedures, and event procedures
6. Hide text and change display colors
7. Create event procedures for a dialog box.

Important Points.
1. Visual Basic for Applications is a programming language that only compiles within the application. A separate compiled executable file is not created outside of the application (access).
2. Coding is the process of writing VBA instructions.
3. VBA instructions are called statements that respond to events that occur with the objects in a database.
4. VBA is called both an event-driven language and an object-oriented language.
5. An event property specifies how an object responds when an even occurs.
6. An event procedure occurs when access executes (calls) a group of statement when an event occurs.
7. Function procedure, or function, performs operations, returns a value, can accept input values, and can be used in expressions.
8. Sub procedure, or subroutine,performs operations and can accept input values, but does not return a value and cannot be used in expressions.
9. You store a group of related procedures together in a module.
10. Each module starts with a Declarations section, which contains statements that apply to all procedures in the module.
11. A standard module is a database object that is stored in memory with other database objects when you open the database.
12. A procedure that more than one object can use is called a public procedure.
13. A class module is usually associated with a particular form or report.
14. The event procedures in a class module are local procedures, or private procedures, which meant that only the form or report for which the class module was crated can use the event procedure.
15. Focus refers to the record and control that is currently active and awaiting user action.
16. The Current event occurs when a form opens and when the focus moves to another record, the VBA procedure is associated as an event procedure with the form's OnCurrent Property.
17. The Visual Basic Editor is the program you use to create and modify VBA code.
18. The Visual Basic Window is the program window that opens when you use VBA.
19. The Code window is the window in which you create, modify and display VBA code.
20. All event procedures are Sub procedures.
21. Each sub procedure begins with a Sub statement and ends with an End Sub statement.
22. The sub statement includes the scope of the procedure, the name of the procedure, and an opening and closing parenthesis.
23. The Option Compare statement designates the technique Access uses to compare and sort text data.
24. A control structure is a set of VBA statements that work together as a unit.
24a. Case control structure is a conditional control structure which evaluates an expression.
25. An assignment statement assigns the value of an expression to a field or property.
26. A field's ForeColor property determines the field's foreground, or font color.
26a. The RGB (RED,GREEN,BLUE) function returns an RGB color value.
Hyper Links: For 3 number color codes: or

27. The Visible property determines when Access displays a control.
28. The AfterUpdate event simply accepts the new or changed entry into a field.
29. Each function begins with a function statement and ends with an End Functions statement.
30. A placeholder is called a parameter.
31. The value passed to the function and used in place of the parameter when the functions is executed is called an argument.
32. All VBA function names, Sub procedure names, argument names, and other name you create must conform to the following rules:

a. Must begin with a letter
b. Cannot exceed 255 characters
c. Can include letters, numbers, and the underscore.
d. Cannot contain keywords that VBA uses as part of its language.
e. Must be unique.

33. The UCase function accepts a single string argument as input, converts the value of the argument to uppercase letters, and then returns the converted value.
34. A logic error occurs when the procedure produces incorrect results.
35. Use the Immediate window to test VBA procedures without changing any data in the database.
36. An If statement executes one of two groups of statements based on a condition being true or false.
36a. True-statement group is the code that is executed when the condition is true.
36b. false-statement group is the code that is executed when the condition is false.
37. The IsNull function returns True when a filed value is null and false when it is not null.
38. The process of translating modules from VBA to machine language is call compilation. The module is compiled.
39. VBA Data types

Data Type Stores
Boolean True/False Values
Byte Integer values from 0 to to 255
Currency Currency values from -922.337,203,685,477.5808 to 922,337,203,685,477,5807
Date Date and time values from 1 January 100 to 31 December 9999
Decimal Non-Integer values with 0 to 28 decimal places.
Double Non-Integer values from -1.797693313486231*10 to the 308 to -4.9406564584*10 to the 324 for negative values, from 4.9406564584*10 to the 324 to 1.79763913486232*10 to the 308 for positive numbers.
Integer Integer values from -32768 to 32767
Long Integer values from -2,147,483,648 to 2,147,483,647
Object Any object reference
Single Non-integer values from -3.402823 * 10 to the 38 to -1.4012898* 10 to the 43 for negative values, from 1.401298 * 10 to the 45 to 3.402823 * 10 to the 38 for positive values.
String Text values up to 2 billion characters in length
Variant An numeric or string data type

40. The string type is the same as the access text field.
41. The Bollean type is equivalent of the yes/no field type in access.
42. To declare a variable the DIM variable AS type statement is used.
42a. A variable is a location in computer memory that can contain a value that may or may not change during program execution.
43. The CStr function is used to convert a value to a string.
44. A group of statements executed repeatedly is called a loop.
45. Do While, Do Until, and For, Next are looping structures.
46. The Len function returns the number of characters in a string.
46a. Left function returns a string containing a specified number of characters from the left side of a specified string.
46b. The val function returns the numbers contain in a specified string as a numeric value.
46c. DoCmd statement executes an action in a procedure.
46d. CancelEvent acton cancels the event that that caused the procedure or marco contain the action to execute.
46e. Me keyword refers to the current object
47. The Load event occurs when Access opens a form.
47a. BeforeUpdate event occurs before changed data in a control or recorded is updated.
48. SetFocus is a method that moves the focus to the specified object or control.
49. A method is an action that operates on specific objects or controls.
50. StrConv function converts the letters in a string to all uppercase or to lowercase, or fist letter of every word to uppercase and other to lower case.
51. VBA constant is a predefined memory location that is initialized to a value that does not change during program execution.
52. vbuppercase constant specifies the conversion of the string to all uppercase letters.
53. Vblowercase constant specifies the conversion the the string to all lower case.
54. Load event occurs when access opens a form.
Best Practice: