March 2019 ~ LEARN MS-ACCESS TIPS AND TRICKS

Friday, March 29, 2019

Update Class Object Dictionary Item through Form

Introduction

Last week, we conducted a trial run to add and retrieve Class Objects from Dictionary Object Items.
One important point we emphasized was that each Class Object added to the Dictionary must be created as a new temporary instance of the Class Object every time it is added.

This practice is not exclusive to the Class or Dictionary objects; it is a general best practice that should be followed whenever adding objects to a Collection, Dictionary, or an Object Array, to prevent accidental overwriting of existing instances in memory. A brief review of the correct procedure is given below:

Create a temporary Object instance.
```
Set tmpObj = New Object
```
Assign values to the temporary object’s Properties.
Add the temporary object instance as an Item to the Dictionary, Collection, or as an element in the Object Array.
Release the temporary object instance from memory so that it can be reused:
```
Set tmpObj = Nothing
```

Repeat steps 1 to 4 for each new object you want to add.

If you would like to know why we can’t use only one instance of the Class Object to add several Items to the Dictionary Object, visit the page Add Class Object as Dictionary Object Items?

Class Object Instances as Dictionary Items

Now, let us get ourselves prepared for a trial run to add, edit, update, and delete Class Object Items in the Dictionary Object, through an MS-Access Form. We worked with Form and Dictionary Object about two weeks back, by adding and retrieving simple items in the Dictionary Object.

This time, we are dealing with Objects and Properties in Dictionary Objects, not simple Items, with added data management functions. Besides that, when the Form is closed, the Dictionary Item Values (ClsArea Class Object Property Values) are saved to a Table.

We will use our simple ClsArea Class Object, with only three Property Values to fill in (Description, Length, and Width Values), for our trial run.

Even though the Table is used only at the end of the session, we will design a simple table first with the fields: Description (strDesc), Length (dblLength), and Width (dblWidth), matching the Class Module ClsArea Object Property Names in brackets.

Create a new Table with the name ClsArea with the Field Names and Field Types shown in the Table Design Image shown below. For Numeric Data Fields, select Double Precision Number Type.

Right-click to open Large Image in New Tab/Window.
Next, we need a Form with the following design to manage the data of the Dictionary Object.

Right-click to open Large Image in New Tab/Window.
Design a Form with the following Controls (not bound to the Table):
- Combo Box on the Header:
  Name Property Value: cboKey
  Row Source Type = Value List
- Four Unbound Text Boxes in the Detail Section of the Form with the following names:
  strDesc
  dblLength
  dblWidth
  Area
- Create another TextBox to the right of the last TextBox with the Name:
  ItemCount
- Create three Command Buttons below the Text Boxes, on the Detail Section, with the Names:
  cmdAdd and Caption: Add
  cmdEdit and Caption: Edit
  cmdDelete and Caption: Delete
- Create two Command Buttons in the Footer Section of the Form with the Names:
  cmdSave and Caption: Save to Table
  cmdExit and Caption: Exit
The Form’s Normal View Image, with some sample data, is given below:

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

As the Form control names indicate, you can enter the Property values of the ClsArea Class Object and add the Class Object as an Item to the Dictionary Object.
The value entered in the strDesc Property will be used as the Key for the Dictionary Item.

⚠️ Important: Ensure that the Description (strDesc) values are unique for each item. If a duplicate key is entered, the Dictionary Object will reject it and trigger an error.
(Extensive validation checks have been intentionally omitted in the code to keep it simple.)

You can also retrieve a specific Class Object’s property values from the Dictionary by selecting its Key from the cboKey Combo Box, then:

Edit the values directly on the Form and update them back into the Dictionary, or
Delete the unwanted item from the Dictionary Object entirely.

The Form Module Event Procedures.

There are several Event Procedures defined in the Form’s Class Module.

A brief explanation of each procedure is provided below.

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

All important Objects and Variables are declared in the Global declaration area.

Private Sub Form_Load()

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

At the Form Load Event, the Dictionary Object is instantiated.

