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.

Here, we will build a Class Module for data processing tasks, a DAO.Recordset Object will be passed to the Custom Class Object. Since it is an Object, that is passed to our Custom Class, we need the Set and Get Property Procedure pair to assign and retrieve the Object or its Property values.

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

The above table has only four fields: Desc, Qty, UnitPrice, and TotalPrice. The TotalPrice 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 has a subroutine to sort the data, on the user-specified field, and dumps a listing on 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 its 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 accepts a Recordset object can be passed to the Class ClsRecUpdate 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 * 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 dumped on the Debug Window. The listing of fields will be limited to five fields only, if the record source has more than that then the rest of the fields are ignored.

The TblCreate() subroutine will Sort the data, based on the column number passed as a parameter, and creates a Table with a new name. The parameter is optional, if a column number is not passed as a parameter, then the Table will be sorted on the data in the first column if the data type of the column is a valid type. The original name of the Table will be modified and added with the String “_2” to the original name. If the Source Table name is Table1 then the new table name will be 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 pass any column numbers for updating a particular column. The column numbers not necessarily be consecutive numbers. But, the third column number parameter is the target column to update. The first parameter is multiplied by the second column parameter to arrive at the result value to update. You may modify the Class Module code to do any other operation you wish to do on the table.

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 tried an example to pass a Base Class Object, through the Set Property Procedure, to become part of the Object in memory. The passed object becomes an extension or Child-Object of the Main Object in memory. In our earlier program, passing the child Object to the Target Object was done in the instantiating phase of our test program. We have assigned values to the passed Object Properties in the later part of the program. The next example is slightly different.

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

This time we will open both Objects (ClsArea – the base class, ClsVolume2 – the target Class) separately in our test program. Assigning values in the Base Class ClsArea Properties, before passing them to the target Class ClsVolume2 Object. Remember the Volume2 Class has only one Property, the p_Height Property, and its Method Volume() needs the Length and Width Values of the Base Class ClsArea to calculate 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 defined as ClsArea Object and Vol as ClsVolume2 Object. The next two statements instantiate both objects in memory.
The next three statements assign values to the properties of the ClsArea Class Object.
The Stop statement gives a pause in the Code execution so that we can verify the Object Property values in the Locals Window.
The Set Vol.CArea = CA statement assigns the ClsArea Class Object CA, as a child object into the Vol (ClsVolume2) Object.
In the Next step dblHeight Property of ClsVolume2 Class Object is assigned with the value 10.
The following statements before the Stop statement print the Values from memory to the Debug Window.
The next two Set Statements remove the Objects from memory, before ending the program.
Display the Locals Window.
Select Locals Window Option from the View Menu.
Click somewhere in the middle of the Code and press F5 to run the code till the program pauses at the Stop statement. Alternatively, you can press F8 to run the code one step at a time to inspect the Locals Window for changes, at each step.
Click on the [+] Symbol to expand and display both Objects Properties and values.
Check the CArea and p_Area Object reference in the Value column of the Vol Object. The Value in there is showing as Nothing because we have not yet passed CA Object to the Vol Object.
If you have finished viewing the Locals Window contents, then run the code till the next Stop statement. Now, the CArea Get Property Procedure and p_Area Object are assigned to the ClsArea Class Object.

We will try another Variant example of both these two Classes ClsArea and ClsVolume2.

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

Check the Code from the beginning: p_Height declared as Private property. The p_Area Property of ClsVolume3 Class is declared as a Public ClsArea Object. That means p_Area will appear as a Property of the ClsVolume3 Class with its own displayable properties for direct Get/Let operations in the User Program, in the Standard Module. Even though ClsArea Class Object has been declared as Public Property of ClsVolume3 Class, its Properties and Methods are encapsulated in ClsArea Class itself.

The ClsArea Class must be a fully developed object, and be error-free to use it within other Objects.

Check the Class_Initialize() and Class_Terminate() Sub-Routines. The ClsArea Object is instantiated in the Class_Initialize() Code and removes the Object from memory in Class_Terminate() Code 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 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 have created a Derived Class ClsVolume Object, using Class ClsArea as Base Class. We have created Property Procedures in the Derived Class to expose the Base Class’s Properties and Function to the Object user programs. This method demands repetition of all the property procedures of the Base Class in the derived class too. Here, we explore how to create the same Derived ClsVolume Class without repeating the Property Procedures of the Base ClsArea Class.

We have learned the use of the Get and Let Property Procedures in Classes. There is one more Property Procedure used in Classes: The Set Property Procedure. The Set Property Procedure directly assigns an Object to a Class Object of the same Type.

Before continuing further you may visit the earlier pages on this topic, if you have not already done so, the links are given below:

ClsVolume Class, the Makeover.

