November 2018 ~ LEARN MS-ACCESS TIPS AND TRICKS

MS-Access Recordset and Class Module

a.p.r. pillaiNovember 25, 2018Class Module, Data Type, Field Type, Recordset No comments

Introduction.

In this section, we will create a Class Module designed for data processing tasks. An DAO.Recordset object will be passed to the custom class object. Since we are passing an object to our custom class, we need to implement a pair of Property Set and Property Get procedures to assign the object to the class and to retrieve the object or its property values when required.

We have a small Table: Table 1, with a few records on it. Here is the image of Table 1.

Right-click to open Large Image in New Tab/Window.

The table above has only four fields: Description, Quantity, Unit Price, and Total Price. The Total Price field is empty.

One of the tasks of our Class Module is updating the TotalPrice field with the product of Qty * UnitPrice.
The Class Module includes a subroutine that sorts the data based on a user-specified field and outputs the sorted listing to the Debug Window.
Another subroutine creates a copy of the Table with a new name, after sorting the data based on the column number provided as a parameter.

ClsRecUpdate Class Module.

Open your Access Database and open the VBA Window.
Insert a Class Module.
Change the Name Property Value to ClsRecUpdate.

Copy and Paste the following Code into the Class Module and save the Module:

Option Compare Database
Option Explicit

Private rstB As DAO.Recordset

Public Property Get REC() As DAO.Recordset
   Set REC = rstB
End Property

Public Property Set REC(ByRef oNewValue As DAO.Recordset)
If Not oNewValue Is Nothing Then
   Set rstB = oNewValue
End If
End Property

Public Sub Update(ByVal Source1Col As Integer, ByVal Source2Col As Integer, ByVal updtcol As Integer)
'Updates a Column with the product of two other columns
Dim col As Integer

col = rstB.Fields.Count

'Validate Column Parameters
If Source1Col > col Or Source2Col > col Or updtcol > col Then
    MsgBox "One or more Column Number(s) out of bound!", vbExclamation, "Update()"
    Exit Sub
End If

'Update Field
On Error GoTo Update_Err
rstB.MoveFirst
Do While Not rstB.EOF
   rstB.Edit
     With rstB
      .Fields(updtcol).Value = .Fields(Source1Col).Value * .Fields(Source2Col).Value
      .Update
      .MoveNext
     End With
Loop

Update_Exit:
rstB.MoveFirst
Exit Sub

Update_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "Update()"
Resume Update_Exit
End Sub

Public Sub DataSort(ByVal intCol As Integer)
Dim cols As Long, colType
Dim colnames() As String
Dim k As Long, colmLimit As Integer
Dim strTable As String, strSortCol As String
Dim strSQL As String
Dim db As Database, rst2 As DAO.Recordset

On Error GoTo DataSort_Err

cols = rstB.Fields.Count - 1
strTable = rstB.Name
strSortCol = rstB.Fields(intCol).Name

'Validate Sort Column Data Type
colType = rstB.Fields(intCol).Type
Select Case colType
    Case 3 To 7, 10
        strSQL = "SELECT " & strTable & ".* FROM " & strTable & " ORDER BY " & strTable & ".[" & strSortCol & "];"
        Debug.Print "Sorted on " & rstB.Fields(intCol).Name & " Ascending Order"

    Case Else
        strSQL = "SELECT " & strTable & ".* FROM " & strTable & ";"

        Debug.Print "// SORT: COLUMN: <<" & strSortCol & " Data Type Invalid>> Valid Type: String,Number & Currency //"
        Debug.Print "Data Output in Unsorted Order"
End Select

Set db = CurrentDb
Set rst2 = db.OpenRecordset(strSQL)

ReDim colnames(0 To cols) As String

'Save Field Names in Array to Print Heading
For k = 0 To cols
   colnames(k) = rst2.Fields(k).Name
Next

'Print Section
Debug.Print String(52, "-")

'Print Column Names as heading
If cols > 4 Then
   colmLimit = 4
Else
   colmLimit = cols
End If
For k = 0 To colmLimit
    Debug.Print colnames(k),
