LEARN MS-ACCESS TIPS AND TRICKS: Wrapper Classes

Showing posts with label Wrapper Classes. Show all posts

MS-Access Class-Module Tutorial Index

a.p.r. pillaiSeptember 05, 2020Class Module, Collection Object, Dictionary Object, Export, RaiseEvent, WithEvents, Wrapper Classes 6 comments

We are planning a series of Tutorials on Microsoft Windows Tree View Control Programming in Microsoft Access and will publish the Series in the coming weeks.

In the meantime, I thought it was appropriate to organize the earlier Tutorial Links, on Class Module and Event Handling Programming Series, of Articles on a separate page, for easy access in published order.

This is helpful for those who would like to go through the Tutorials on a gradual progressive way of learning. Those tutorials started from the Basic-level and progressed through the more advanced stage of programming levels. The learning curve should start from the base level and go up to the top to understand the changes at each level. Once you are familiar with the Object or Control, you may continue to learn further by experimenting with them on your own Programs or Projects.

After all, there is more than one way to solve a problem in Computer Programming based on the programmer’s understanding of the Language, Skill, and Experience.

CLASS MODULE TUTORIALS

COLLECTION OBJECT

DICTIONARY OBJECT

MS-ACCESS EVENT HANDLING TUTORIALS

Download Android App MSA Guru Version of LEARN MS-ACCESS TIPS AND TRICKS, from the Google Play Store.

Download Link: MSA Guru Size: 2.3MB

Dictionary Object Basics

a.p.r. pillaiFebruary 03, 2019Array, Collection Object, tutorial, Wrapper Classes No comments

Introduction.

By now, I hope you have reviewed the recent articles on using the Collection Object in Microsoft Access. Even if you haven’t, you should still be able to follow along with the Dictionary Object and its usage. Collection and Dictionary Objects share many similarities, but understanding their differences will help you decide which one is best suited for a particular task.

In either case, the links are given below for easy access.

The Dictionary Object is not natively part of Microsoft Access VBA; it originates from VBScript, commonly used on web pages. To use a Dictionary Object in Access, we need to create it explicitly in a VBA program. There are two ways to accomplish this in Microsoft Access VBA:

A. With the use of MS-Access Function CreateObject().

Dim d As Object

Set d = CreateObject("Scripting.Dictionary")

This approach has a minor drawback for beginners: IntelliSense will not display the Dictionary Object’s methods and properties, because the object is declared as a generic Object type.

Dictionary Object Library File.

B. But, there is a better method. Add the Microsoft Scripting Runtime Library to the selected existing list of Libraries in Microsoft Access.

When we do that, we can declare and use the Dictionary Object as we did for the Collection Object.

Select the References option from the Tools Menu in the VBA Window.
The sample display of Library Files is given below.

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

The check-marked item (Microsoft Scripting Runtime) is the Library File you need to look for in your System. The unchecked items are in alphabetical order.
Move the Scrollbar down to find the file Microsoft Scripting Runtime, select it, and click the OK Button to come out.

Now, you can declare and instantiate a Dictionary Object with Intellisense support, like the sample code given below.

Dim d As Dictionary

Set d = New Dictionary

Dim d As New Dictionary

Dictionary Object has the following List of Methods and Properties:

Method Description

Add - Adds an item to the object with the specified Key. Always added an Item with a Key-Value.

Exists - Verifies that the specified key exists.

Items Return - an array of Item (Element) Values.

Keys - Returns an array of Keys.

Remove - Removes the Item specified by the Key.

RemoveAll - Removes the Dictionary Object from Memory.

Property Description

Count - Gives a count of Items in the dictionary.

Item - Retrieve/Replace/Add the item with the specified key. If the specified key doesn’t exist, then the Item value is added to the Dictionary with the specified key.

Key - Replaces the specified Key with a new Key.

CompareMode - Mode for comparing string keys.

0 - Binary (default): A <> a, A<a

1 - Text: A=a, Aa=aa, AA=aa

The Test Run Code.

Copy and paste the following sample code into your VBA Standard Module:

Public Sub Dict_Test0()
Dim d As Dictionary
Dim mkey, mitem
Dim strKey As String
Dim msg As String
Dim Title As String

Set d = New Dictionary

'Set Key-Text Compare Mode
d.CompareMode = 1 'Text Compare(nancy = NANCY = Nancy = NaNCy)
 
'Syntax: obj.Add "Key", "Content"

'Countries and Capitals

d.Add "Australia", "Canberra"
d.Add "Belgium", "Brussels"
d.Add "Canada", "Ottawa"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

For Each mkey In d.Keys
   msg = msg & mkey & vbCr
Next

msg = msg & vbCr & "Select a Country, Q=Quit."
Title = "Dict_Test0()"
strKey = ""

Do While strKey = "" And strKey <> "Q"
   strKey = InputBox(msg, Title, "")
   If strKey = "Q" Then
      Exit Do
   End If

If d.Exists(strKey) Then
mitem=d(strKey)
    MsgBox "Country: " & UCase(strKey) & vbCr & vbCr & " Capital:  " & UCase(mitem), , Title
Else
    MsgBox "Country: " & UCase(strKey) & vbCr & vbCr & "Doesn't exists.", , Title
End If

   strKey = ""
Loop

'Remove Dictionary from memory
d.RemoveAll

End Sub

Viewing the Values in Memory.

Insert a Stop statement immediately below the USA, Washington, D.C. Add a statement.
Select the Locals Window option from the View Menu.
Click somewhere within the Code and press F5 to run the Code.

The Program pauses at the Stop statement.

Check the Locals Window, click on the plus symbol in [+]d, and view the contents. It shows only the Key Values, and the Item values are not visible.

Press the F5 Key again to continue executing the Code.

Enter a Country Name from the displayed list and then press the Enter Key or click the OK Command Button to display the selected Country’s Capital.

You can type the country name in Upper Case/Lower Case or in mixed form. The compare mode setting at the beginning of the Code will take care of comparing the Key value entered by you with the list of keys in the Dictionary Object.

Enter the letter Q to Exit from the Do ... Loop and stop the Program.

How it Works.

Let us review the code. Since I have already added the Microsoft Scripting Runtime File into my selected list of VBA Library files, I could declare the variable d as a Dictionary Object, as we did with the Collection Object. Declared a few other required Variables as well.

The statement Set d = New Dictionary instantiates the Dictionary in memory as Object d.

The d.CompareMode determines how the given Key Value is compared with the existing list of Keys in memory for retrieving/replacing Item (Element) or Key-Value.

The Syntax Comment line indicates how to add an item to the Dictionary Object as its Element.

In Dictionary Object, the Key is the first parameter and the Item second. Both Key and Item Parameters are mandatory and separated by a Comma.

In the Collection Object, the order of both these parameters is reversed. The first Parameter is Item, and the second Parameter, Key, is Optional.

The d.Add the statement, and check whether the given key already exists in the Dictionary Object first; if it does, then give out an error message: ‘This key is already associated with an element of this Collection’. The key values must be unique.

If CompareMode=1, then the variants of the name ‘Nancy’, ‘nancy’, ‘NaNcY’ are all referring to the same Key NANCY or nancy.

If CompareMode=0 (Binary Compare), then all the above three names are different Keys.

When the Add method finds that the Key value given doesn’t match the existing Keys, the new Key is added to the Item Value in the Dictionary Object.

The Key Value can be of any Data Type except Variant, Array, or Object. Stick with one type of key value for all Items, not a mix of different data types.

We have added eight country names and their capitals.

The For Each … Next statement reads the list of Keys from the Dictionary Object and prepares a menu for the InputBox () Function.

The conditional Do While ... Loop runs until the User enters the letter Q or q (Quit) in the InputBox() function.

The user types a Country name through the InputBox() function to display the Country’s capital in a message box.

The entered country name in the strKey variable is validated, using the d.Exists() method to ensure that the entered Key exists in the Dictionary, reads the corresponding Item value, and displays it in the Message Box.

When the user enters the letter Q in the Inputbox() function, the program stops executing the statement d.RemoveAll That clears the Dictionary Object from memory.

We have read the Key Values alone using the For Each mKeys In d.Keys statement to create a list of Keys for the InputBox Menu. The d.Keys statement creates a 0-based Array of Key Values. You can create a separate Array of Key Values with the following statement:

myKeys = d.Keys

Determine the LBound and UBound Array elements to work with the list.

In the same way, we can read all Items (elements) into an Array, away from the Dictionary Object, to work with it if needed.

Taking a Listing of All Items.

Let us try to make a listing of all Items, with the method explained above. We will make a copy of the above Code and make some changes to retrieve the Items into an Array and print them into the Debug Window.

Here is the Code:

Public Sub Dict_Test0_1()
Dim d As Dictionary
Dim mitem, j As Long

Set d = New Dictionary

'Set Key-Text Compare Mode
d.CompareMode = 1 'Text Compare(nancy = NANCY = Nancy = NaNCy)
 
'Syntax: obj.Add "Key", "Content"

'Countries and Capitals
d.Add "Australia", "Canberra"
d.Add "Belgium", "Brussels"
d.Add "Canada", "Ottawa"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

mitem = d.Items