Edit and Delete Command Buttons are disabled because the Form will be in data entry mode by default. Data save detection flag saveFlag is set to false. The flag will be set to True when the Dictionary Data is saved to the Table, from one of two possible selections of Command Buttons at the Footer of the Form.

Private Sub Form_Current()

Private Sub Form_Current()
Dim o As String
    o = cboKeys.RowSource
    If Len(o) = 0 Then
       Me.cboKeys.Enabled = False
    Else
       Me.cboKeys.Enabled = True
    End If
End Sub

Checks whether the Combo box's Row Source property has any Value in it (when the Form is open, it will be empty); if not, disable the Combo box Control.

Private Sub strDesc_GotFocus()

Private Sub strDesc_GotFocus()
 If editFlag Then
  Me.cmdAdd.Enabled = False
  Me.cmdEdit.Enabled = True
  Me.cmdDelete.Enabled = True
 Else
  Call initFields
  Me.cmdAdd.Enabled = True
  Me.cmdEdit.Enabled = False
  Me.cmdDelete.Enabled = False
  editFlag = False
 End If

End Sub

Checks whether the form is in Edit mode or not. Edit Mode Flag is set when the user clicks the Edit Command Button. This happens after the user selects an item from the Combobox.

If in edit mode, the data entry fields are not cleared, the Edit and Delete Command Buttons are enabled, Add Command Button is disabled.

If in Add mode, then data fields are emptied in preparation for a new Item, the Add Command Button is enabled, and the Edit and Delete Command Buttons are disabled.

Private Sub dblWidth_LostFocus()

Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

On the dblWidth Text Box's LostFocus Event, the product of dblLength * dblwidth is displayed in the Area Text Box.

Private Sub cmdAdd_Click()

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub

The TextBox values are transferred into temporary variables and checked to see whether any text box is empty or not.

The ClsArea Class Object Properties are assigned with strDesc, dblLength & dblwidth text box values, from temporary variables.

Adds Class Object to Dictionary Object.

Updates the Combo Box with the Dictionary Object Key from the Class Object's Description (strDesc) Value.

The Dictionary Items Count is displayed on the ItemCount Text Box.

The focus is set in the strDesc field. The text boxes are cleared for entry of new values.

Private Sub cboKeys_AfterUpdate()

Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

Retrieving the Dictionary Item

After adding several items to the Dictionary Object, you can retrieve any item by selecting its Key from the Combo Box. The corresponding details will be displayed on the Form. In this mode, the Add command button and the strDesc field will remain disabled. The strDesc field will be unlocked only when you click the Edit button.

If you select an invalid Key (for example, a Key value that was changed during earlier edit operations) from the Combo Box, an error message — ‘Item for Key: XXXX not found’ — will be displayed, indicating that the specified item does not exist in the Dictionary Object.

Private Sub cmdEdit_Click()

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub

Editing Dictionary Item

The item retrieved from the Dictionary and displayed on the Form can be edited by clicking the Edit command button. When you click it, the button’s caption changes to Update.

Make the required changes to the fields (including the Description, if needed), and then click the Update button again to save the changes back into the Dictionary Object.

If the strDesc value (the Dictionary Key) is modified, the corresponding old Key in the Dictionary is replaced with the new Description value. The Combo Box will also be updated to reflect the new Key, though the old Key will remain listed in the Combo Box until it is refreshed.

After the update, the data fields are cleared, the Add button is enabled again, and the Edit and Delete buttons are disabled.

Private Sub cmdDelete_Click()

Private Sub cmdDelete_Click()p
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'make description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

Deleting Dictionary Item

When the Delete Command Button is clicked, the current Item on the Form is deleted from the Dictionary, after the user reconfirms it.

The data entry fields are cleared, and the item count control is updated with the reduced number of items.

Private Sub cmdSave_Click()

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub

Saving Data from the Dictionary Object.

When you have finished working with the Form and want to save the Dictionary Data Class’s property values from the Dictionary to the temporary Table (clsArea) and to close the Form, click on the 'Save to Table' Command Button.

Private Sub cmdExit_Click()

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

