ContactEditor - Excel VBA db app: Storage Library and MVP

Question

In the previous post, I presented my Storage Library. Here, I would like to focus on the integration of the library with the MVP component.

Functional class mapping

FigFunctionalClassMapping

First of all, the figure shows a new member of the Storage Library, the DataCompositeManager data manager, not discussed in the previous post. Since the user form displays a single record loaded from DataRecordModel and the data is loaded from the database into DataTabelModel, additional code is necessary to transfer the data between the two model classes. For this purpose, the DataCompositeManager manager has been introduced to take care of inter-model transfers.

---

DataCompositeManager

'@Folder "ContactEditor.Storage.Manager"
'@ModuleDescription "Composite class incorporating one Table and one Record model with backends. Record submodel is used to represent a row from the Table."
'@PredeclaredId
'@IgnoreModule ProcedureNotUsed
'@Exposed
Option Explicit
Private Type TDataCompositeManager
 RecordModel As DataRecordModel
 RecordStorage As IDataRecordStorage
 TableModel As DataTableModel
 TableStorage As IDataTableStorage
End Type
Private this As TDataCompositeManager
Private Sub Class_Initialize()
 Set this.RecordModel = New DataRecordModel
 Set this.TableModel = New DataTableModel
End Sub
Private Sub Class_Terminate()
 Set this.RecordModel = Nothing
 Set this.TableModel = Nothing
End Sub
Public Property Get Record() As Scripting.Dictionary
 Set Record = this.RecordModel.Record
End Property
Public Property Get RecordModel() As DataRecordModel
 Set RecordModel = this.RecordModel
End Property
Public Property Get TableModel() As DataTableModel
 Set TableModel = this.TableModel
End Property
Public Property Get FieldNames() As Variant
 FieldNames = this.TableModel.FieldNames
End Property
Public Property Get Values() As Variant
 Values = this.TableModel.Values
End Property
Public Property Get IDs() As Variant
 IDs = this.TableStorage.GetIds
End Property
Public Sub InitRecord(ByVal ClassName As String, ByVal ConnectionString As String, ByVal TableName As String)
 Set this.RecordStorage = DataRecordFactory.CreateInstance(ClassName, this.RecordModel, ConnectionString, TableName)
End Sub
Public Sub InitTable(ByVal ClassName As String, ByVal ConnectionString As String, ByVal TableName As String)
 Set this.TableStorage = DataTableFactory.CreateInstance(ClassName, this.TableModel, ConnectionString, TableName)
End Sub
Public Sub LoadDataIntoModel()
 this.TableStorage.LoadDataIntoModel
 this.RecordStorage.LoadDataIntoModel
End Sub
Public Sub SaveDataFromModel()
 this.RecordStorage.SaveDataFromModel
 this.TableStorage.SaveDataFromModel
End Sub
Public Sub SaveRecordDataToRecordStorage()
 this.RecordStorage.SaveDataFromModel
End Sub
Public Sub LoadRecordFromTable(ByVal RecordId As String)
 this.TableModel.CopyRecordToDictionary this.RecordModel.Record, RecordId
 this.RecordModel.RecordIndex = this.TableModel.RecordIndexFromId(RecordId)
 this.RecordModel.IsNotDirty
End Sub
Public Sub UpdateRecordToTable()
 this.TableModel.UpdateRecordFromDictionary this.RecordModel.Record
End Sub

The figure also shows the MVP components: ContactEditorModel (Model), ContactEditorForm (View), and ContactEditorPresenter (Presenter). ContactEditorModel encapsulates an instance of DataCompositeManager and exposes it.

---

ContactEditorModel

'@Folder "ContactEditor.Forms.Contact Editor"
'@IgnoreModule ProcedureNotUsed
'@Exposed
Option Explicit
Public Enum DataPersistenceMode
 DataPersistenceDisabled
 DataPersistenceOnApply
 DataPersistenceOnExit
End Enum
Private Type TContactEditorModel
 RecordTableManager As DataCompositeManager
 PersistenceMode As DataPersistenceMode
 SuppressEvents As Boolean
End Type
Private this As TContactEditorModel
Private Sub Class_Initialize()
 Set this.RecordTableManager = New DataCompositeManager
 this.SuppressEvents = False
End Sub
Private Sub Class_Terminate()
 Set this.RecordTableManager = Nothing
End Sub
Public Property Get RecordTableManager() As DataCompositeManager
 Set RecordTableManager = this.RecordTableManager
End Property
Public Property Get PersistenceMode() As DataPersistenceMode
 PersistenceMode = this.PersistenceMode
End Property
Public Property Let PersistenceMode(ByVal Mode As DataPersistenceMode)
 this.PersistenceMode = Mode
End Property
Public Property Get SuppressEvents() As Boolean
 SuppressEvents = this.SuppressEvents
