Subform control event (1 Viewer)

way2bord · Jun 21, 2013

I have a listbox on a subform (or a subform within a subform).

When it is clicked I want all other listboxes to unselect.

There may be listboxes on the mainform, on other subforms, on other subforms of subforms.

I imagine I'm looking at a recursive function of some sort, but I'm not entirely sure of syntax to identify parent and children forms...help?

pseudocode so far:
loop all controls
if control = listbox, unselect all
if control = subform - recurse: loop all subform controls
if control = parent... err... Fail.

ChrisO · Jun 21, 2013

Interestingly peculiar: the List Box seems to need to be passed as an Object not as a Control…

Behind a Form or Subform:-

Code:

Private Sub List0_AfterUpdate()
    
    ClearListBoxes Exception:=Me.List0
    
End Sub

(We can push it to the Property Sheet if required but that doesn’t matter at this stage.)

In a standard module:-

Code:

Option Explicit
Option Compare Text


Public Function ClearListBoxes(ByRef Exception As Object)

    [color=green]' Move up to the outer most Active Control's Form Parent.
    ' Pass the List Box Exception as an Object.
    ' Process all subforms top down.[/color]
    ResetAllListBoxes Screen.ActiveForm, Exception

End Function


Private Sub ResetAllListBoxes(ByRef frmForm As Form, _
                              ByRef Exception As Object)
    
    Dim ctl As Control
    
    [color=green]' Do this Form.[/color]
    ResetListBoxes frmForm, Exception
    
    [color=green]' Recursively process all subforms.[/color]
    For Each ctl In frmForm
        If ctl.ControlType = acSubform Then
           [color=green] ' Do the Form in the subform control.[/color]
            ResetAllListBoxes ctl.Form, Exception
        End If
    Next ctl

End Sub


Private Sub ResetListBoxes(ByRef frmForm As Form, _
                           ByRef Exception As Object)

    Dim lngRow As Long
    Dim obj    As Object
    
    For Each obj In frmForm
        If obj.ControlType = acListBox Then
            [color=green]' Check the Object address against the Exception address.[/color]
            If ObjPtr(obj) <> ObjPtr(Exception) Then
                [color=green]' Reset the List Box Object.
                ' Remove one at a time in case of Multi Select.[/color]
                For lngRow = 0 To obj.ListCount - 1
                    obj.Selected(lngRow) = False
                Next lngRow
            End If
        End If
    Next obj

End Sub

Just be careful as to how the Objects are defined.

Chris.

way2bord · Jun 21, 2013

Looks very similar to what I had - I needed that push up to the top level form though. Perfect - thanks!

ChrisO · Jun 23, 2013

If you want to automatically set up the handlers in the Main Form and all subforms then you can place the following in the Main Form’s Load event directly in the Property sheet:-

=SetHandlers([Form])

For this to work, the Main Form and subforms do not need their Class Module to exist. Of course, if the Class Module is required for other event handling then it might be required.

That line of code then calls this:-

Code:

Public Function SetHandlers(ByRef frm As Form)
    Dim ctl As Control
    
    For Each ctl In frm
        If ctl.ControlType = acListBox Then
            [color=green]' Set the Mouse Up handler.[/color]
           ctl.OnMouseUp = "=ClearListBoxes([" & ctl.Name & "])"
        End If
    Next ctl

    [color=green]' Recursively set handlers in all subforms.[/color]
    For Each ctl In frm
        If ctl.ControlType = acSubform Then
            [color=green]' Do the Form in the subform control.[/color]
            SetHandlers ctl.Form
        End If
    Next ctl

End Function

That then calls the previous ClearListBoxes function and passes the Exception List Box as an Object.

So to recap:
If all that is required is to clear all List Boxes, except the passed List Box, then the main Form and all of its subforms do not require a Class Module and their Has Module Property can be set to No.

Chris.

Subform control event (1 Viewer)

way2bord

Registered User.

ChrisO

Registered User.

way2bord

Registered User.

ChrisO

Registered User.

Similar threads

Users who are viewing this thread