Next: Debug.Print
Debug.Print String(52, "-")

'Print records in Debug window
rst2.MoveFirst
Do While Not rst2.EOF
  For k = 0 To colmLimit 'Listing limited to 5 columns only
     Debug.Print rst2.Fields(k),
  Next k: Debug.Print
rst2.MoveNext
Loop

rst2.Close
Set rst2 = Nothing
Set db = Nothing

DataSort_Exit:
Exit Sub

DataSort_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "DataSort()"
Resume DataSort_Exit

End Sub

Public Sub TblCreate(Optional SortCol As Integer = 0)
Dim dba As DAO.Database, tmp() As Variant
Dim tbldef As DAO.TableDef
Dim fld As DAO.Field, idx As DAO.Index
Dim rst2 As DAO.Recordset, i As Integer, fldcount As Integer
Dim strTable As String, rows As Long, cols As Long

On Error Resume Next

strTable = rstB.Name & "_2"
Set dba = CurrentDb

On Error Resume Next
TryAgain:
Set rst2 = dba.OpenRecordset(strTable)
If Err > 0 Then
  Set tbldef = dba.CreateTableDef(strTable)
  Resume Continue
Else
  rst2.Close
  dba.TableDefs.Delete strTable
  dba.TableDefs.Refresh
  GoTo TryAgain
End If
Continue:
On Error GoTo TblCreate_Err

fldcount = rstB.Fields.Count - 1
ReDim tmp(0 To fldcount, 0 To 1) As Variant

'Save Source File Field Names and Data Type
For i = 0 To fldcount
    tmp(i, 0) = rstB.Fields(i).Name: tmp(i, 1) = rstB.Fields(i).Type
Next
'Create Fields and Index for new table
For i = 0 To fldcount
   tbldef.Fields.Append tbldef.CreateField(tmp(i, 0), tmp(i, 1))
Next
'Create index to sort data
Set idx = tbldef.CreateIndex("NewIndex")
With idx
   .Fields.Append .CreateField(tmp(SortCol, 0))
End With
'Add Tabledef and index to database
tbldef.Indexes.Append idx
dba.TableDefs.Append tbldef
dba.TableDefs.Refresh

'Add records to the new table
Set rst2 = dba.OpenRecordset(strTable, dbOpenTable)
rstB.MoveFirst 'reset to the first record
Do While Not rstB.EOF
   rst2.AddNew 'create record in new table
    For i = 0 To fldcount
        rst2.Fields(i).Value = rstB.Fields(i).Value
    Next
   rst2.Update
rstB.MoveNext 'move to next record
Loop
rstB.MoveFirst 'reset record pointer to the first record
rst2.Close

Set rst2 = Nothing
Set tbldef = Nothing
Set dba = Nothing

MsgBox "Sorted Data Saved in " & strTable

TblCreate_Exit:
Exit Sub

TblCreate_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "TblCreate()"
Resume TblCreate_Exit

End Sub

The 'rstB' Property is declared as a DAO.Recordset Object.

Through the Set Property Procedure, a Recordset object can be passed to the ClsRecUpdate Class Object.

The Update() Subroutine accepts three-column numbers (0-based column numbers) as parameters to calculate and update the third parameter column with the product of the first column and * second column.

The DataSort() subroutine sorts the records in ascending order based on the Column Number passed as a parameter.

The Sorting Column data type must be either Number, Currency, or String. Other data types are ignored. The Recordset column numbers are 0-based, which means the first column number is 0, the second column is 1, and so on.

A listing of the records will be displayed in the Debug Window. The output will be limited to the first five fields; if the record source contains more than five fields, the remaining fields will be ignored.

The TblCreate() Subroutine sorts the data based on the column number provided as a parameter and creates a new table with a modified name. The parameter is optional; if no column number is specified, the data will be sorted by the first column (provided its data type is valid). The new table will retain the original table name with “_2” appended to it. For example, if the source table is namedTable1 The newly created table will be named Table1_2.

The Test Program for ClsUpdate.

Let us test the ClsRecUpdate Class Object with a small Program.