End Property
Public Property Let SuppressEvents(ByVal Mode As Boolean)
 this.SuppressEvents = Mode
End Property

ContactEditorForm is Modeless, and it defines several custom events handled by the Presenter.

---

ContactEditorForm

'@Folder "ContactEditor.Forms.Contact Editor"
Option Explicit
'''' To avoid issues, populate ComboBox.List with array of strings,
'''' cast if necessary (ComboBox.List column elements used for
'''' ComboBox.Value must have the same type as ComboBox.Value,
'''' otherwise expect runtime errors and glitches.
Implements IDialogView
Public Event FormLoaded()
Public Event LoadRecord(ByVal RecordId As String)
Public Event ApplyChanges()
Public Event FormConfirmed()
Public Event FormCancelled(ByRef Cancel As Boolean)
Private Type TView
 Model As ContactEditorModel
 IsCancelled As Boolean
End Type
Private this As TView
Private Function OnCancel() As Boolean
 Dim cancelCancellation As Boolean: cancelCancellation = False
 RaiseEvent FormCancelled(cancelCancellation)
 If Not cancelCancellation Then Me.Hide
 OnCancel = cancelCancellation
End Function
Private Sub id_Change()
 If this.Model.SuppressEvents Then Exit Sub
 RaiseEvent LoadRecord(id.Value)
End Sub
Private Sub OkButton_Click()
 Me.Hide
 RaiseEvent FormConfirmed
End Sub
Private Sub CancelButton_Click()
 '@Ignore FunctionReturnValueDiscarded
 OnCancel
End Sub
Private Sub ApplyButton_Click()
 RaiseEvent ApplyChanges
End Sub
Private Sub UpdateDisabledRadio_Click()
 this.Model.PersistenceMode = DataPersistenceMode.DataPersistenceDisabled
End Sub
Private Sub UpdateOnApplyRadio_Click()
 this.Model.PersistenceMode = DataPersistenceMode.DataPersistenceOnApply
End Sub
Private Sub UpdateOnExitRadio_Click()
 this.Model.PersistenceMode = DataPersistenceMode.DataPersistenceOnExit
End Sub
Private Sub IDialogView_ShowDialog(ByVal viewModel As Object)
 Set this.Model = viewModel
 Me.Show vbModeless
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 If CloseMode = VbQueryClose.vbFormControlMenu Then
 Cancel = Not OnCancel
 End If
End Sub
Private Sub UserForm_Activate()
 InitializeId
 InitializeAge
 InitializeGender
 InitializeTableUpdating
 RaiseEvent FormLoaded
End Sub
Private Sub InitializeGender()
 Dim listValues() As Variant
 listValues = Array("male", "female")
 Gender.Clear
 Gender.List = listValues
End Sub
Private Sub InitializeAge()
 Dim listValues(18 To 80) As Variant
 Dim AgeValue As Long
 For AgeValue = 18 To 80
 listValues(AgeValue) = CStr(AgeValue)
 Next AgeValue
 
 Age.Clear
 Age.List = listValues
End Sub
Private Sub InitializeId()
 id.Clear
 id.List = this.Model.RecordTableManager.IDs
End Sub
Private Sub InitializeTableUpdating()
 UpdateDisabledRadio.Value = True
End Sub
Private Sub FirstName_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "FirstName", FirstName.Value
End Sub
Private Sub LastName_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "LastName", LastName.Value
End Sub
Private Sub Age_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "Age", Age.Value
End Sub
Private Sub Gender_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "Gender", Gender.Value
End Sub
Private Sub Email_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "Email", Email.Value
End Sub
Private Sub Country_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "Country", Country.Value
End Sub
Private Sub Domain_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "Domain", Domain.Value
End Sub

ContactEditorPresenter initializes the DataCompositeManager member of ContactEditorModel and initiates subsequent operations in event handlers.

---

ContactEditorPresenter

'@Folder "ContactEditor.Forms.Contact Editor"
Option Explicit
'@MemberAttribute VB_VarHelpID, -1
Private WithEvents view As ContactEditorForm
Private Type TPresenter
 Model As ContactEditorModel
 Dialog As IDialogView