If you choose to click the Exit command button instead of the Save to Table button, you will be prompted to confirm whether you want to save the data. If you decide not to save, you can select the appropriate option to close the form without writing the data to the table.

There are also a few common subroutines that are called from multiple event procedures. Their code is included in the full listing provided below:

Private Sub initFields()
Clears all text boxes on the form when called from event procedures.
Private Sub comboUpdate(ByVal stDesc As String)
Called from the cmdAdd_Click() and cmdEdit_Click() event procedures to update the Combo Box items.
Private Sub Save2Table()
Called from the cmdSave_Click() and cmdExit_Click() event procedures to save the Dictionary Object data to the table.

The Form's Class Module Code.

Highlight, Copy, and Paste the Entire Code given below in the Form's Class Module of your Form. Save the Form with the name frmDictionary.

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary”)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

Private Sub Form_Current()
Dim o As String
o = cboKeys.RowSource
If Len(o) = 0 Then
  Me.cboKeys.Enabled = False
Else
  Me.cboKeys.Enabled = True
End If
End Sub


Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub

Private Sub cmdDelete_Click()
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'select description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub

Private Sub Save2Table()
Dim db As Database, rst As Recordset
Dim recCount As Long, j As Long, item

On Error GoTo Save2Table_Error

recCount = D.Count

Set db = CurrentDb
Set rst = db.OpenRecordset("ClsArea", dbOpenTable)
For Each item In D.Items
   With rst
        .AddNew
         !strDesc = item.strDesc
         !dblLength = item.dblLength
         !dblWidth = item.dblWidth
        .Update
    End With
Next

rst.Close
Set rst = Nothing
Set db = Nothing

saveFlag = True
MsgBox "Data Saved  to Table: ClsArea"

Save2Table_Exit:
Exit Sub

Save2Table_Error:
MsgBox Err & ":" & Err.Description, , "Save2Table_Click()"
Resume Save2Table_Exit

End Sub

Private Sub Form_Unload(Cancel As Integer)
Set D = Nothing
End Sub

Private Sub strDesc_GotFocus()
    If editFlag Then
        Me.cmdAdd.Enabled = False
        Me.cmdEdit.Enabled = True
        Me.cmdDelete.Enabled = True
    Else
        Call initFields
        Me.cmdAdd.Enabled = True
        Me.cmdEdit.Enabled = False
        Me.cmdDelete.Enabled = False
        editFlag = False
    End If

End Sub


Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

Private Sub initFields()
'Empty all fields
    Me!strDesc = Null
    Me!dblLength = Null
    Me!dblWidth = Null
    Me!Area = Null
    Me.cmdAdd.Enabled = True
End Sub

Private Sub comboUpdate(ByVal stDesc As String)
Dim cbo As ComboBox, cboVal As String
Set cbo = Me.cboKeys
    cboVal = cbo.RowSource
    cboVal = cboVal & ";" & stDesc
    cbo.RowSource = cboVal
    cbo.Requery
    If Len(cboVal) > 0 Then
      Me.cboKeys.Enabled = True
    End If
End Sub

Perform a Demo Run.

Open the frmDictionary form, and try adding a few entries to the Dictionary object.
Select an item from the Combo Box to view the property values of the corresponding class object.
Click the Edit command button and modify the Description, Length, and Width values as needed.
Click the Edit button again (now showing the caption Update) to save the changes back into the Dictionary object. If you open the Combo Box afterward, you will see both the updated item with the new description and the old item with its previous description.
Select the old description from the Combo Box. You will receive an error message indicating that the item no longer exists, as its Dictionary key has been updated with the new description.
To delete an entry, select an item from the Combo Box and click the Delete command button. The selected item will be removed from the Dictionary object.
The event procedure code has been kept simple, without validation checks, error-handling routines, or safeguards against unintended user actions on the form.
You may enhance the code with these features, if needed, and reuse it in your own projects.

Downloads

You may download this demo database from the link given below.

Download Dictionary_2003.zip

Download Dictionary_2007.zip

MS-ACCESS CLASS MODULE

Tuesday, March 19, 2019

Add Class Objects as Dictionary Items

