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

[Gasman]

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

View attachment 116654

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.

Thank you Mark. ATM, an error stops me even closing the form with @cheekybuddha's code, so I have to kill Access in task manager.

 

[MarkK]

One thing I do recall arguing in favour of a few times...
• Rather than manage a subset of controls by setting the Tag property in design view, and then enumerating the form's entire controls collection to work with them, do something like...

Private cts_ As VBA.Collection

Property Get MyCtlSubset() As VBA.Collection
    If cts_ Is Nothing Then Set cts_ = GetCollection(Me.cmdButton, Me.Text0, Me.Text2, Me.Text4)
    Set MyCtlSubset = cts_
End Property

Private Function GetCollection(ParamArray stuff()) As VBA.Collection
    Dim var
    Set GetCollection = New VBA.Collection
    For Each var In stuff
        GetCollection.Add var
    Next
End Function

• I prefer to list the subset's member controls in code so I can see them--and add and remove them immediately.
• Don't have to navigate design view, back and forth, find the control, open a property sheet, select the "Other" tab, and dig around.
• Just type "Me." and select the control by name from intellisense, and stuff it into some kind of enumerable.
I've even just done it like this...

Property Get MyCtrlArray() As Variant
    MyCtrlArray = Array(Me.cmdButton, Me.Text0, Me.Text2, Me.Text4)
End Property

Makes it easy to do stuff like...

Private Sub Form_Load()
    SetCtlSubset_Visible Me.MyAdminButtonSet, Sys.User.IsAdmin
End Sub

Private Sub Form_Current()
    SetCtrlSubset_Enabled Me.MyEditOrderButtonSet, IsNull(Me.OrderShipDate)
End Sub

Sub SetCtlSubset_Enabled(vEnumerable As Variant, State As Boolean)
    Dim var
    For Each var In vEnumerable
        var.enabled = State
    Next
End Sub

Sub SetCtlSubset_Visible(vEnumerable As Variant, State As Boolean)
    Dim var
    For Each var In vEnumerable
        var.visible = State
    Next
End Sub

And more than half this code (doesn't have to be) (shouldn't be) on every form

 

[Gasman]

Let me learn to crawl first, before even attempting to walk.
TBH, if I get this simple class excercise working, I think I will leave it at that. It is already hurting.

 

[Uncle Gizmo]

Rather than manage a subset of controls by setting the Tag property in design view, and then enumerating the form's entire controls collection

I didn't see much benefit in this approach until I created a monthly calendar form, which worked well... Then I decided that I could add two more copies of the "Months" buttons to make a three months display. In effect I tripled the number of controls that the code had to iterate through! This caused a noticeable slowdown in the form nothing serious but could be an issue, especially if I made a six-month or 12 month version. Hence I never made a version with more than three months, but I've just realised that Mark's approach might provide two opportunities for improvement, limit the number of controls accessed in other words speed it up and also simplify the code.... I better add that to the list of things I may never get around to!

 

[Uncle Gizmo]

Hence I never made a version with more than three months,

A better approach might be to alter my single months calendar so that it is a subform within a main form and get it operating there. Modify the code so it operates from the main form...

Now to add multiple months views I just duplicate the subform across the main form...

This approach could cater for multiple years displayed as 12 months, avoiding the issue of the maximum number of controls that you can display on a form...

 

[MarkK]

I better add that to the list of things I may never get around to!

Ha ha, I have lists like that.