End Type
Private this As TPresenter
Public Sub Show(ByVal TableBackEnd As String)
 Set view = New ContactEditorForm
 Set this.Dialog = view
 InitializeModel TableBackEnd
 
 '''' Loads data from the backends into the Model
 this.Model.RecordTableManager.LoadDataIntoModel
 
 this.Dialog.ShowDialog this.Model
End Sub
Private Sub ApplyChanges()
 this.Model.RecordTableManager.SaveRecordDataToRecordStorage
 Select Case this.Model.PersistenceMode
 Case DataPersistenceMode.DataPersistenceOnApply
 this.Model.RecordTableManager.UpdateRecordToTable
 this.Model.RecordTableManager.SaveDataFromModel
 Case DataPersistenceMode.DataPersistenceOnExit
 this.Model.RecordTableManager.UpdateRecordToTable
 Case DataPersistenceMode.DataPersistenceDisabled
 Exit Sub
 End Select
End Sub
Private Sub view_ApplyChanges()
 ApplyChanges
End Sub
Private Sub view_FormLoaded()
 LoadFormFromModel
End Sub
Private Sub LoadFormFromModel()
 this.Model.SuppressEvents = True
 
 Dim FieldName As Variant
 Dim FieldIndex As Long
 Dim FieldNames As Variant: FieldNames = this.Model.RecordTableManager.FieldNames
 For FieldIndex = LBound(FieldNames) To UBound(FieldNames)
 FieldName = FieldNames(FieldIndex)
 view.Controls(FieldName).Value = CStr(this.Model.RecordTableManager.RecordModel.GetField(FieldName))
 Next FieldIndex
 this.Model.SuppressEvents = False
End Sub
Private Sub view_LoadRecord(ByVal RecordId As String)
 If this.Model.RecordTableManager.RecordModel.IsDirty Then
 Dim SaveChanges As Boolean
 SaveChanges = MsgBox("Apply unsaved changes?", vbYesNo + vbExclamation + vbDefaultButton2)
 If SaveChanges Then ApplyChanges
 End If
 this.Model.RecordTableManager.LoadRecordFromTable RecordId
 LoadFormFromModel
End Sub
Private Sub view_FormCancelled(ByRef Cancel As Boolean)
 'setting Cancel to True will leave the form open
 Cancel = MsgBox("Cancel this operation?", vbYesNo + vbExclamation) = vbNo
 If Not Cancel Then
 ' modeless form was cancelled and is now hidden.
 ' ...
 Set view = Nothing
 End If
End Sub
Private Sub view_FormConfirmed()
 'form was okayed and is now hidden.
 '...
 If this.Model.PersistenceMode <> DataPersistenceDisabled Then
 this.Model.RecordTableManager.UpdateRecordToTable
 this.Model.RecordTableManager.SaveDataFromModel
 Else
 this.Model.RecordTableManager.SaveRecordDataToRecordStorage
 End If
 Set view = Nothing
End Sub
'@Description "Instantiates model and binds it to the desired backends."
Private Sub InitializeModel(ByVal TableBackEnd As String)
 Set this.Model = New ContactEditorModel
 
 Dim ClassName As String
 Dim TableName As String
 Dim ConnectionString As String
 
 '''' Binds TableModel to its backend
 Select Case TableBackEnd
 Case "ADODB"
 ClassName = "ADODB"
 TableName = "Contacts"
 ConnectionString = "sqlite:"
 Case "Worksheet"
 ClassName = "Worksheet"
 TableName = "Contacts"
 ConnectionString = ThisWorkbook.Name & "!" & Contacts.Name
 Case "CSV"
 ClassName = "CSV"
 TableName = "Contacts.xsv!sep=,"
 ConnectionString = ThisWorkbook.Path
 End Select
 this.Model.RecordTableManager.InitTable ClassName, ConnectionString, TableName
 
 '''' Binds RecordModel to its backend
 ClassName = "Worksheet"
 TableName = vbNullString
 ConnectionString = ThisWorkbook.Name & "!" & ContactBrowser.Name
 this.Model.RecordTableManager.InitRecord ClassName, ConnectionString, TableName
End Sub

Answer 1

I have realized that it would be preferable to place all operations involving the DataCompositeManager instance within one module (Presenter). I recall that the reason I also kept such operations within CONTROL_Change handlers in the form code-behind was that I thought it would require a "WithEvents" module-level attribute in the Presenter for each control instance. I just realized that I could keep CONTROL_Change handlers in the form code-behind and pass control_name/new_value pairs to Presenter for further processing.

Within ContactEditorForm, I should have defined a custom form event:

Public Event FieldChanged(ByVal FieldName As String, ByVal NewValue As Variant)

and changed handlers

Private Sub FIELDNAME_Change()
 If this.Model.SuppressEvents Then Exit Sub
 this.Model.RecordTableManager.RecordModel.SetField "FIELDNAME", FIELDNAME.Value
End Sub

to (see id_Change handler)

Private Sub FIELDNAME_Change()
 If this.Model.SuppressEvents Then Exit Sub
 RaiseEvent FieldChanged("FIELDNAME", FIELDNAME.Value)
End Sub

Then, within ContactEditorPresenter, I could do

Private Sub view_FieldChanged(ByVal FieldName As String, ByVal NewValue As Variant)
 this.Model.RecordTableManager.RecordModel.SetField FieldName, NewValue
End Sub