New to classes VBA Access

Question

I have been working on a project and have multiple tick boxes (25) and multiple labels in a form that are names SC1, SC2...SCN and Lbl1, Lbl2...LblN depending on a recordset. When I click the tickbox I want the label beside it to display some information, see below -

Private Sub SC1_Click()
   If (Me!SC1) = True Then
    Form.Controls("Lbl1").Caption = ("Completed by " & (Environ$("Username")))
    Form.Controls("Lbl1").ForeColor = vbGreen
Else
    Form.Controls("Lbl1").Caption = ("Please tick if Complete")
    Form.Controls("Lbl1").ForeColor = vbBlack
End If

End Sub

My issue is I can't change the number in the Sub name so I would have to create multiple sub procedures. I think if I created a class for the tick box this would change but I am not sure how I can set up the class. I have tried the below class template but am not sure where I can change the property values in order to reach my goal. I am not sure why you would have both get and set properties in one class. Any help on this is greatly appreciated.

Option Compare Database
Option Explicit


Private pName As String
Private pCaption As String
Private pVisiblity As Boolean
Private pValue As Boolean


Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = Value
End Property

Public Property Get Caption() As String
    Caption = pCaption
End Property
Public Property Let Caption(Value As String)
    pCaption = "Please Tick Box if complete"
End Property

Public Property Get Visibility() As Boolean
    Visibility = pVisibility
End Property
Public Property Let Visibility(Value As Boolean)
    pVisibility = True
End Property

Public Property Get Value() As Boolean
    Value = pValue
End Property
Public Property Let Value(Value As Boolean)
    pValue = True
End Property

Answer 1

There are two parts to creating and linking form controls to custom support objects (classes). In your case

Class Module: clsMyCheckbox

Option Explicit
Option Compare Database
Public WithEvents chkBox As CheckBox
Public chkLabel As Label

Private currentUser As String

Private Sub chkBox_Click()
    If chkBox.Value = True Then
        chkLabel.Caption = "Completed by " & currentUser
        chkLabel.ForeColor = vbGreen
    Else
        chkLabel.Caption = "Please tick if Complete"
        chkLabel.ForeColor = vbBlack
    End If
End Sub

Private Sub Class_Initialize()
    currentUser = Environ$("Username")
End Sub

And in your form module:

Option Explicit
Option Compare Database

Private localCheckboxes As New Collection

Private Sub Form_Load()
    '--- find all the checkboxes on the form and create a
    '    handler object for each one
    Dim ctl As Control
    Dim chknum As String
    Dim cbObj As clsMyCheckbox
    Dim chkLbl As Label
    For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox Then
            '--- you can filter by name if needed
            If ctl.Name Like "SC*" Then
                chknum = Right(ctl.Name, Len(ctl.Name) - 2)
                Set chkLbl = Me.Controls.Item("Lbl" & chknum)
                chkLbl.Caption = "initialized" 'optional during form load
                Set cbObj = New clsMyCheckbox  'class object for this checkbox
                Set cbObj.chkBox = ctl         'link the control to the object
                Set cbObj.chkLabel = chkLbl    'link the label too
                '--- add it to a local store so the object persists
                '    as long as the form is open
                localCheckboxes.Add cbObj
            End If
        End If
    Next ctl
End Sub

Private Sub Form_Unload(Cancel As Integer)
    '--- guarantee the objects are destroyed with the form
    Set localCheckboxes = Nothing
End Sub

Answer 2

I think you are going the wrong way. In Access you can't really derive your own classes for GUI control and use them on the form. For your problem, you basically have three options:

1. Use the default event handlers and call one custom function from each. This will improve your situation a little.
2. Use one custom event handler for all checkboxes, instead of the default event-handlers.
3. Use a class and attach an instance to each of the checkboxes you use. The class can then recieve any event from the checkbox. This is powerful but you will still need to register your class with each control and hold all you instances somewhere for this to work.

For your problem, I'd go with solution 2:

First, write a custom event handler like this in your Form-module:

Private Function chkClick(sName As String)
    Debug.Print "Clicked: " & sName
    Me.Controls(sName).Controls(0).Caption = "x"
End Function

Next, enter design mode of you form and go to all checkboxes. In Checkbox "SC1", you go to the "OnClick" event and enter =chkClick("SC1") as event handler instead of [Eventprocedure]. Make sure you use the correct name of the control as the parameter of the function.

Congratulations! From now on, all your checkboxes will call the same event-handler and pass their name. Since the label of a checkbox is its associated control, you get to that label from the checkbox via .Controls(0), meaning the first "sub"-control of the checkbox. This way, you don't need to know the name of the associated label at all!