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

[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.

 

[jwcolby54]

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.

I can keep your brain working!

 

[KitaYama]

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.

Can you or @MarkK share a simple file (or a link to the thread) to show how you did it?

I normally use a property in my forms instead of a collection.

Thanks.

 

[cheekybuddha]

Can you or @MarkK share a simple file (or a link to the thread) to show how you did it?

See here for a current thread

 

[Gasman]

I can keep your brain working!

Well, I gave up on the Cobol. The downloads were huge.

 

[Uncle Gizmo]

Can you or @MarkK share a simple file (or a link to the thread) to show how you did it?

Looking back through my notes I found a record of a conversation I had with chatty, and it was nearly a year ago so bear in mind, chatty's faculties weren't quite so good then!

You said:

The following code loads a collection of option groups and a collection of textbooks is into a collection...


Private opts_ As VBA.Collection
Private srch_ As VBA.Collection

Private Property Get OptionGroups() As VBA.Collection
' lazy loads options group controls into a collection
If opts_ Is Nothing Then
Set opts_ = New VBA.Collection
opts_.Add Me.opttxtsDepartment
opts_.Add Me.opttxtsDescription
opts_.Add Me.opttxtsProduct
End If
Set OptionGroups = opts_
End Property

Private Property Get SearchTextBoxes() As VBA.Collection
' lazy loads search textboxes, keyed by name
If srch_ Is Nothing Then
Set srch_ = New VBA.Collection
srch_.Add Me.txtsDepartment, "Department"
srch_.Add Me.txtsDescription, "Description"
srch_.Add Me.txtsProduct, "Product"
End If
Set SearchTextBoxes = srch_
End Property

It was written by somebody else who is obviously a much better programmer than me!

But I'm puzzled as to why the option groups only reference the option object:- opts_.Add Me.opttxtsDepartment

But in the case of the text boxes there are like two parameters?

srch_.Add Me.txtsDepartment, "Department"

Can you explain why Mark has done that

ChatGPT said:

No problem at all! Let me break it down for you in a way that makes sense.

In the code, Mark is working with two different collections: one for Option Groups (opts_) and another for Search TextBoxes (srch_). The key distinction is how these collections are being used, which affects why he writes them differently.

1. Option Groups (opts_):

When adding items to the opts_ collection, Mark is just adding the controls themselves (i.e., Me.opttxtsDepartment, Me.opttxtsDescription, etc.). These are Option Group controls that don’t need to be "keyed" by any particular identifier. In other words, he is simply adding these controls to the collection without any label or key because he doesn't need to reference them by a name later. He's only storing the controls.

vba
Copy code
opts_.Add Me.opttxtsDepartment ' Adds the Option Group control itself


In this case, Mark expects to work with these option controls directly, probably by looping through them or accessing them one by one, without needing to look them up by a specific name.

2. Search TextBoxes (srch_):

In the case of the search textboxes, Mark adds both the control (Me.txtsDepartment) and a key ("Department"). The key is a string that represents an identifier for each textbox control, making it easier to access a specific textbox later by using this key.

vba
Copy code
srch_.Add Me.txtsDepartment, "Department" ' Adds the TextBox with a "Department" key


Here’s why the two parameters are needed in this case:

The first parameter (Me.txtsDepartment) is the textbox control itself, just like in the Option Groups collection.
The second parameter ("Department") is the key, a string that represents the specific textbox. This allows Mark to retrieve a textbox from the collection by name later on, like this:
vba
Copy code
Set txtBox = srch_("Department")


So, the key difference between the two collections is that the Search TextBoxes collection (srch_) is keyed, meaning you can retrieve textboxes by their names (like "Department", "Product", etc.), while the Option Groups collection (opts_) is just a list of controls with no names attached.

Why the Difference?

For Option Groups: Mark probably doesn't need to refer to them by name. He might iterate through them or treat them as a list, so a simple collection of controls works just fine.

For Search TextBoxes: Mark might need to refer to a specific textbox by its name (e.g., "Department"), so he uses a keyed collection to make this easier and more efficient.

Takeaway:

Simple Collection (opts_): Use when you just need a list of controls without needing to identify them by name.
Keyed Collection (srch_): Use when you want to access specific controls using a unique identifier (like "Department").

I hope this clears it up! Feel free to ask more if you’re still curious about any part of this.

You said:
Where's your help a few days ago I did it in a slightly different way:-

Private collTextBoxes As Collection
Private collOptionGroups As Collection


And the form load event fills the collections

Private Sub Form_Load()
Set collTextBoxes = New Collection
Set collOptionGroups = New Collection

' Populate collections here
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox And Left(ctrl.Name, Len(conTxtPrefix)) = conTxtPrefix Then
collTextBoxes.Add ctrl
ElseIf TypeOf ctrl Is OptionGroup And Left(ctrl.Name, Len(conOptPreFix)) = conOptPreFix Then
collOptionGroups.Add ctrl
End If
Next ctrl

Call fFillSubFrm
End Sub 'Form_Load

So I was wondering could that idea here loading the collections in the form load event could that be transferred into the property and do that within the property?

Now I've just realized that means that the property would probably have to load all the controls every time it was called if we do it that way?

Unless of poor course and less of course the property can be made to keep its value after its first iterations through the code?

Does that make sense
ChatGPT said:

Yes, that absolutely makes sense! Let's walk through the idea of moving the logic from your Form_Load event into the property while keeping the collection persistent after its first load. This is totally doable, and you're right to think that we can lazy load the collections into the property, just like in Mark's original approach. I'll explain how that would work.