Access VBA: Storing Data in Variables and Constants

A variable is a chunk of information
A variable has a name and a datatype
A variable has a value

Use a  Dim statement for variables (two lines)

Dim name As type
name = value

A constant is a value that does not change.  There are lots of inbuilt constants in VBA, but to create your own, use the following syntax:

Const name As type = value

STORING DATA IN ARRAYS
An array is a collection of variables or constants.   An array allows you to create multiple variables or constants in one go.  To create an array you add a dimension value which tells VBA how many variables exist in the array:

Dim name(dimension)As Type

Dim mytest(5) As String

Creates the following variables

mytest(1)
mytest(2)
mytest(3)
mytest(4)
mytest(5)

DATA STORED AT THE MODULE LEVEL
Date stored in variables, constants and arrays, has both a lifetime and a scope.  Both these things are defined by where the variable is defined within a module.

To assign values to variables, arrays and constants for a whole module, you place the data at the top of the module under
Option Compare Database

These variables are public as they are available to all procedures in the module

DATA STORED AT A PROCEDURE LEVEL
Data stored within a procedure, is private, in that it is only available to that procedure.

NAMING CONVENTIONS FOR VARIABLES
Name variables with a three letter prefix that indicates the type of data variable:

Byt      byte
Cur      Currency
Dtm     Date/Time
Dbl      Double
Int       Integer
Lng      Long
Sng      Single
Bln      Boolean
str        String
var       Variant