Originally Posted by
davegri
To each his own. There are 27 records in the table. The collection requires you to type in exactly the Alias for the desired value. The UDT will supply the entire 27 aliases in the dropdown to choose from.
Hi Dave!
I agree with the useful feature of Intellisense but, for this case, the benefits of the UDT ends here. Your code in StartUp() demands a table structured as spreedsheet with 27 (Colin says up to 160) fields rather than records and you can’t refers to the members of the UDT in loops, so, you need also a large and boring statement of School.MemebrName= !strVarName, and, finaly, you have only one instance of this object for each session.
The purpose of the tblProgramConstants table (I'd prefer tblProgramParameters) is to give to the user the ability to change the parameters of a completed project via the UI, which is a brilliant idea. On the programming level, the "Collection way" simulates the Environ() function, and the notation CollectionName(“MemberName”) is very familiar to programmers. In some cases, this notation could be a big advantage which lacks of VBA: the indirectly reference to a variable.
Imagine this scenario: You have passed all program parameters in a collection named Param and you have a generic procedure that loads the defaults of a form of a multilanguage application:
Code:
Public Sub LoadFormDefaults(frm As Form)
Dim ctrl As Control
Dim strParam As String
On Error Resume Next
If Not Param Is Nothing Then
For Each ctrl In frm.Controls
'Set the papameter name to the name of the control without its prefix ("txt","cbo", "lbl" etc).
'E.g. the textbox [txtUserName] takes the value of Param("UserName").
'The caption of the label [lblUserName] takes the value of Param("UserNameCaption").
strParam = Mid(ctrl.Name, 4)
Select Case TypeName(ctrl)
Case "TextBox", "ListBox", "ComboBox", "CheckBox", "OptionGroup" ',...
ctrl = Param(strParam) 'Pass the value of the parameter to control
Case "Label"
ctrl.Caption = Param(strParam & "Caption") 'Set the caption of the label
Case Else
End Select
Next ctrl
'Set the caption of the form
If Left(frm.Name, 4) = "sfrm" Then
strParam = Mid(frm.Name, 5)
ElseIf Left(frm.Name, 3) = "frm" Then
strParam = Mid(frm.Name, 4)
Else
strParam = ""
End If
If Len(strParam) Then
strParam = strParam & "Caption"
frm.Caption = Param(strParam)
End If
On Error GoTo 0
End If
End Sub
In any form’s Load event could exists a line of code like this:
Code:
Private Sub Form_Load()
LoadFormDefaults Me
'...
'...
Imagine how many lines of code would save with such a practice like this and how flexible is.
An inverse procedure in Unload event of the form, would change the values of the collection (defaults) for the current session, according to user’s preferences.
But all methods work so each developer can use whichever they feel most comfortable with
Sure Colin, that’s generally true, but, in technique, some issues are beyond of our personal preferences.