The test program code is given below:

Public Sub DataProcess()
Dim db As DAO.Database
Dim rstA As DAO.Recordset

Dim R_Set As ClsRecUpdate
Set R_Set = New ClsRecUpdate

Set db = CurrentDb
Set rstA = db.OpenRecordset("Table1", dbOpenTable)

'send Recordset Object to Class Object
Set R_Set.REC = rstA

'Update Total Price Field
Call R_Set.Update(1, 2, 3) 'col3=col1 * col2

'Sort Ascending Order on UnitPrice column & Print in Debug Window
Call R_Set.DataSort(2)

'Create New Table Sorted on UnitPrice in Ascending Order
Call R_Set.TblCreate(2) 
Set rstA = Nothing
Set db = Nothing
xyz:
End Sub

You may pass any Recordset to test the Class Object.

You can specify any column numbers when updating a particular column; they do not need to be consecutive. The third column number parameter identifies the target column to be updated. The values from the first column parameter are multiplied by the values from the second column parameter, and the resulting value is written to the target column. You may modify the Class Module code to perform any other operation on the table as needed.

List of All the Links on this Topic.

Base Class and Derived Object Variants

a.p.r. pillaiNovember 14, 2018Class Module, Methods, Objects, Property, VBA No comments

Introduction.

Last week, we explored an example where a Base Class Object was passed through a Set Property Procedure, allowing it to become part of another object in memory. The passed object essentially became an extension or child object of the main object. In that earlier program, we passed the child object to the target object during the instantiation phase of our test program and then assigned values to the child object’s properties later in the code.

In the next example, we will take a slightly different approach.

For those who would like to go through the earlier Articles on the MS-Access Class Module, the links are given below:

This time, we will open both objects—ClsArea (the base class) and ClsVolume2 (the target class)—separately in our test program. We will assign values to the ClsArea base class properties before passing it to the ClsVolume2 target class object. Remember, the ClsVolume2 class has only one property, p_Height, and its Volume() method requires the Length and Width values from the base class ClsArea to calculate the volume.

Copy and paste the following sample Test Code into a Standard Module.

The SetNewVol2_2 Procedure.
```
Public Sub SetNewVol2_2()
'Method 2/2
Dim CA As ClsArea
Dim Vol As ClsVolume2

Set CA = New ClsArea
Set Vol = New ClsVolume2

CA.strDesc = "Bed Room"
CA.dblLength = 90
CA.dblWidth = 10
Stop


'Here ClsArea class Object CA is passed to the 
‘Property procedure Set CArea of ClsVolume2 object Vol
Set Vol.CArea = CA 'Pass ClsArea obj to ClsVolume2

Vol.dblHeight = 10 'assign height to ClsVolume2


Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
With Vol.CArea
  Debug.Print .strDesc, .dblLength, .dblWidth, .Area(), Vol.dblHeight, Vol.Volume()
End With
Stop

Set CA = Nothing
Set Vol = Nothing

End Sub
```
VBA Code Review.
In the first Dim statement, CA is declared as a ClsArea object and Vol as a ClsVolume2 object. The next two statements instantiate these objects in memory.
The following three statements assign values to the properties of the ClsArea object.
A Stop statement is then used to pause code execution, allowing us to inspect the property values of the object in the Locals window.
Next, the statement Set Vol.CArea = CA assigns the ClsArea object (CA) as a child object of the ClsVolume2 object (Vol).
After that, the dblHeight property of the ClsVolume2 object is assigned the value 10.
The subsequent statements, placed before the next Stop statement, print the property values from memory to the Debug window.

Finally, the last two Set statements release both objects from memory before the program ends.
Display the Locals Window.
Inspecting the Locals Window
1. Open the Locals Window
  From the View menu in the VBA Editor, select Locals Window.
2. Run the Code
  - Click anywhere in the middle of the code window.
  - Press F5 to run the program until it pauses at the Stop statement.
  - Alternatively, press F8 to run the code step by step, which lets you observe the changes in the Locals Window at each step.
