How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation (1 Viewer)

[Uncle Gizmo]

Well, I haven't been so absorbed in programming since my early days, when I discovered you could assign objects to an object variable and do some wonderful code with it!

I had dabbled with class modules, but I never found a really useful use for a class module, until now!

I was going to say I couldn't have done it without ChatGPT, but I'm pretty sure I could have done it on my own. It would have just taken me a week instead of a couple of evenings!

I like walking through objects with a For next loop, and operating on a set of objects simultaneously.

I also have a situation on a form where I have a pair of objects, a TextBox and an OptionGroup. The TextBox provides the search term, and the OptionGroup provides the type of search you want to operate:

• Option 1: Wildcard search, ends with (e.g., 'abc*'): SQL: LIKE 'abc*'.
• Option 2: Wildcard search, contains (*abc*): SQL: LIKE '*abc*'.
• Option 3: Wildcard search, starts with ('*abc'): SQL: LIKE '*abc'.
• Option 4: Null search (IS NULL): SQL: [Field] IS NULL.
• Option 5: Exact match ('abc'): SQL: = 'abc' (no wildcards).

Naming Convention​

• My original code used a customised naming convention to match the controls. The later version now uses the Tag property. The Textbox Tag property contains the name of the field to be searched, it was a logical step to move away from the customised naming convention and add the search field name to both the text box and option group tag property to link each TextBox to its corresponding OptionGroup. This way, I know which pairs to operate on.

The code necessary to walk through the collections of the objects is quite straightforward once you get the hang of it, but every time you want to walk through the code, you need to re-use this code to select the right objects. Again, mostly copy and paste!

But then ChatGPT suggested I put them in a collection. Now I remember a thread a while back where Mark said that he often uses collections (If I recall correctly)... Further interrogation of ChatGPT led to the idea of building a class module to hold details of the object and a collection to retain the objects, and a custom property to fill the collection.

Benefits of the Class Module​

• The class module encapsulates both the TextBox and OptionGroup into a single entity, so I don't need to worry about handling them separately.
• Encapsulation allows me to treat the pair as a standalone object, making the code simpler and more maintainable.

---

Here is the Old Code to set the text boxes to an empty string and return the option groups to option one:​

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    'Find text boxes only
        If Left(ctlTxt.Name, intTxtPrefixLen) = conTxtPrefix Then 'that are prefixed conTxtPrefix
            ctlTxt.Value = ""   'Clear out the Text Box
        End If
    End If
    For Each ctlOpt In Me.Controls
        If ctlOpt.ControlType = acOptionGroup Then  'only select option groups
            If Right(ctlOpt.Name, Len(ctlOpt.Name) - intOptPrefixLen) = ctlTxt.Name Then 'where the name matches the text box
                ctlOpt.Value = 1                    'Set the leftmost option button
            End If
        End If
    Next ctlOpt
Next ctlTxt

Here is the New Code​

Dim ctrlCriteria As clsSearchCriteria

' Loop through the collection of SearchCriteriaCollection objects
For Each ctrlCriteria In Me.SearchCriteriaCollection
    ' Clear the TextBox value
    ctrlCriteria.TextBox.Value = ""  ' Clear the TextBox
   
    ' Reset the OptionGroup to the default option (leftmost button)
    ctrlCriteria.OptionGroup.Value = 1  ' Reset to the first option
Next ctrlCriteria

---

See how short, condensed, and tidy the code now becomes!

---

Further Benefits​

• Simplifies code reuse for multiple TextBox and OptionGroup pairs.
• No need to worry about handling TextBox and OptionGroup separately—it's all in one object via the class module.

 

[Gasman]

I know how you feel Tony.
I am even looking at refreshing myself in COBOL, but on a PC and not a mainframe, just to keep my brain working.

 

[CJ_London]

I’m playing with html/css/javascript

 

[Uncle Gizmo]

I know how you feel Tony.
I am even looking at refreshing myself in COBOL, but on a PC and not a mainframe, just to keep my brain working.

Hi Paul, I think you're onto something there! From what I hear, decent COBOL programmers are becoming pretty rare these days, especially with so many legacy systems still running in industries like banking. You could end up landing a really profitable gig if you refresh those skills. Smart move keeping the brain ticking over with COBOL!"

 

[Gasman]

No, at 70 years of age, I am now retired. Plus GCOS7 COBOL is different from IBM/ICL COBOL
I know, as I had to teach ICL people.
Mind you, I did love GCOS7.

I did a fair bit in financial systems for banks and building societies as you say.

 

[Uncle Gizmo]

I’m playing with html/css/javascript

Hi Chris,
I noticed your recent post where you built an HTML interface for Microsoft Access, interesting stuff! I'd love to hear more about what you're doing with it. If you could keep us updated on your progress, that would be awesome! Or maybe it's time for another pint or two, and a chat, before the weather gets even worse!

 

[Uncle Gizmo]

I'm thinking two plus two makes four...?
Both of you seem to have ventured into some interesting coding projects, stepping outside your usual comfort zones. I wonder if it's thanks to the helpfulness of Chatty, or in Paul's case, maybe he's finally had enough of jigsaw puzzles!

 

[Gasman]

Well TBH I was looking at doing a free course with Rocket (which used to be MicroFocus apparently), but having a devil fo a job getting the visual studio that is mentioned in the course. :-( So much so, I am thinking 'stuff it' .

 

[Uncle Gizmo]

visual studio

Do you mean visual studio code, the Microsoft IDE?

 

[Gasman]

Yes. I have a version on my laptop, which is nothing like the version in the course.
I am going to go looking again when I have time.

 

[cheekybuddha]

Do you mean visual studio code, the Microsoft IDE?

Just being pedantic: Visual Studio != Visual Studio Code

Visual Studio - grossly bloated IDE that is Windows only

Visual Studio Code - text editor on steroids that can be used as a basic IDE with appropriate plugins, and is cross-platform.

 

[Uncle Gizmo]

to keep my brain working

I have been meaning to ask how you got on with that "OK Google" monstrosity?

Are you finding it a help? Or did it drive you mad, as it did me and my dog?

 

[Gasman]

Oh, I bought a few more, before moving on to Alexa's.
They all sit side by side like cousins.

 

[Pat Hartman]

I am even looking at refreshing myself in COBOL, but on a PC and not a mainframe, just to keep my brain working.

The last COBOL project I did was to create a CICS application using Microfocus COBOL, CICS, and IMS software on my PC. I created it for a client who was leasing the software to big corporations who would run it on their mainframe. The software tools were pretty impressive.

The software allowed the corporations to create service level agreements between their IT departments and the various user departments. The software would calculate the plan vs actuals. It also sent summaries to the mother ship and my client would produce a report of all the software users experiences so the IT departments could see how they were doing against other similar companies.

 

[MarkK]

If you are starting out working with custom classes, note this debugger setting...

If this is not set, the debugger will not break at the error in your custom class, it will break at the line making the call in the consumer.