Debug.Print "Country Capitals"
Debug.Print "----------------"
For j = LBound(mitem) To UBound(mitem)
   Debug.Print j, mitem(j)
Next

'Remove the Dictionary from memory
d.RemoveAll

End Sub

Copy and paste the code into a Standard Module. Display the Debug Window (CTRL+G).

Run the code to get a listing of Country Capitals in the Debug window as shown below.

Country Capitals
----------------
 0            Canberra
 1            Brussels
 2            Ottawa
 3            Copenhagen
 4            Paris
 5            Rome
 6            Riyadh
 7            Washington D.C.

You may modify the item = d.Items statement to mitem = d.Keys to take a listing of all Countries.

We will continue this discussion Next Week.

MS-ACCESS CLASS MODULE

Wrapper Class Functionality Transformation

a.p.r. pillaiDecember 16, 2018Array, Calculation, Class Module, VBA, Wrapper Classes 7 comments

Introduction.

Last week, we created a new Wrapper Class named ClsTiles, which used the ClsArea Class twice within the same Class Module—one instance to store the room’s floor dimensions and another to store the tile dimensions—to calculate the number of tiles needed for the room.

In this new Wrapper Class Module, we will take the existing ClsVolume2 Class and transform it into a ClsSales Class. With a few cosmetic changes, we will give it a complete facelift inside the Wrapper Class, concealing its original purpose as a volume calculation class and repurposing it to calculate the selling price of products with a discount.

Interestingly, the ClsVolume2 Class already has all the necessary properties to store sales-related data: strDesc, dblLength, dblWidth, and dblHeight can be repurposed to represent Description, Quantity, Unit Price, and Discount Percentage, respectively.

Remember, the ClsVolume2 Class itself is a derived class, originally built using ClsArea as its base class.ClsVolume2 Class Re-Visited.

But, first, the VBA Code of ClsVolume2 Class Module (the Base Class for our new ClsSales Class Module) is reproduced below for reference:

Option Compare Database
Option Explicit

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 = CArea.dblLength * CArea.dblWidth * Me.dblHeight
End Function

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

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

Private Sub Class_Terminate()
Set p_Area = Nothing
End Sub

The only obstacle preventing us from using the ClsVolume2 Class directly for sales data entry is that its Property Procedure names—dblLength, dblWidth, and dblHeight—do not correspond to the required sales-related properties: Quantity, Unit Price, and Discount Percentage.

Fortunately, all these properties in the ClsVolume2 Class are of the Double data type, which is perfectly suitable for our sales calculations, so no data type changes are needed.

Similarly, the public function names Area() and Volume() are not meaningful in the context of sales, but their underlying calculation logic can be repurposed for sales-related computations without modification.

a) Area = dblLength * dblWidth is suitable for TotalPrice = Quantity * UnitPrice

b) Volume = Area * dblHeight is good for DiscountAmount = TotalPrice * DiscountPercentage

Here, we have two choices to use the ClsVolume2 as the ClsSales Class.

The easiest approach is to create a copy of the ClsVolume2 Class and save it as a new Class Module named ClsSales. Then, update the Property Procedure names and public Function names to reflect sales-related values and calculations. You can also add any additional functions needed for handling sales operations directly within this new class module.
Create a Wrapper Class based on the ClsVolume2 Base Class. In the Wrapper Class, implement corresponding property procedures and public functions that encapsulate and rename the Base Class’s property procedures and functions, effectively masking their original names. Add new functions, if any, to the Wrapper Class as needed.

The first option is relatively straightforward and easy to implement. However, we will choose the second option to learn how to access the Base Class’s properties within the new Wrapper Class and how to mask their original names with new property names.

The Transformed ClsVolume2 Class.

Open your Database and display the VBA Editing Window (Alt+F11).
Select the Class Module from the Insert Menu to insert a new Class Module.
Change the Class Module’s Name property Value from Class1 to ClsSales.

Copy and Paste the following VBA Code into the Module and Save the Code:

Option Compare Database
Option Explicit

Private m_Sales As ClsVolume2

Private Sub Class_Initialize()
    'instantiate the Base Class in Memory
    Set m_Sales = New ClsVolume2
End Sub

Private Sub Class_Terminate()
    'Clear the Base Class from Memory
    Set m_Sales = Nothing
End Sub

Public Property Get Description() As String
  Description = m_Sales.CArea.strDesc 'Get from Base Class
End Property

Public Property Let Description(ByVal strValue As String)
  m_Sales.CArea.strDesc = strValue ' Assign to Base Class
End Property

Public Property Get Quantity() As Double
Quantity = m_Sales.CArea.dblLength
End Property