3. Expand the Objects
  Click the [+] symbol next to the object names in the Locals Window to expand and display their properties and current values.
4. Observe Object References
  - Check the CArea and p_Area object references under the Vol object.
  - At this point, their values will show as Nothing because we have not yet passed the CA object to the Vol object.
5. Continue Running the Code
  - After reviewing the Locals Window, run the code until it pauses at the next Stop statement.
  - Now, the CArea Set Property procedure assigns the p_Area object reference to the ClsArea object, linking it into the ClsVolume2 object.
Next, we will try another variation of this example using the same two classes — ClsArea and ClsVolume2 — to demonstrate a slightly different approach.

New Class Module ClsVolume3.

1. Insert a new Class Module and change its name Property Value to ClsVolume3.

2. Copy and Paste the following VBA Code into the ClsVolume3 Class Module:

Option Compare Database
Option Explicit
'Method three 
Private p_Height As Double
Public p_Area As ClsArea

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = p_Area.dblLength * p_Area.dblWidth * Me.dblHeight
End Function

Private Sub Class_Initialize()
    Set p_Area = New ClsArea
End Sub

Private Sub Class_Terminate()
    Set p_Area = Nothing
End Sub

In the code, p_Height is declared as private property, while p_Area is declared as a public ClsArea object within the ClsVolume3 class. This means p_Area appears as a property of the ClsVolume3 class, with its own accessible properties and methods for direct Get/Let operations in the user program (from a standard module). Although the ClsArea object is exposed as a public property of ClsVolume3, its internal properties and methods remain encapsulated within the ClsArea class itself.

It is important to ensure that the ClsArea class is fully developed and free of errors before using it inside other classes.

The Class_Initialize() and Class_Terminate() routines handle the lifecycle of the embedded object: The ClsArea object is instantiated in Class_Initialize() when a ClsVolume3 object is created, and released from memory in Class_Terminate() When the user program ends.

The Testing Program.

The sample Test VBA Code is given below.

Copy and paste the code into the Standard Module.

Public Sub SNewVol3()
'Here ClsArea class is declared as a Public Property of ClsVolume3
Dim volm As ClsVolume3

Set volm = New ClsVolume3

volm.p_Area.strDesc = "Bed Room"
volm.p_Area.dblLength = 15 'assign length
volm.p_Area.dblWidth = 10 'assign width in clsArea
volm.dblHeight = 10 'assign height to ClsVolume2

Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
With volm.p_Area
   Debug.Print .strDesc, .dblLength, .dblWidth, .Area, volm.dblHeight, volm.Volume
End With
Set volm = Nothing

End Sub

Display the Locals Window (View -> Locals Window), if it is not already open.

Click somewhere in the middle of the code and press F8 to execute the VBA Code one line at a time and watch the Local Window to track what happens at each step.

All the above variants of the ClsVolume Class have been written with less Code, except the first example of the ClsVolume Class.

Working with the Recordset Object.

Next week, we will work with a built-in Object 'DAO.Recordset' and build a Class Module to:

Calculate and update a Field,
Sort the Data,
Print the sorted data in the Debug Window,
And create a Clone of the Table with sorted data.

That is a lot of action next week.

List of All the Links on this Topic.

Earlier Post Link References:

VBA Base Class and Derived Object-2

a.p.r. pillaiNovember 08, 2018Class Module, Object Reference, Objects, Property, VBA 3 comments

Introduction.

Last week, we created a derived Class, 'lsVolume', using ClsArea as the base class. In that approach, we added property procedures in the derived class to expose the base class’s properties and functions to the user programs. This method, however, requires repeating all the base class property procedures in the derived class.

In this section, we will explore how to create the same derived ClsVolume class without duplicating the property procedures of the ClsArea base class.

So far, we have learned about Get and Let property procedures in classes. There is also a third type: the Set Property Procedure, which is used to directly assign an object to a class object of the same type.

Before proceeding, you may want to revisit the earlier pages on this topic if you haven’t already. Links are provided below:

ClsVolume Class, the Makeover.

We shall create a different variant of the same ClsVolume Class Module we created last week, using ClsArea as Base Class, with a different approach, and with less Code.

