Update Class Object Dictionary Item through Form ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

Last week we did a trial run for adding/retrieving Class Objects into/from Dictionary Object Items. One important point we have highlighted there was that each Class Object Item added to the Dictionary Object must be a new instance of the Class Object, every time.

This is not peculiar to Class Object or Dictionary Object alone. This method must be followed to avoid inadvertent loss of important data while adding Objects to a Collection, Dictionary, or to the Array of an object itself. A review of the procedure, in brief, is given below:@@@

Create a temporary Object instance (Set tmpObj = New Object)
Assign Values to the temporary Object instance’s properties.
Add the temporary Object instance, as Dictionary Object Item or Collection Object Item or as the Object’s declared Array element.
Remove the temporary Object instance (Set tmpObj = Nothing) from memory.
To add more items repeat steps 1 to 4.

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 Dictionary Object, through Ms-Access Form. We worked with Form and Dictionary Object about two weeks back, for adding and retrieving simple items in 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 the 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.
Next, we need a Form with the following design to manage the data of Dictionary Object.
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:

As the Form control names suggest we can key in Property Values of ClsArea Class Object and add the Class Object to Dictionary Object, as its Item. The strDesc Property Value will be used as Dictionary Key. Ensure that while Keying in data the Description Field Value must be unique among all items, otherwise, it will not accept in the Dictionary Object and will end up with Errors. I have avoided extensive validation checks in the Code, to maintain the code as simple as possible.

A particular class Object’s property values can be retrieved from Dictionary Object, using the Key-Value selected from the cboKey Combo Box, edit the values on the Form and Update the changes back into the Dictionary. If it is an unwanted item, then it can be deleted from Dictionary Object.

The Form Module Event Procedures.

There are several Event Procedures behind the Form’s Class Module and a brief explanation is given under each Procedure given 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 at 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 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, then the data entry fields are not cleared, 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, 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 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 Combo Box with the Dictionary Object Key, from Class Object's Description (strDesc) Value.

The Dictionary Items Count is displayed on ItemCount Text Box.

The focus is set in the strDesc field, by which the text boxes are cleared to enter new values to add.

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 may retrieve an Item from Dictionary Object and display the details on Form, by selecting the Dictionary Key Item from the Combo Box. The Add Command Button will be disabled. The strDesc Field will be disabled. Will unlock it only if you click on the Edit Button.

If you select an invalid Key (the Key value changed during earlier edit operations) from Combobox, then an error message: 'Item for Key: XXXX. Not Found' will be displayed, indicating that the item doesn't exist in 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 brought from Dictionary to Form can be edited by clicking on the Edit Command Button. The Caption of the Edit Command Button will change to Update when you click on it.

Make any changes, including the Description (if necessary) then Click Edit Button again (with Caption Update) second time to update the change back into Dictionary Object.

If strDesc (The Key value in the Dictionary) is changed, then the Dictionary Object Item's corresponding old Key is updated with the new Description Value. The Combobox will be updated with the new Key, but, the old Key is not deleted from the ComboBox.

Data fields are cleared, Add Command Button is enabled, 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 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 ClsArea Class’s property values from 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 on the Exit Command Button, rather than the ‘Save to Table’ Command Button, here also you are reminded to save the data. If you don't want to save then you may select the appropriate option to close the Form, without saving the data in Table.

There are a few common Sub-Routines, called from more than one event Procedure and their Codes are added to the Full listing of the Code given below.

Private Sub initFields() all text boxes on the Form are cleared when called from Event Procedures.

Private Sub comboUpdate(ByVal stDesc As String) Called from cmdAdd_Click() and cmdEdit_Click() Event Procedures.

Private Sub Save2Table() Called from cmdSave_Click() and cmdExit_Click() Event Procedures.

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 Form frmDictionary and try out adding a few entries into the Dictionary Object.
Select an item from the Combo Box to display the Property Values of the corresponding Class Object.
Click on Edit Command Button, and make some changes to the Description, Length, and Width Values.
Click on Edit Command Button again (with the caption Update) to write the changes back into the Dictionary Object. If you click on the Combobox you can see the item with the changed description and the old description of the item.
Select the old description from the Combo Box. You will get an error message saying that the item doesn’t exist because the Dictionary Key of the Item is updated with a changed value.
Select an item from the Combo Box and Click on the Delete Command Button to delete the item displayed on the Screen.

Tried to keep the Event Procedure Code simple, avoiding Validation checks on input values, error trap routines, or safeguarding other side effects of user activities on the Form.

You may improve the Code if the need arises and use it for your own Projects.