I.T. Discussion Community!
-Collapse +Expand
Access
Search Access Group:

Advanced
-Collapse +Expand Access To/From
To/FromCODEGuides
-Collapse +Expand Access Store
PRESTWOODSTORE

Prestwood eMagazine

November Edition
Subscribe now! It's Free!
Enter your email:

   ► KBAccess Knowledge Base  Print This    Code Snippet DB All Groups  

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.

Contribute a Code Snippet
Expand All

29 Microsoft Access Code Snippets

Group: Microsoft Access


Topic: Access & VBA

-Collapse +Expand 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
Posted By Mike Prestwood, Post #102132, KB Topic: Access & VBA
-Collapse +Expand 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
Posted By Mike Prestwood, Post #102038, KB Topic: Access & VBA
-Collapse +Expand 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)
Posted By Mike Prestwood, Post #101624, KB Topic: Access & VBA



Topic: Language Basics

-Collapse +Expand 4. Access VBA Assignment (=)
 

Access uses = for it's assignment operator.

Dim Age As Integer
Dim FullName As String
   
FullName = "Randy Spitz"
Age = 38
Posted By Mike Prestwood, Post #101582, KB Topic: Language Basics
-Collapse +Expand 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")
Posted By Mike Prestwood, Post #101538, KB Topic: Language Basics
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101492, KB Topic: Language Basics
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101499, KB Topic: Language Basics
-Collapse +Expand 8. Access VBA Comparison Operators (=, <>)
 

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
Posted By Mike Prestwood, Post #101584, KB Topic: Language Basics
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101707, KB Topic: Language Basics
-Collapse +Expand 10. Access VBA Deployment Overview
 

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

Posted By Mike Prestwood, Post #101948, KB Topic: Language Basics
-Collapse +Expand 11. Access VBA Development Tools
 

Languages Focus: Development Tools

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

Access VBA Development Tools

Microsoft 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).

Posted By Mike Prestwood, Post #101544, KB Topic: Language Basics
-Collapse +Expand 12. Access VBA End of Statement (Return)
 

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.

Access VBA End of Statement

A 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";
Posted By Mike Prestwood, Post #101688, KB Topic: Language Basics
-Collapse +Expand 13. Access VBA File Extensions (.MDB)
 
  • .MDB - Access Database
  • .MDE - Protected Access Database
Posted By Mike Prestwood, Post #100913, KB Topic: Language Basics
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101581, KB Topic: Language Basics
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101525, KB Topic: Language Basics
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101890, KB Topic: Language Basics
-Collapse +Expand 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.

Posted By Mike Prestwood, Post #101714, KB Topic: Language Basics
-Collapse +Expand 18. Access VBA Report Tools Overview (Built-In)
 

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

Posted By Mike Prestwood, Post #101648, KB Topic: Language Basics
-Collapse +Expand 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)
Posted By Mike Prestwood, Post #101588, KB Topic: Language Basics
-Collapse +Expand 20. Access VBA Unary Operators
 

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

Posted By Mike Prestwood, Post #101553, KB Topic: Language Basics
-Collapse +Expand 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.
Posted By Mike Prestwood, Post #101571, KB Topic: Language Basics



Topic: Language Details

-Collapse +Expand 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"))
Posted By Mike Prestwood, Post #101579, KB Topic: Language Details
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101596, KB Topic: Language Details
-Collapse +Expand 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
Posted By Mike Prestwood, Post #101952, KB Topic: Language Details



Topic: Commands

-Collapse +Expand 25. Access VBA Left of String (Left)
 

Access VBA Left of String

Dim LeftString As String
LeftString = Left("Prestwood", 3)
MsgBox LeftString
Posted By Mike Prestwood, Post #101606, KB Topic: Commands



Topic: OOP

-Collapse +Expand 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.

Posted By Mike Prestwood, Post #101827, KB Topic: OOP
-Collapse +Expand 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.

Posted By Mike Prestwood, Post #101832, KB Topic: OOP
-Collapse +Expand 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.

Posted By Mike Prestwood, Post #101842, KB Topic: OOP
-Collapse +Expand 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.

Posted By Mike Prestwood, Post #101750, KB Topic: OOP

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech