LEARN MS-ACCESS TIPS AND TRICKS: Fields

Introduction.

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS, has been selected by the https://blog.feedspot.com panelist as one of the Top 5 Microsoft Access Blogs on the Web and awarded the Badge given below.

You can find some of the top-ranked Blogs on a variety of subjects, RSS Feeds, YouTube Channels, top News Websites, and others. Subscribe to Blogs/News Feeds, or topics of any other area of interest, and get regular updates from www.blog.feedspot.com to your Inbox, as and when they happen, by providing your email address.

Returning to our VBA lessons, last week we briefly discussed passing arrays as parameters to a Function using the ByRef method. This allowed us to work directly with the original array inside the called function and sort its values in descending order.

In our example, we used an array containing values for only five elements, but in practice, an array can hold many rows and columns of data.

The Re-Dimension (ReDim) Statement.

An array can be re-dimensioned multiple times during program execution to increase or decrease the number of rows if its size cannot be determined in advance. In such cases, you should omit the element count in the initial Dim statement.

Example:

'Cannot Re-dimension pre-defined Arrays
.
Dim Products(1 to 5) as String
.
or
.
Dim Products(5) as String'The number of elements are predefined

'Re-dimension this Array later for required  
'Number of elements, not known in advance. 
.
Dim Products() as String
.
'Re-Dimension the Array for required number of elements
'Remember the array index numbers will be 0 to 4, total 5 elements
ReDim Products(5) As String
'
'OR
'In this case Array Index Number Range 1 to 5
ReDim Product(1 to 5) As String

'later on in the program
'all the values assigned to first 5 elements will be lost.
.
ReDim Products(7) As String 
.
or
.
ReDim Products(Ubound(Products)+2) As String
.
'To preserve the values already assigned to first 5 elements
.
ReDim Preserve Products(7) As String

Note: An important point to note is that any re-dimensioning of the array should be done in the calling procedure itself—before passing it to the called function if a size change is required. Although arrays are passed by reference and the called function can work directly with the passed variable, it cannot re-dimension the array to increase or decrease its number of elements from within the called function.

Two-Dimensional Array as Function Argument.

Now that we are aware of the limitations of passing arrays to a called function, let’s try passing a two-dimensional array containing Sales data to a function and printing its values in the Debug Window. Each record in the Sales data array will contain the following values:

Product Name - Text
Quantity - Integer
Unit Price – Double
Total Value - Double (will be calculated in the called function)

The sales records contain fields with different data types. Normally, to pass these values individually to a called function, we would need four separate array variables with different data types — for example:

String for Product Name
Integer for Quantity
Double for Unit Price
Double for Total Price

Each field’s values would be stored in its own array variable and passed separately to the function.

However, we will do this differently. Instead of using four separate arrays, we will use a single variable: a two-dimensional array of the Variant data type, with four rows (each row representing one sales record) and four columns (each column representing a field). This way, we can pass all four fields of each record together as one variable to the called function.

The Variant Data Type.

We are not storing the column names anywhere within the array. It is simply assumed that:

The first column holds the Product Name
The second column holds the Quantity
The third column holds the Unit Price
The fourth column holds the Total Price

Since we are using a Variant data type for the array, each individual cell in the array can hold different data types. A Variant variable automatically adapts its data type to match the type of value assigned to it. This flexibility allows us to store text, integers, and floating-point numbers together in the same array.

Before writing the complete function, let’s first look at the Dim statement used to define the two-dimensional array, and how the sales values are assigned to each element of the array.

' variant Variable can hold different data types in each element 
Dim Product(4,4) as Variant 
'
Product(0, 0) = "Hard Disk": Product(0, 1) = 5: Product(0, 2) = 125.5: Product(0, 3) = 0

Product(1, 0) = "Keyboard": Product(1, 1) = 2: Product(1, 2) = 25.25: Product(1, 3) = 0

Product(2, 0) = "Mouse": Product(2, 1) = 3: Product(2, 2) = 13.75: Product(2, 3) = 0

Product(3, 0) = "DVD Writer": Product(3, 1) = 10: Product(3, 2) = 30: Product(3, 3) = 0

In the above example, we have only four records (rows) in our table, and each record contains four fields (columns). Every cell in this two-dimensional array is identified by a pair of numbers—the row index and the column index—separated by a comma. The index number on the left represents the row index, and the number on the right represents the column index. Since our array has 4 rows and 4 columns, both index ranges go from 0 to 3.

Here is how the columns are arranged:

Column 0: Product Name
Column 1: Quantity
Column 2: Unit Price
Column 3: Total Value (to be calculated and assigned later)

We can pass the entire array to a function as a ByRef parameter, allowing the function to work directly with the original data.

If you are new to two-dimensional arrays, it can feel a bit confusing at first to understand how the values are arranged and how to refer to each cell. This becomes even trickier when performing calculations across multiple cells within the same row.

Fortunately, there is a better way to handle this complexity—by using User-Defined Variables. Yes, you can actually define your own variable type, in addition to the built-in types provided by VBA.

We will explore this concept in detail next week. Once you get familiar with it, you’ll find it much easier and more intuitive than juggling rows and columns. The best part is that this method scales effortlessly—you can work with 5 rows, 500 rows, or even 5000 rows using the same statements in your function.

Create the Product List Data.