We shall create a different variant of the same ClsVolume Class Module, that we have 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 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

Select Compile Project Name from Debug Menu to compile the VBA Code in the Database to ensure that all VBA Project Code is Err Free. If you have encountered an Error in your other VBA Programs, please track down the error, correct it and recompile your Project. Otherwise, the VBA IntelliSense that displays a list of properties and functions of Objects will not work. That will not prevent us from assigning/retrieving values to/from Object Properties. But, while learning it is important to see the Properties of an Object pop up and displays the list, as an assistant in Coding.

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

We have omitted all property procedures of ClsArea, created in the last version of ClsVolume Class, and replaced them with a Get/Set Property Procedures, instead of the Get / Let. Check the declaration Area where we have declared p_Area is declared as a ClsArea Class Object.

When an Object is declared is in this way we normally should create an instance of this object in the Class_Initialize() Procedure in the above Code. We didn't do it here because we plan to do it in the user Program and fill up its Properties with appropriate values and then pass it to the ClsVolume2 Class, before the final calculation phase to use their values.

Take note of the Set CArea() procedure. Its ByRef parameter AreaValue is declared as the ClsArea Object. It will accept the ClsArea Class Object when passed to the Property Set CArea(ByRef AreaValue as ClsArea), in object variable AreaValue, and assigns to the p_Area Property of ClsVolume2 Object.

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

In our earlier programs, we have written Property procedures for individual elements (Length, Width, Height) of an object to assign/return values To/From them. Here, the difference is that we are passing an entire Object as a Parameter to the Set Procedure. To retrieve this Object's Property Value (say lblHeight) we must address it as CArea.dblLength. The Get/Set Property Procedure name CArea becomes the child object of the main Object when declared in the Main Program and their Property Procedures can be accessed directly through the Object Address Vol.CArea.dblLength.

A Test Program in Standard Module.

We will write a small program in the Standard Module to test our newly derived Class Object ClsVolume2.

Insert a new Standard Module in your Project. Copy and paste the following Code into the Module and Save the code:

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 us take a quick look at the VBA code above. First-line instantiates the Class ClsVolume2 with the name Vol. After the next two comment lines the Set statement with the Vol.CArea Property Procedure is called and passes the New instantiated ClsArea Object as the Parameter.

I put a Stop statement on the next line to give a pause in the Program to see how the object is being assigned to the Set CArea Object. How to do that, We will explore that shortly?

The next four lines assign values to the ClsArea Object and to the Height property of the ClsVolume2 Object.

The next Stop creates a pause in the Program so that we can inspect the memory of how the values are kept in memory.

Next line prints the Headings in the Debug Window for the values printed on the next line.

Next line prints the values of Object Properties from memory in the Debug Window.

Run the Code to the Next Stop Statement

Let us run the Code and inspect the memory to see what happens there at each stage, where I put the Stop statement.

Click somewhere in the middle of the code and press F5 to run the code and pause the program at the first Stop Statement.
Select Locals Window from the View Menu to open a new window below the Code Window, to display how the ClsArea and ClsVolume2 Objects, their properties, and their member property procedures are held in memory. A sample image of the Locals Window is given below.
The Locals Window View.
Drag other Windows' sizing handles up to reduce their height to give more space for the display of Locals Window. Better, close Debug Window, for the time being, and use Ctrl+G to bring it back when needed later.

We can have a graphical view of all the objects and their Properties in the Locals Window. The first name with the plus [+] symbol shows the name of the Standard Module, from where our program is running.
The next plus [+] symbol with the name Vol is the ClsVolume2 instantiated Object in memory.
Click on the [+] symbols to expand and display the details.

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.

The expansion of [+]CArea gives us the view of the ClsArea Object we have passed to the Set CArea() property procedure.
The expansion of [+]p_Area gives the view of the ClsArea Property declared as Private.
Note the p_Area Private Property, of 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 of the Locals window will show the values assigned to the Object Properties and currently no values in them.
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.

Inside the CArea Object the first two lines with values 90, 10, and the last strDesc Variable with value "Bed Room" are assigned through the Get Property Procedures respectively. The p_Desc, p_Length, and p_width are values assigned through Set Property Procedures to p_Area Property of 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 ClsArea Base Class.

Usage of ClsArea and ClsVolume2 Class Objects Differently.

Next week we will try another approach with the same two objects. If you want to try it yourself, here is the clue as to how to try it out yourself.

Instantiate ClsVolume2 and ClsArea Class as two different Objects in the Standard Module Program.
Assign values into both Object Properties.
Assign the ClsArea instantiated Object to the CArea Object in ClsVolume2 Class Object, before printing the Values to the Debug Window.

In this example, we can achieve the same result as we did in the above example, without repeating the Get/Let Property Procedures as we did in the ClsVolume Class Module.