Introduction.

We have learned the fundamentals of the Dictionary Object, experimented with sorting simple items, and displayed Access table records on an MS Access form using the Dictionary Object.

Now, let us move a step further and learn how to add MS Access Class Objects to a Dictionary, retrieve each object item, and display their Property values and Method outputs in the Debug Window.

If you would like to revisit the earlier related articles for reference, their links are provided below.

We need to have the ClsArea class object in the database to try out this example using the Dictionary object.

If it is not already present, you can create it by following these steps:

Open the VBA Editor.
Insert a new Class Module.
In the Properties Window, change the (Name) property of the new class to ClsArea.
Copy and paste the following VBA code into the ClsArea class module and save it.

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
  strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
  p_Desc = strNewValue
End Property

Public Property Get dblLength() As Variant
  dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Variant)
    Do While Val(Nz(dblNewValue, 0)) <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
    Loop
  p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Variant
  dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Variant)
    Do While Val(Nz(dblNewValue, 0)) <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
    Loop
  p_Width = dblNewValue
End Property

Public Function Area() As Double
  If (Me.dblLength > 0) And (Me.dblWidth > 0) Then
     Area = Me.dblLength * Me.dblWidth
  Else
     Area = 0
     MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
  End If
End Function

Private Sub Class_Initialize()
    p_Length = 0
    p_Width = 0
    'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub

Private Sub Class_Terminate()
   'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub

The ClsArea Class object has three properties — strDesc, dblLength, and dblWidth — and one method: Area().

We will create multiple instances of this class to represent different rectangular shapes or rooms, and calculate their areas using the Area() method, and add each class object instance as an Item in a Dictionary object.

The value stored in the class object's strDesc property will be used as the Key for each corresponding item in the dictionary.

Since dictionary keys must be unique, make sure that the strDesc property values are not duplicated.

For example, if you have several bedrooms to calculate areas for, you can name them Bedroom1, Bedroom2, Bedroom3, and so on.

The ClassObjInDictionary() Procedure Code.

Let us try the Dictionary with the Class Module Object ClsArea as Items. The sample VBA Code for the Dictionary Object is given below. Copy and paste it into a Standard Module and save the Module.

Public Sub ClassObjInDictionary()
'--------------------------------------------------
'Add Class Object as Items to Dictionary Object
'Retrieve the Class Object from Dictionary Object
'and Print the values in the Debug Window.
'--------------------------------------------------
Dim C As ClsArea
Dim D As Object, Desc As String, mKey

Set D = CreateObject("Scripting.Dictionary")
D.CompareMode = 1
Desc = ""

Do While Not Desc = "Q"
'instantiate Class Object
Set C = New ClsArea
    
    'Get input Values for ClsArea Object\
    Do While Len(Desc) = 0
      Desc = InputBox("Description or Q=Quit:")
    Loop
       If Desc = "Q" Then Exit Do
       
    C.strDesc = Desc
    C.dblLength = CDbl(InputBox("Length of " & UCase(Desc) & ": "))
    C.dblWidth = CDbl(InputBox("Width of " & UCase(Desc) & ": "))
    
'add to Dictionary
'Description is added as Key of Dictionary Object
    D.Add Desc, C
    Desc = ""
'Clear Class Object
    Set C = Nothing
Loop


If D.Count = 0 Then
  MsgBox "No Data in Dictionary Object!" & vbCr & "Program Aborted."
  Exit Sub
End If

'Output Section
Debug.Print "Key Value", "Description", "Length", "Width", "Area"

For Each mKey In D.keys
        Set C = D(mKey)
        Debug.Print mKey, C.strDesc, C.dblLength, C.dblWidth, C.Area
Next

End Sub

The VBA Code Line by Line.

At the beginning of the code, we create and instantiate the Dictionary object D.

We use the Desc string variable to capture the description text that will be assigned to the strDesc property of the class object. This same variable also acts as the control for the Do While ... Loop.

The loop continues to run until the user enters the single character Q (for Quit) into the Desc variable.