Public Function ProdList()
Dim Products(4, 4) As Variant
Dim j As Integer, k As Integer, stridx As String
' 0 = Description
' 1 = Quantity
' 2 = Unit Price
' 3 = Total Price to be calculated
'Array elements index numbers are 0 to 3
For j = 0 To 3
 For k = 0 To 3
    stridx = "(" & j & "," & k & ")"
    Select Case k
        Case 0
          Products(j, k) = InputBox("Product Name" & stridx)
        Case 1
          Products(j, k) = InputBox("Quantity" & stridx)
        Case 2
          Products(j, k) = InputBox("Unit Price" & stridx)
        Case 3
          Products(j, k) = 0 'total value will be calculated
    End Select
    Next k
Next j

Call ProdPrint(Products)

End Function

VBA Code Line by Line

We have defined the Products variable as a Variant data type with 4 rows and 4 columns, so it can hold values of different data types in each cell.

In the next line, we declare three more variables:

j and k as control variables for the For…Next loops
strIdx as a String variable for building text to display the index numbers of each cell in the InputBox() prompt.

We then set up two nested For … Next loops to control the index numbers of the rows and columns. The outer loop (j) controls the row index, while the inner loop (k) controls the column index.

Inside the inner loop, we use a Select Case…End Select structure to determine which field is being processed based on the current value of k:

When k = 0, the InputBox() function prompts for the Product Name and assigns it to Products(j, 0).
When k = 1, it prompts for the Quantity and assigns the value to Products(j, 1).
When k = 2, it prompts for the Unit Price and assigns it to Products(j, 2).
When k = 3, it initializes Products(j, 3) = 0 (this will later hold the calculated Total Price).

The outer loop repeats this process four times (for j = 0 to 3), and for each row, the inner loop runs four times (for k = 0 to 3), collecting values from the user for each cell in the current row.

The Output Function ProdPrint().

When the control exits the loop, the ProductPrint() function is called, passing the Products variable as a parameter.

Public Function ProdPrint(List As Variant)
Dim j As Integer, k As Integer

'Ubound() function will get the
'total rows in the array - first value in the Dim statement
For j = 0 To UBound(List, 1) - 1
      List(j, 3) = List(j, 1) * List(j, 2)
    For k = 0 To UBound(List, 2) - 1 'get second value in Dim statement
        Debug.Print List(j, k),
    Next k: Debug.Print
Next j

End Function

The ProductPrint() function receives the memory reference (address) of the Products array through the ByRef method. If the ByVal or ByRef keyword is not specified explicitly before the parameter variable, it assumes ByRef as the default.

As in the earlier program, two integer variables, j and k, are declared as control variables for the outer and inner For…Next loops. These loops are required to navigate the array using its row and column index numbers. The loop starts at 0, and to determine the end value dynamically, we use the Ubound() (Upper Boundary) function on the array dimension. In the previous example, we directly used the values 0 to 3, but here we use Ubound() to make the program flexible. This ensures that if the array size changes later through ReDim statements, the loop still calculates the correct number of rows and columns automatically.

Usage UBound() Function to get Two-Dimensional Index Numbers.

UBound(Array,1)

The Ubound(List, 1) function returns the number of rows in the array, which is 4. However, array row indexes start from 0 in memory, so the valid index numbers are 0 to 3. The second argument (1) in the Ubound() function specifies that we want the upper bound of the first dimension (rows) of the array. Because the row index starts at 0, we subtract 1 from the total number of rows (4 − 1) when using these indexes in a For…Next loop.

UBound(Array,2)

The UBound(List, 2) function returns the number of columns in the array. The second parameter is optional—if omitted, UBound will return only the upper bound of the first dimension (rows). For single-dimensional arrays, this second parameter is never used.

The statement immediately following the first For…Next loop — List(j, 3) = List(j, 1) * List(j, 2) — calculates the total price of each item and stores it in the rightmost cell. These updated values are then printed in the Debug window during the next For…Next loop, which outputs the complete sales record for each item.

Controlling the Print-Head

Placing a comma at the end of a 'Debug.Print' statement aligns the next item in the 14th column on the same line, following the previously printed item.

An empty 'Debug.Print' statement, placed immediately after the inner Next statement (without a trailing comma), moves the print cursor back to the first column of the next line. This ensures the output for the next sales record begins at the correct position.

If a semicolon (;) is placed at the end of a 'Debug.Print' statement, the print cursor advances to the very next character position, without leaving any space between the printed items.

Next week, we will explore user-defined variables that can hold mixed data types. With them, we can assign meaningful names to each element of a collection instead of relying on array positions as we did in the examples above. This will make it much easier—and more intuitive—than trying to memorize each array element’s logical meaning based solely on its index.

Public Function PrimaryKey_Example1(ByVal EmpCode As Integer) Dim db As Database, rst As Recordset Set db = CurrentDb Set rst = db.OpenRecordset("Employees", dbOpenTable) rst.Index = "PrimaryKey" 'activate the Index on Employee Code rst.Seek "=", EmpCode 'we are looking for the record of Employee Code provided 'Let us test whether the search for Employee Code was successfull or not If Not rst.NoMatch Then MsgBox "EC: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name] Else MsgBox "EC: " & EmpCode & " Not found!" End If Set rst = Nothing Set db = Nothing End Function

Public Function PrimaryKey_Example2(ByVal strFirstName As String, ByVal strLastName As String) Dim db As Database, rst As Recordset Set db = CurrentDb Set rst = db.OpenRecordset("Employees", dbOpenTable) rst.Index = "MyIndex" 'activate the Index 'Search for the Employee record rst.Seek "=", strFirstName, strLastName 'Let us test whether the search for the Employee was successfull or not If Not rst.NoMatch Then MsgBox "Employee: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name] Else MsgBox "Employee: " & strFirstName & " " & strLastName & " Not found!" End If Set rst = Nothing Set db = Nothing End Function