Create a new Class Module and change its Name Property Value to ClsVolume2.

Copy and Paste the following Code into the Class Module ClsVolume2 and Save the Module:

Option Compare Database
Option Explicit
'Method two-1

Private p_Height As Double
Private p_Area As ClsArea

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = p_Area.dblLength * p_Area.dblWidth * p_Height
End Function

The new Get and Set Property Procedure for the ClsArea Object.

Public Property Get CArea() As ClsArea
   Set CArea = p_Area
End Property

Public Property Set CArea(ByRef AreaValue As ClsArea)
  Set p_Area = AreaValue
End Property

From the Debug menu, select Compile [Project Name] to compile all the VBA code in your database and ensure it is error-free. If any errors are found in other VBA programs, locate and correct them, then recompile the project.

While unresolved errors will not prevent you from assigning or retrieving values from object properties, the VBA IntelliSense—which displays a list of an object’s properties and functions—will not work properly until the project compiles successfully.

Seeing the property list appear in IntelliSense is an invaluable aid during coding, especially while learning and experimenting with class objects.

**Get / Set instead of the Get / Let Property Procedure.**

In this version of the ClsVolume class, we have omitted all the property procedures ClsArea that were present in the previous version. Instead, we use Get and Set property procedures rather than the traditional Get/Let pair.

Take a look at the declaration: the private member p_Area is declared as a ClsArea class object. Normally, when an object is declared this way, we would create an instance of it in the Class_Initialize() procedure.

However, in this approach, we have not instantiated it within the class. The plan is to create and populate the ClsArea object in the user program, set its properties with appropriate values, and then pass it to the ClsVolume2 class. The class will then use these values during the final calculation phase.

Take note of the Set CArea() procedure. Its ByRef parameter, AreaValue, is declared as a ClsArea object. When an ClsArea instance is passed to this property procedure, the object variable AreaValue receives it and assigns it to the private p_Area property of the ClsVolume2 object.

This mechanism allows the ClsVolume2 class to use an externally created and populated ClsArea object without having to instantiate it internally, maintaining flexibility and reusability.

The Get CArea() Property procedure returns the Object to the calling program.

In our earlier programs, we wrote property procedures for individual elements of an object, such as Length, Width, and Height—to assign or retrieve values. In this version, the difference is that we are passing an entire object as a parameter to a Set property procedure.

To access a property of this passed object—for example, dblLength—we use the syntax CArea.dblLength. Here, the Get/Set property procedure name CArea essentially becomes a child object of the main object when declared in the main program. Its individual properties can then be accessed directly using the object address, such as:


Vol.CArea.dblLength

This approach allows the main object to interact with the entire child object and its properties as a single unit, simplifying property management and enhancing reusability.

A Test Program in Standard Module.

We will now create a small test program in a Standard Module to verify the functionality of our newly derived class object. ClsVolume2.

Insert a new Standard Module into your project.
Copy and paste the following code into the module.
Save the module before running the program.

Public Sub SetNewVol2_1()
'Method 1/2
Dim Vol As New ClsVolume2

'ClsArea Object instantiated and passed to the
'Property Procedure Set CArea in ClsVolume2.

Set Vol.CArea = New ClsArea 'declare and instantiate the object in one statement

Stop

Vol.CArea.strDesc = "Bed Room"
Vol.CArea.dblLength = 90
Vol.CArea.dblWidth = 10

Vol.dblHeight = 10 'assign height to ClsVolume2

Stop

Debug.Print "Description", "Length", "Width", "Area", "Height", "Volume"
Debug.Print Vol.CArea.strDesc, Vol.CArea.dblLength, Vol.CArea.dblWidth, Vol.CArea.Area, Vol.dblHeight, Vol.Volume

Set Vol.CArea = Nothing
Set Vol = Nothing

End Sub

Code Review Line by Line.

Let’s quickly review the VBA code above.

The first line instantiates the ClsVolume2 class with the object name Vol.
After the comment lines, the Set Vol.CArea statement calls the property procedure and passes the newly instantiated ClsArea object as its parameter.