Public Property Let Quantity(ByVal dblValue As Double)
   If dblValue > 0 Then
     m_Sales.CArea.dblLength = dblValue ' Assign to clsArea, Base Class of ClsVolume2
   Else
     MsgBox "Quantity: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
     Do While m_Sales.CArea.dblLength <= 0
          m_Sales.CArea.dblLength = InputBox("Quantity:, Valid Value >0")
     Loop
   End If
End Property

Public Property Get UnitPrice() As Double
   UnitPrice = m_Sales.CArea.dblWidth
End Property

Public Property Let UnitPrice(ByVal dblValue As Double)
   If dblValue > 0 Then
      m_Sales.CArea.dblWidth = dblValue ' Assign to clsArea, Base Class of ClsVolume2
   Else
     MsgBox "UnitPrice: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
       Do While m_Sales.CArea.dblWidth <= 0
          m_Sales.CArea.dblWidth = InputBox("UnitPrice:, Valid Value >0")
       Loop
   End If
End Property

Public Property Get DiscountPercent() As Double
   DiscountPercent = m_Sales.dblHeight
End Property

Public Property Let DiscountPercent(ByVal dblValue As Double)
' Assign to Class .dblHeight of ClsVolume2
Select Case dblValue
    Case Is <= 0
       MsgBox "Discount % -ve Value" & dblValue & " Invalid!", vbExclamation, "ClsSales"
       Do While m_Sales.dblHeight <= 0
          m_Sales.dblHeight = InputBox("Discount %, Valid Value >0")
       Loop
    Case Is >= 1
       m_Sales.dblHeight = dblValue / 100
    Case 0.01 To 0.75
       m_Sales.dblHeight = dblValue
End Select
End Property

Public Function TotalPrice() As Double
Dim Q As Double, U As Double
Q = m_Sales.CArea.dblLength
U = m_Sales.CArea.dblWidth
If (Q * U) = 0 Then
   MsgBox "Quantity / UnitPrice Value(s) 0", vbExclamation, "ClsVolume"
Else
   TotalPrice = m_Sales.CArea.Area 'Get from Base Class ClsArea
End If
End Function

Public Function DiscountAmount() As Double
   DiscountAmount = TotalPrice * DiscountPercent
End Function

Public Function PriceAfterDiscount()
   PriceAfterDiscount = TotalPrice - DiscountAmount
End Function

So far in the Wrapper Class, we have created an instance of the ClsVolume2 class, renamed its property and function members to more suitable names, and added validation checks with appropriate error messages. We also ensured that invalid input does not trigger the Base Class’s own validation routines, which could otherwise display error messages like “Value of the dblLength property is invalid” from the ClsVolume2 class.

Review the highlighted lines in the code above; they should help you understand how the property values are assigned to and retrieved from the Base Class ClsVolume2.

First, review the ClsArea Class Module, and then examine the ClsVolume2 Class Module, which is derived from ClsArea. After understanding both of these, revisit the code in this Wrapper Class for a better perspective.

Test Program for ClsSales Class in Standard Module.

Let us write a Test Program to try out the Wrapper Class.

Copy and Paste the following VBA Code into a Standard Module.

Public Sub SalesTest()
Dim S As ClsSales

Set S = New ClsSales

S.Description = "Micro Drive"
S.Quantity = 12
S.UnitPrice = 25
S.DiscountPercent = 0.07

Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
With S
    Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
End With
End Sub

Run The Code.

Keep the Debug Window open (Ctrl+G).
Click somewhere in the middle of the Code and press the F5 key to run the Code and to print the output in the Debug Window.
You can further test the code by entering negative numbers for any of the input values and running it to trigger the new error messages. You may also try disabling one or more input lines by placing a comment symbol (') at the beginning of the line, then rerun the code and observe the results.

Calculate Price/Discount for an Array of Products.

The following test code creates an array of three Products and Sales Values by entering directly from the Keyboard.

Copy and paste the following Code into a Standard Module and run to test the Wrapper Class further.

Public Sub SalesTest2()

Dim S() As ClsSales
Dim tmp As ClsSales
Dim j As Long

For j = 1 To 3
Set tmp = New ClsSales
   tmp.Description = InputBox(j & ") Description")
   tmp.Quantity = InputBox(j & ") Quantity")
   tmp.UnitPrice = InputBox(j & ") UnitPrice")
   tmp.DiscountPercent = InputBox(j & ") Discount Percentage")
 ReDim Preserve S(1 To j) As ClsSales
 Set S(j) = tmp
 Set tmp = Nothing
Next

'Output Section
Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
For j = 1 To 3
   With S(j)
       Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
   End With
Next

For j = 1 To 3
  Set S(j) = Nothing
Next

End Sub

Once the correct values have been successfully entered into the array, the product names and corresponding sales values are displayed in the Debug Window.