This approach allows you to enter any number of class object instances into the dictionary. When you are done, simply enter Q in the description prompt to exit the loop.

Next, we create an instance of the ClsArea class object using the object variable C.

Inside this loop, the statement Desc = InputBox() is placed within a second Do While ... loop. This ensures that the user actually enters a value into the Desc variable.

If the user presses Enter, clicks OK, or Cancel without entering any text, the InputBox() function will repeat to prompt until a valid value is entered.

The valid Description Value is assigned to the C.strDesc Property of the Class Object.

Through the next two InputBox() functions, collect the Length and width values of the Room from the user and assign them to C.dblLength and C.dblWidth Properties, respectively.

Now, the ClsArea Class Object is ready to be added to the Dictionary Object.

The statement D.Add Desc, C adds the current instance of the ClsArea Class Object in the Dictionary Object as its first Item to Desc (or C.strDesc Property Value) as the Key of Dictionary Item.

Next, we clear the ClsArea Class Object instance C from memory.

You might have noticed by now that the Class Object instance C is created at the beginning of the outer Do While ... Loop, fills up the Class Object Property Values, adds it to the Dictionary Object, and the Class Object instance C is Set to Nothing as the last statement within the Loop. That means we are creating a New Class Object instance for each Item in the Dictionary Object.

Why it has to be this way, creating new instances of the Class Object for each Item, is an important point to keep in mind.

When we add the Class Object instance as an Item to the Dictionary Object, internally, only the Class Object’s Location Address is saved in the Dictionary Object as a Pointer. The actual Class Object Property values are not moved to the Dictionary Object Item.

When we execute the statement Set C = Nothing, the Class Object instance C is cleared, but the instance’s location reference (pointer) is saved in the Dictionary Object Item. The actual ClsArea Class Object remains in that location, and we can retrieve it using the Object Pointer saved in the Dictionary Object Item.

When a new Class Object Instance is created, it is done in a new location in memory, and its reference is added to the Dictionary Object.

Enter some Description for a few bedrooms, Length, and Width Values to test the Code. Enter the letter Q to complete the Data entry when you are ready to take a dump of the data in the Debug Window.

A sample Listing is given below:

Key Value     Description   Length        Width         Area
Bed Room1     Bed Room1      14            15            210 
Bed Room2     Bed Room2      12            12            144 
Living Room   Living Room    23            24            552 
Kitchen       Kitchen        11            11            121 
Store Room    Store Room     21            14            294

In the printing code segment, we did not create a new instance of the ClsArea object (C) to read the class object pointers stored in the dictionary items. Instead, we directly accessed the stored object references from the dictionary and printed their values in the Debug Window.

Note: If you feel more comfortable doing so, you may create an ClsArea object instance (C) and assign the stored dictionary item reference to it. Both approaches work equally well.

The statement


Set C = D(mKey)

reads the object reference (pointer) of the ClsArea object from the dictionary item into C. Once assigned, you can retrieve its property values and method output and print them to the Debug Window.

If you have already run the sample code and understood how it works, try a small modification:

Move the object creation and object cleanup (removal) statements for the ClsArea object outside the Do While...Loop.
Then, rerun the code, add a few items to the dictionary, and print their property values to verify the output in the Debug Window.

Take a Trial Run With the Following Changes in the Code

The Do While ... loop segment with suggested changes is given below for you to make changes in your code. Check the highlighted statements above and below the Do While ... Loop.

Desc = ""
Set C = New ClsArea

Do While Not Desc = "Q"
'instantiate Class Object
    
    'Get input Values for ClsArea Object
    Do While Len(Desc) = 0
      Desc = InputBox("Description or Q=Quit:")
    Loop
       If Desc = "Q" Then Exit Do
       
    C.strDesc = Desc
    C.dblLength = CDbl(InputBox("Length of " & UCase(Desc) & ": "))
    C.dblWidth = CDbl(InputBox("Width of " & UCase(Desc) & ": "))
    
'add to Dictionary
'Description is added as Key of Dictionary Object
    D.Add Desc, C
    Desc = ""
'Clear Class Object
Loop
    Set C = Nothing

