Search code examples
vbaformsms-accesstogglebutton

How do I efficiently pair Toggle Buttons and Textboxes in Access Form?


I know the title is a bit confusing so let me make myself as clear as possible.
In an Access form (2010), I have a set of text fields meant to contain dates. Those fields are all optional, the user can fill in any number of dates. To make it more user-friendly, I want to associate each field to a toggle button. Then I want 2 things to happen :

On CURRENT event :

  • If a text field has a value, then it is visible and the toggle button associated with it is pressed.
  • If a text field is empty, then it is not visible and the toggle button isn't pressed.

On CLICK of a toggle button :

  • If the text field associated with it has a value, then this field gets cleared (and invisible) and the toggle button gets de-pressed ;
  • If the text field associated with it is empty, then the focus is set on it and the toggle button gets pressed (and stay that way if a value is entered in the text field, otherwise everything gets back the way it was, invisible and unpressed).

So far I've achieved the first step by setting two collections of controls based on some examples I found online. So, when the form is loaded, I call this :

  Private mcolGroupTxToggle As New Collection
  Private mcolGroupBuToggle As New Collection

  Private Sub InitializeCollections()
    Dim ctl As Control

    If mcolGroupTxToggle.Count = 0 Then
       For Each ctl In Me.Controls
         If ctl.Tag = "txtoggle" Then
            mcolGroupTxToggle.Add ctl, ctl.Name
         End If
       Next ctl
       Set ctl = Nothing
    End If

    If mcolGroupBuToggle.Count = 0 Then
       For Each ctl In Me.Controls
         If ctl.Tag = "butoggle" Then
            mcolGroupBuToggle.Add ctl, ctl.Name
         End If
       Next ctl
       Set ctl = Nothing
    End If

  End Sub

And on Form_Current event, I call that :

  Private Sub OnLoadToggles(mcol As Collection, pcol As Collection)
    Dim ctl As Control
    Dim btn As Control
    Dim strBtn As String

    For Each ctl In mcol
    'Every button has the same name than the textbox + "b"
    strBtn = ctl.Name & "b"

        For Each btn In pcol
        If btn.Name = strBtn Then
            If IsNull(ctl) Then
                ctl.Visible = False
                btn.Value = False
            Else
                ctl.Visible = True
                btn.Value = True
            End If
        End If
        Next btn

    Next ctl
    Set ctl = Nothing

  End Sub

Everything works well so far, but I'm not sure that's the best way to do it and I figured I would need to repeat some lines in step 2.
Making the distinction between text boxes and buttons in the procedure seems weird, I feel like it should be done prior so that I don't have to do it in every procedure. I also feel like it would be better to loop through each pair of controls (text + button) instead of each control in both collections.

Basically, I'm wondering if it would be (1) better and (2) possible to have something as simple as this :

Private Sub OnLoadToggles(Collection of pairs)
for each [pair of txt and btn]
  if isnull(txt) Then
    txt.visible = false
    btn.value = false
  else
  ...
  end if
...

My guess is that I would need to make a public sub where I set a collection of pairs of buttons and text fields based on their tags (there are other controls in my form that need to be left alone) and names, but I'm not sure how, I'm a beginner in VBA.

Any suggestions please ?

-- Edit step 2 --

Thanks to Andre's answer the second part was easier than I thought. I've updated my sample database. So on click events I call this :

Private Sub ClickToggles()
    Dim ctl As Control
    Dim btn As Control
    Dim strBtn As String
    Dim strCtl As String

    strBtn = Me.ActiveControl.Name
    strCtl = Left(strBtn, Len(strBtn) - 1)
    Set ctl = Me(strCtl)
    Set btn = Me(strBtn)

        If IsNull(ctl) Then
            btn.Value = True
            ctl.Visible = True
            ctl.Enabled = True
            ctl.SetFocus
        Else
            ctl.Value = ""
            btn.Value = False
            ctl.Visible = False
        End If

End Sub

It's not perfect but it works. Probably not a good idea to clear the data at this point because misclicks may happen. It would be better to loop through the textboxes right before saving the form and clear values of invisible and/or disabled controls from the collection. I might to that later.
I had to add the .enabled property next to the .visible one because on lostfocus events I was getting an error saying the control was still active so it couldn't make it not visible.

Right now I'm more concerned about the amount of click and lost focus events. I'd rather have some public functions and event handlers dealing with it but it's getting too complicated for me. I'll get back to it when I know more about... everything.

Suggestions are still welcome anyway =) !


Solution

  • Since your control pairs are "paired" by their name anyway, you don't need any fancy constructs, or even the second collection. Just address the matching control directly by its name.

    Instead of using If/Else for setting boolean properties, it is usually easier to assign a variable to the property.

    Private Sub OnLoadToggles(mcol As Collection)
    
        Dim ctl As Control
        Dim btn As Control
        Dim strBtn As String
        Dim bShowIt As Boolean
    
        For Each ctl In mcol
            'Every button has the same name than the textbox + "b"
            strBtn = ctl.Name & "b"
            ' If you have the control name, you can get the control directly:
            Set btn = Me(strBtn)
    
            ' Using a variable you don't need If/Else
            bShowIt = Not IsNull(ctl.Value)
    
            ctl.Visible = bShowIt
            btn.Value = bShowIt
    
        Next ctl
    
        ' Note: this is not necessary for local variables - they go automatically out of scope
        Set ctl = Nothing
    
    End Sub