I included a Stop statement on the next line to pause the program so you can observe how the object is assigned to the CArea property. We’ll explore how to inspect this in memory shortly.

The following four lines assign values to the ClsArea object properties (strDesc, dblLength, dblWidth) and to the dblHeight property of the ClsVolume2 object.
The next Stop statement pauses the program again, allowing you to inspect how these values are stored in memory.
The subsequent line prints the headings in the Debug Window for clarity.
Finally, the last line prints the values of the object properties retrieved from memory, displaying them in the Debug Window.

Run the Code to the Next Stop Statement

Let’s run the code and inspect the memory to see what happens at each stage where the Stop statements are placed.
1. Click anywhere inside the code and press F5 to run the program. The execution will pause at the first Stop statement.
2. From the View menu, select Locals Window. This opens a window below the code editor that displays the ClsArea and ClsVolume2 objects, along with their properties and member procedures, as they are stored in memory.
3. Observe the structure of the objects and how the property values are held. A sample image of the Locals Window is shown below for reference.
The Locals Window View.

Right-click to open Large Image in New Tab/Window.

To give more space for the Locals Window, drag the sizing handles of other windows upward to reduce their height. Alternatively, you can close the Debug Window temporarily and press Ctrl+G to bring it back when needed.

The Locals Window provides a graphical view of all objects and their properties in memory:
1. The first item with a plus [+] symbol shows the name of the Standard Module from which the program is running.
2. The next plus [+] symbol represents the Vol object, which is the instantiated ClsVolume2 object in memory.
Click the plus [+] symbols to expand each item and display detailed information about the object’s properties and member procedures.
Right-click to open Large Image in New Tab/Window.
You will find the next level of Objects and Properties.
The [+]CArea indicates that this Object has the next level of Properties and their Values.
The dblHeight Get property Procedure comes directly under the Vol Object.
The [+]p_Area is the Private Property declared ClsArea Class in the ClsVolume2 Class.
The p_Height is also the Private Property declared in the ClsVolume2.

Click on the plus [+] symbols to expand the objects to show their Properties and Values.

Right-click to open Large Image in New Tab/Window.
Expanding the [+] CArea node displays the ClsArea object that was passed to the Set CArea() property procedure.
The expansion of [+]p_Area gives the view of the ClsArea Property declared as Private.
Note that the p_Area Private Property, of the ClsVolume2 Class Object, and all its elements are accessible only through the CArea Object Property Get/Set Procedures to the outside world.
The second column in the Locals Window displays the values assigned to the object’s properties. At this stage, no values have been assigned, so the fields are currently empty.
The Third Column shows the Data Type or Object Class Module Names.
Press F5 to run the program further, till it is paused at the next Stop statement, to assign some values to the Object Properties. The program will pause at the next Stop statement. Check the Locals Window for changes in Values.

Right-click to open Large Image in New Tab/Window.
Inside the CArea Object, the first two lines with values 90, 10, and the last strDesc Variable with value "Bedroom" are assigned through the Get Property Procedures, respectively. The p_Desc, p_Length, and p_width are values assigned through Set Property Procedures to the p_Area Property of the ClsVolume2 Class Object as well.
The p_Area Object of ClsArea Class declared as Private Property of ClsVolume2 is seen with its Get/Set Property Procedures and assigned values.
Check the Type Column of [-]CArea and [-]p_Area; both Objects are derived from the ClsArea Base Class.

Usage of ClsArea and ClsVolume2 Class Objects Differently.
Next week, we will explore another approach using the same two objects.

If you’d like to experiment on your own beforehand, here’s a clue to get you started:
1. Instantiate ClsVolume2 and ClsArea classes as two different Objects in the Standard Module Program.
2. Assign values to both Object Properties.
3. Assign the ClsArea instantiated Object to the CArea Object in the ClsVolume2 Class Object, before printing the Values to the Debug Window.
In this example, we can achieve the same result as in the previous example without having to repeat the Get/Let property procedures in the ClsVolume class module.

The Links of All the Pages in this Topic.