I moved the statement Set C = New ClsArea to a position above the Do While...Loop, and placed the Set C = Nothing statement below the Loop so that it executes only after completing the data entry of Class Objects into the Dictionary within the Do While...Loop.

I entered all five sample items listed earlier, using the same names but with different values for Length and Width.

Finally, the printing section listed all five items in the Debug Window.
However, instead of showing the individual values entered for each item, the values of the last item were printed for all five entries.

Key Value     Description   Length        Width         Area
Bed Room1     Store Room     12            13            156 
Bed Room2     Store Room     12            13            156 
Living Room   Store Room     12            13            156 
Kitchen       Store Room     12            13            156 
Store Room    Store Room     12            13            156

Why has it happened this way?

When we add a Class Object with its Properties to a Dictionary Object, only the reference (memory address) of the Class Object is stored in the Dictionary Item—not its actual Property values.

When an instance of the Class Object is created using the New keyword, that instance is assigned a fixed memory location (address). Any new values entered into its Properties will overwrite the previous values stored in that same instance. Each time this same object reference is added to the Dictionary, the Dictionary stores only the address of the Class Object, not a copy of its current Property values.

By contrast, if we create a new instance of the Class Object during each loop cycle, a fresh object is created, with a different memory address. The Dictionary stores these unique addresses, allowing each Item to retain its own distinct Property values.

When the statement Set C = Nothing is executed, it simply clears the reference from the object variable C so it no longer points to any location. However, the actual object data remains alive in memory because its reference is still held by the Dictionary.

But when we moved the Set C = New ClsArea and Set C = Nothing statements outside the Do While...Loop, we ended up using only a single instance of the Class Object to input multiple sets of values. Each new set of Property values overwrote the previous ones in that same object. As a result, all the Dictionary Items ended up pointing to this single object instance, which holds only the last set of values entered.

Therefore, during printing, even though the Keys appear correctly in the listing, all the Items show the same (last entered) Property values.

Next week, we will learn how to add, edit, update, and delete Class Objects in the Dictionary through an MS Access Form.

MS-ACCESS CLASS MODULE

Sunday, March 10, 2019

Display Records from Dictionary to Form

Introduction.

We will now perform a similar exercise to what we did earlier with the Collection Object—displaying table records on a form based on a key value selected from a Combo Box.

This time, we will use a Dictionary Object to store Employee Records, using the Last Name as the Key.

Design a sample form:

