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

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

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)

Access uses = for it's assignment operator.

Dim Age As Integer

Dim FullName As String
    
FullName = "Randy Spitz"

Age = 38

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")

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

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

Save as VB Classic.

//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

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

You can deploy your Microsoft Access application either with the full version of Access or with the Access Runtime (see Deploying Applications Using the Access Runtime).

Languages Focus: Development Tools

Primary development tool(s) used to develop and debug code.

Languages Focus: End of Statement

In 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.

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";

• .MDB - Access Database
• .MDE - Protected Access Database

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

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

Same as VB. Access VBA logical operators:

'Given expressions a, b, c, and d:

If Not (a and b) and (c or d) Then

  'Do something.

End If

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.

Microsoft Access offers a built-in reporting tool that will suffice for most desktop database applications.

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)

An operation with only one operand (a single input) such as + and -.

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.

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"))

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

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

Access VBA Left of String

Dim LeftString As String

LeftString = Left("Prestwood", 3)

MsgBox LeftString

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.

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.

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.

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.