Rediscovering the Joy of Programming
I haven’t been this absorbed in programming since my early days—back when I first discovered the magic of assigning objects to object variables and unlocking all kinds of possibilities in code.For years I dabbled with class modules, but I never found a truly practical use for them… until now.
I almost said I couldn’t have done it without ChatGPT, but the truth is I probably could have—it just would have taken me a week instead of a couple of evenings.
One of the things I enjoy most is walking through objects with a For…Next loop and operating on a set of them simultaneously. Recently, I had a situation on a form involving a pair of controls: a TextBox and an OptionGroup.
- The TextBox provides the search term.
- The OptionGroup defines the type of search:
- Option 1: Ends with → LIKE 'abc*'
- Option 2: Contains → LIKE '*abc*'
- Option 3: Starts with → LIKE '*abc'
- Option 4: Null → [Field] IS NULL
- Option 5: Exact match → = 'abc'
Naming Convention Evolution
Originally, I relied on a custom naming convention to match controls. Later, I realized it was far more logical to use the Tag property. By storing the field name in both the TextBox and OptionGroup tags, I could easily link each pair together.The code to walk through collections of objects is straightforward once you get the hang of it, but it often meant reusing the same boilerplate logic—lots of copy and paste.
Then came the breakthrough: ChatGPT suggested putting them into a collection. That reminded me of a thread I’d read years ago where Mark mentioned how often he used collections. Digging deeper, I realized I could go one step further: build a class module to hold the details of each object, store them in a collection, and expose a custom property to manage them.
Why the Class Module Works
- Encapsulation: The class module bundles the TextBox and OptionGroup into a single entity.
- Simplicity: I no longer have to handle them separately.
- Maintainability: The code becomes cleaner, shorter, and easier to reuse.
Old Code (Verbose)
Code:
Dim ctlTxt As Control
Dim ctlOpt As Control
Dim intTxtPrefixLen As Integer
Dim intOptPrefixLen As Integer
intTxtPrefixLen = Len(conTxtPrefix)
intOptPrefixLen = Len(conOptPreFix)
For Each ctlTxt In Me.Controls
If ctlTxt.ControlType = acTextBox Then
If Left(ctlTxt.Name, intTxtPrefixLen) = conTxtPrefix Then
ctlTxt.Value = ""
End If
End If
For Each ctlOpt In Me.Controls
If ctlOpt.ControlType = acOptionGroup Then
If Right(ctlOpt.Name, Len(ctlOpt.Name) - intOptPrefixLen) = ctlTxt.Name Then
ctlOpt.Value = 1
End If
End If
Next ctlOpt
Next ctlTxtNew Code (Elegant)
Code:
Dim ctrlCriteria As clsSearchCriteria
For Each ctrlCriteria In Me.SearchCriteriaCollection
ctrlCriteria.TextBox.Value = "" ' Clear the TextBox
ctrlCriteria.OptionGroup.Value = 1 ' Reset to first option
Next ctrlCriteriaThe Payoff
- Code is shorter, tidier, and easier to read.
- Reuse is effortless across multiple TextBox/OptionGroup pairs.
- Encapsulation means I can treat each pair as a single object.
Last edited by a moderator:
Takeaway:
