Access Code Snippets Page
These Code Snippets are contributed by you (our online community members). They are organized by our knowledge base topics. Specifically, by the Access sub-topics.
|
29 Microsoft Access Code Snippets
Group: Microsoft Access
Topic: Access & VBA
|
1. Access VBA Array (x = Array()) |
|
Arrays in Access VBA use a 0-based indice. UBound returns -1 if the array has no elements, 0 if it has 1, 1 if it has 2, etc.
Dim MyArray As Variant Dim i As Integer MyArray = Array("Mike", "Lisa", "Felicia", "Nathan") If UBound(MyArray) > -1 Then For i = 0 To UBound(MyArray) MsgBox (MyArray(i)) Next End If
|
|
2. Access VBA Empty String Check (Len(s&vbNullString)) |
|
In Access VBA, you have to add an empty string to the value being compared in order to get consistent results. For example, add &"" to your string varilable or it's code equivalent &vbNullString. Then compare to an empty string or verify it's length to 0 with Len.
All these will work for variables unassigned, set to "", or set to Null: If s&"" = "" Then MsgBox ("Quotes with &'' say null is empty") End If If Len(s&"") = 0 Then MsgBox ("Len with &'' says null is empty") End If If Len(s&vbNullString) = 0 Then MsgBox ("Using vbNullString also works!") End If
|
|
3. Access VBA Parameters (ByRef, ByVal) |
|
By Reference or Value For parameters, you can optionally specify ByVal or ByRef. ByRef is the default if you don't specify.
Function SomeRoutine(ByRef pPerson, ByVal pName, Age)
|
Topic: Language Basics
|
4. Access VBA Assignment (=) |
|
Access uses = for it's assignment operator.
Dim Age As Integer Dim FullName As String FullName = "Randy Spitz" Age = 38
|
|
5. Access VBA Case Sensitivity (No) |
|
Access VBA is not case sensitive. Like VB Classic, if you type any other case for command or variable names, Access VBA will change it to the "accepted" or "defined" case. For example, if you type msgbox it is converted to Msgbox.
The following code works: MsgBox ("hello")
|
|
6. Access VBA Code Blocks (End Xxx) |
|
Access VBAcode blocks are surrounded by statement ending keywords that all use End such as End Sub, End If, and WEnd.
Sub x End Sub If x Then End If While x WEnd
|
|
7. Access VBA Comments (' or REM) |
|
Access VBA, like all the VB-based languages, uses a single quote (') or the original class-style basic "REM" (most developers just use a quote). Access VBA does NOT have a multiple line comment. Directives are sometimes called compiler or preprocessor directives. A # is used for directives within Access VBA code. Access VBA offers only an #If..then/#ElseIf/#Else directive.
'Single line comment.
REM Old school single line comment. #If MyDirective Then '...some code. #End If
|
|
8. Access VBA Comparison Operators (=, <>) |
|
//Does Access evaluate the math correctly? No! If 0.1 + 0.1 + 0.1 = 0.3 Then MsgBox "correct" Else MsgBox "not correct" End If
|
|
9. Access VBA Constants (Const kPI = 3.1459) |
|
Scope can be Public, Global, or Private. The use of the newer Public keyword is preferred to the older Global. Private Const is the same as just specifying Const.
Const kPI = 3.1459 Const kName = "Mike" //Public variable: Public Const kFeetToMeter=3.28, kMeterToFeet=.3
|
|
10. Access VBA Deployment Overview |
|
|
|
11. Access VBA Development Tools |
|
Languages Focus: Development ToolsPrimary development tool(s) used to develop and debug code.
Access VBA Development ToolsMicrosoft Office Access is the primary tool and does include pretty good debugging features, some limited OOP features such as designing a class and instantiating an object, and, best of all, MS still has developers working on MS Access (as opposed to Corel Paradox).
|
|
12. Access VBA End of Statement (Return) |
|
Languages Focus: End of StatementIn coding languages, common End of statement specifiers include a semicolon and return (others exist too). Also of concern when studying a language is can you put two statements on a single code line and can you break a single statement into two or more code lines.
Access VBA End of StatementA return marks the end of a statement and you cannot combine statements on a single line of code. You can break a single statement into two or more code lines by using a space and underscore " _".
MsgBox "Hello1" MsgBox "Hello2" MsgBox "Hello3"
'The following commented code on a single line does not work... 'MsgBox "Hello4" MsgBox "Hello5" 'Two or more lines works too with a space+underscore: MsgBox _ "Hello6";
|
|
13. Access VBA File Extensions (.MDB) |
|
- .MDB - Access Database
- .MDE - Protected Access Database
|
|
14. Access VBA If Statement (If..ElseIf..Else..End If) |
|
The End If is optional if you put your code on a single line.
//Single line example. If X = True Then MsgBox "hello" //Complete example. If X = True Then '>>>do something. ElseIf Y = "ABC" Then '>>>do something. Else '>>>do something. End If
|
|
15. Access VBA Literals (quote) |
|
Literals are quoted as in "Prestwood". If you need to embed a quote use two quotes in a row.
MsgBox ("Hello") MsgBox ("Hello ""Mike"".") 'Does Access VBA evaluate this simple 'floating point math correctly? No! If (.1 + .1 + .1) = 0.3 Then MsgBox "Correct" Else MsgBox "Not correct" End If
|
|
16. Access VBA Logical Operators (and, or, not) |
|
Same as VB. Access VBA logical operators:
and |
and, as in this and that |
or |
or, as in this or that |
Not |
Not, as in Not This |
'Given expressions a, b, c, and d: If Not (a and b) and (c or d) Then 'Do something. End If
|
|
17. Access VBA Overview and History |
|
Microsoft Access is a class-based
language. Although you can create classes, Access VBA is not fully OOP.
You can create classes, but not inherit from them. It is a traditional language with a few OOP extensions. You code in a
traditional approach using functions, procedures, and global data, and
you can make use of simple classes to help organize your reusable code. Microsoft Access is most suitable for creating business desktop applications that run within Microsoft Access for Windows.
|
|
18. Access VBA Report Tools Overview (Built-In) |
|
Microsoft Access offers a built-in reporting tool that will suffice for most desktop database applications.
|
|
19. Access VBA String Concatenation (& or +) |
|
Although you can use either a & or a + to concatenate values, my preference is to use a + because more languages use it. However, if you use & then some type conversions are done for you. If you use + you will sometimes have to cast a value to concatenate it. For example, you will have to use CStr to cast a number to a string if you use the + operator as a concatenation operator.
Dim FirstName As String Dim LastName As String FirstName = "Mike" LastName = "Prestwood" MsgBox "Full name: " & FirstName & " " & LastName MsgBox "2+2=" + CStr(2+2)
|
|
20. Access VBA Unary Operators |
|
An operation with only one operand (a single input) such as + and -.
|
|
21. Access VBA Variables (Dim x as Integer) |
|
Access VBA is a loosely typed language. Declaring variables is optional unless you use the Option Explicit statement to force explicit declaration of all variables with Dim, Private, Public, or ReDim. Using Option Explicit is strongly recommended to avoid incorrectly typing an existing variable and to avoid any confusion about variable scope. Variables declared with Dim at the module level are available to all procedures within the module. At the procedure level, variables are available only within the procedure.
Dim FullName As String Dim Age As Integer Dim Weight As Double FullName = "Mike Prestwood" Age = 32 Weight = 154.4 'Declaritive assignment not supported: ''Dim Married As String = "Y" '>>>Not supported.
|
Topic: Language Details
|
22. Access VBA Associative Array (Collection) |
|
In addition to Add and Item, collections also offer Count and Remove. Notice that Add uses the format of Value, Key (which is backwards from many other languages).
Dim States As New Collection States.Add "California", "CA" States.Add "Nevada", "NV" MsgBox (States.Item("CA"))
|
|
23. Access VBA Custom Routines (Sub, Function) |
|
Access VBA is a non-OOP language with some OOP features. It offers both Subs and Functions. A Sub does not return a value while a Function does. When Subs and Functions are used in a class module, they become the methods of the class.
Sub SayHello(ByVal pName As String) MsgBox ("Hello " & pName) End Sub Function Add(pN1 As Integer, pN2 As Integer) As Integer Add = pN1 + pN2 End Function
|
|
24. Access VBA Self Keyword (Me) |
|
Same as VB. The Me keyword is a built-in variable that refers to the class where the code is executing. For example, you can pass Me from one module to another.
Private Sub Command10_Click() MsgBox Me.Name 'Displays name of form (Form1 in this case).
End Sub
|
Topic: Commands
|
25. Access VBA Left of String (Left) |
|
Access VBA Left of String
Dim LeftString As String LeftString = Left("Prestwood", 3) MsgBox LeftString
|
Topic: OOP
|
26. Access VBA Constructors (Class_Initialize) |
|
When an object instance is created from a class, Access VBA calls a special parameter-less sub named Class_Initialize. Since you cannot specify parameters for this sub, you also cannot overload it.
When a class is destroyed, Access VBA calls a special sub called Class_Terminate.
|
|
27. Access VBA Destructor |
|
When an object instance is destroyed, Access VBA calls a special parameter-less sub named Class_Terminate. For example, when the variable falls out of scope. Since you cannot specify parameters for this sub, you also cannot overload it.
When an object instance is created from a class, Access VBA calls a special sub called Class_Initialize.
|
|
28. Access VBA Interfaces |
|
Same as in VB6. Access VBA has limited support for interfaces. You can create an interface of abstract methods and properties and then implement them in one or more descendant classes. It's a single level implementation though (you cannot inherit beyond that). The parent interface class is a pure abstract class (all methods and properites are abstract, you cannot implement any of them in the parent class).
In the single level descendant class, you have to implement all methods and properties and you cannot add any. Your first line of code is Implements InterfaceName.
|
|
29. Access VBA Member Method (Sub, Function) |
|
Access VBA uses the keywords sub and function. A sub does not return a value and a function does. Many programmers like to use the optional call keyword when calling a sub to indicate the call is to a procedure.
|
|
|