Create a Combo Box in the Header Section (to list all employees' last names).
A few TextBox controls in the Detail Section (to display the selected employee’s information).

We will then write the Form-based event procedures in the Form Class Module.

Load data records from a Table or Query into the Dictionary Object.
Retrieve a specific record from the Dictionary, based on the selected key (Last Name) from the Combo Box.
Populate the TextBoxes on the form with the retrieved record values.

Note: You can download a Demo Database, with the Form and VBA Code, from the bottom of this page.

Let us start with the preparation steps so that you will know what it takes to complete this Project. You will be better informed of the whole process if you plan to implement this method in one of your own projects.

The Employees Table for Sample Data.

We need some data to load into the Dictionary Object.

Import the Employees Table from the Northwind sample database.
Copy and paste the following SQL String into the SQL editing window and save it with the name: EmployeesQ
```
SELECT Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.[Job Title], Employees.[Business Phone], Employees.[Home Phone]
FROM Employees;
```
The Sample Form with a Few Textboxes and a Combo Box.
We will take only a few fields of data from the Employees table. We can quickly design a Form with the field names from EmployeesQ, but without attaching the EmployeeQ to the Form as a record source. The next steps will be needed to add Text Boxes with correct data field names, without typing them into the Name Property of the Text Boxes.
NB: You can give any name to the TextBoxes; it works with any name.
Select the Design Form option from the Forms Group of the Create Menu. It will open a Blank Form.
Right-click on the Form and select Form Header/Footer to insert Header and Footer sections to the Form.
The Sample Design of the Form is given below.
Right-click to open Large Image in New Tab/Window.
Click on the Detail Section of the Form to make it an active Section.
Now, we will add six Text Boxes from the Employee record with field names as the Text Box Names.
Click on the 'Add Existing Field' Button from the Tools Buttons Group in the Design Menu.
Find the Employees Table and click on the [+] Symbol to show the Employees Table Fields.
Double-click on the following list of Fields, one by one, to insert them into the Detail Section of the Form:
- Last Name
- First Name
- E-mail Address
- Job Title
- Business Phone
- Home Phone
NB: This is an Unbound Form, and the inserted Field controls also must be Unbound Text Boxes.

Keep the Text Box's Name Property Value (Field Name) and remove the Control Source Property Value.
Click on the first Text Box to select it.
Display the Property Sheet (F4) of the selected Text Box.
Remove the data Field Name from the Control Source Property to make the text box Unbound. Ensure that the Name Property Value remains intact.
Remove other Text Box's Control Source Property Values.
Next, we need a Combo Box on the header of the Form with the list of the Last names of Employees.
Select the Combo-Box control from the Controls Group under the Design Menu and place the Combo-Box control in the Header Section of the Form. If the Control Wizard is active, then follow steps 14 to 18, and go to step 19
If Control Wizard is on, then select the first option and click Next.
Select the Query Option on the next screen, select EmployeeQ, and click Next.
In the next Screen, double-click on the Last Name to select and insert it into the right panel, and click Next.
Select the Last Name in the first text box to sort Last Names in Ascending Order, and click Next.
On the next screen, click Finish.
Change the Name Property Value of the Combo-Box to cboLastName.
Find the Limit to List Property of the Combo Box and change the Value to Yes.
Copy and paste the following SQL into the Row Source Property of the Combo-Box:
```
SELECT EmployeesQ![Last Name] FROM EmployeesQ;  
```
Insert a Command Button in the Footer Section of the Form.
Change the Caption of the Command Button to Exit and the Name Property value to cmdClose.
Select the View Code Button from the Tools Group to display the Class Module of the Form.

Highlight the entire VBA Code below, copy and paste it into the Form’s Class Module, overwriting the existing lines of Code:

The Form's Class Module VBA Code.

Option Compare Database
Option Explicit

Private D As Object
Dim txtBox() As String

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim Rec() As Variant
Dim fldCount As Long, ctl As Control
Dim k As Long, frm As Form, Sec As Section
Dim strKey As String

'Restore the Form to it's actual design size
DoCmd.Restore

'instantiate Dictionary Object
   Set D = CreateObject("Scripting.Dictionary")
   

'Open Recordset Source to save in Dictionary
   Set db = CurrentDb
   Set rst = db.OpenRecordset("EmployeesQ", dbOpenDynaset)
'get recordset fields count
   fldCount = rst.Fields.Count - 1

'Redimension Field Names Array (Rec) for number of fields in Table
   ReDim Rec(0 To fldCount) As Variant

   'Add records to Dictionary Object
   Do While Not rst.EOF
     'Get current record field values into Rec Variant Array
     For k = 0 To fldCount
        Rec(k) = rst.Fields(k).Value
     Next
     'Last Name as Dictionary Key
     strKey = rst.Fields("[Last Name]").Value
     
    'Add record to Dictionary Object with 'Last Name' Key
     D.Add strKey, Rec
     rst.MoveNext
   Loop
   
   'Set current Form
   Set frm = Me
   
'Set Detail Section of Form to look for Text Boxes
   Set Sec = frm.Section(acDetail)
   
'Redim txtBox Array to save Textbox Names on the Form
'To display field values
   ReDim txtBox(0 To fldCount) As String
  
  'Get Text Box Names,from Detail Section of Form, and save them into Array.
  'this will be used in the ComboBox AfterUpdate Event
  k = 0
  For Each ctl In Sec.Controls
     If TypeName(ctl) = "TextBox" Then
        txtBox(k) = ctl.Name
        k = k + 1
     End If
  Next
   
   rst.Close
   Set rst = Nothing
   Set db = Nothing
End Sub


Private Sub cboLastName_AfterUpdate()
  Dim strD As String, R As Variant
  Dim j As Long
  Dim L As Long
  Dim H As Long

'Get Selected Key from ComboBox
 strD = Me![cboLastName]
  
  'Retrieve the record from Dictionary
  'using KEY and load the field
  'Values into the Variant Array
  R = D(strD)
  L = LBound(R)
  H = UBound(R)
'Read Field Values from Array and display
'them into it's corresponding Textbox names on the Form
  For j = L To H
    Me(txtBox(j)) = R(j)
  Next
  Me.Refresh

End Sub

Private Sub cmdClose_Click()
  DoCmd.Close
End Sub


Private Sub Form_Unload(Cancel As Integer)
  'Clear Dictionary Object from Memory
  Set D = Nothing
End Sub

Save the Form with the name Dict_Employees or any other name you prefer.

How it All Works Together.

There are four Subroutines in the above Code.

Private Sub Form_Load() Event Procedure.
Private Sub cboLastName_AfterUpdate()
Private Sub cmdClose_Click()
Private Sub Form_Unload(Cancel As Integer)

In the Declaration area of the Module, an Object variable D is defined for the Dictionary Object. The txtbox() array variable is declared for storing the Text Box names from the Form’s Detail Section and will be used for displaying the selected record’s field values.

The Dictionary Object is created in the Form_Load Event Procedure,

Immediately after instantiating the Dictionary Object, the EmployeesQ Recordset is open for adding records to the Dictionary Object. A Select Query is created to pick only selected fields of the Employees Table, rather than using the Employees Table directly.

The Rec() Variant Array is re-dimensioned for the number of fields in the record. Each field value is added to the Rec Variant Array element, and the whole array is inserted into the dictionary Object as a single Item (a record), with the Last Name field value as a unique Dictionary Key.

In the next stage of the code, the Dict_Employees Form’s Detail Section area is scanned to identify text boxes, and their names are saved into the textbox() array for use in the cboLastName_AfterUpdate() Event Procedure. The txtbox() Array was declared in the Global area of the Module.

When the user selects a name from the Combo Box, the Private Sub cboLastName_AfterUpdate() Event Procedure is executed. The Form's Normal View image is given below:

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

When the user selects Last Name from the cboLastName Combo Box, it is saved into the String Variable strD. The statement R=D (strD) reads the corresponding employee record field values array into the Variant Variable R.

We have not explicitly defined the Variant Variable R as an Array. But when we read an Array of values from a Dictionary Object Item into it, it automatically re-dimensions itself for the number of required elements and loads each field value into its elements.

The next two steps determine the Array Index range values.

Within the For ... Next Loop, the record values are displayed in the Form Text Boxes, using the test box names saved in the Form_Load() Event Procedure.

You may select other names from the Combo box to display their details on the Form. When you are ready to close the Form, click on the Exit Command Button.

When the user clicks on the Exit Command Button, the Form is closed. Before closing the Form, the Form_Unload Event is triggered, and the Dictionary Object is cleared from Memory.

Download the Demo Database.

Download Dictionary2003.zip

Download Dictionary2007.zip

LEARN MS-ACCESS TIPS AND TRICKS

Friday, March 29, 2019

Update Class Object Dictionary Item through Form

Introduction

Class Object Instances as Dictionary Items

The Form Module Event Procedures.

Retrieving the Dictionary Item

Editing Dictionary Item

Private Sub cmdDelete_Click()

Deleting Dictionary Item

Saving Data from the Dictionary Object.

The Form's Class Module Code.

Perform a Demo Run.

Downloads

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

Tuesday, March 19, 2019

Add Class Objects as Dictionary Items

Introduction.

The ClassObjInDictionary() Procedure Code.

The VBA Code Line by Line.

Take a Trial Run With the Following Changes in the Code

Why has it happened this way?

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

Sunday, March 10, 2019

Display Records from Dictionary to Form

Introduction.

The Employees Table for Sample Data.

The Sample Form with a Few Textboxes and a Combo Box.

The Form's Class Module VBA Code.

How it All Works Together.

Download the Demo Database.

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

About Me

Statcounter