Assigning Tags to Bound Controls

[Kayleigh]

I have a function which requires a check of the tag of all bound controls in form, if the tag property is set to a specific string.
I have found this useful post for the second part but could not find a way to determine if the control is bound or not?
Would also be useful if I could test a form to find if it is bound. Is there a way to do this?

 

[Minty]

Check the value of it's control source?
If it is not null then it's bound to something.

 

[arnelgp]

you can check the control's ControlSource.
if it is not Blank and the Left() is not equal to "=",
then it is Bound:

Dim ctl As Control
For Each ctl In Me.Controls
    If TypeOf ctl Is TextBox Or _
        TypeOf ctl Is ComboBox Or _
        TypeOf ctl Is CheckBox Or _
        TypeOf ctl Is ListBox Then
        If Len(ctl.ControlSource) > 0 Then
            If Left$(ctl.ControlSource, 1) <> "=" Then
                'the control is bound
                Debug.Print ctl.Name, "bound"
            End If
        End If
    End If
Next

 

[Kayleigh]

Thank you. I like that idea. Would it be possible to write as a stand-alone function. Something like this:

'Check if control is bound
Public Function isBound(ctl As Control) As Boolean
    If TypeOf ctl Is TextBox Or _
        TypeOf ctl Is ComboBox Or _
        TypeOf ctl Is CheckBox Or _
        TypeOf ctl Is ListBox Then
        If Len(ctl.ControlSource) > 0 Then
            If Left$(ctl.ControlSource, 1) <> "=" Then
                'the control is bound
                Debug.Print ctl.Name, "bound"
                isBound = True
            Else
                isBound = False
                Debug.Print ctl.Name, "unbound"
            End If
        End If
    End If
End Function

 

[Pat Hartman]

If it is not null then it's bound to something.

Not quite. If it is not null AND the first character <> "=", then it is bound.

 

[isladogs]

Just out of interest, what is the purpose of the function that

requires a check of the tag of all bound controls in a form

 

[Kayleigh]

I am trying to automate the audit trail process. Like this thread.

 

[arnelgp]

'Check if control is bound
Public Function isBound(ctl As Control) As Boolean
    Const cBoundControls As String = _
        "|Textbox|Combobox|Listbox|CheckBox|OptionButton|ToggleButton|"
    Dim ctlTypName As String, ctlSource As String
    ctlTypName = "|" & TypeName(ctl) & "|"
    isBound = False
    If InStr(1, cBoundControls, ctlTypName) <> 0 Then
        ctlSource = ctl.ControlSource
        If Len(ctlSource) > 0 Then
            If Left$(ctlSource, 1) <> "=" Then
                'the control is bound
                'Debug.Print ctl.Name, "bound"
                isBound = True
            End If
        End If
    End If
End Function

 

[Kayleigh]

Hi I'm trying to use this function again and I keep getting an error 2450 when it definitely worked a few months ago.

Here is the full code:
(It seems to bug at the first for loop when it can't find the 'referenced forms')

Option Compare Database
Option Explicit

Dim bIsLoaded As Boolean

'Iterate through forms
Public Sub tagsAllForms()

    On Error GoTo Err_tagsAllForms
    Dim oForm As Form
    Dim nItem As Long
    For nItem = 0 To CurrentProject.AllForms.Count - 1
        'On Error Resume Next
        If IsFormLoaded(CurrentProject.AllForms(nItem).Name) Then bIsLoaded = True
        If Not bIsLoaded Then
           ' On Error Resume Next
            DoCmd.OpenForm CurrentProject.AllForms(nItem).Name, acDesign, , , , acHidden
        End If
        Set oForm = Forms(CurrentProject.AllForms(nItem).Name)
        changeTag oForm.Name
        If Not bIsLoaded Then
            On Error Resume Next
            DoCmd.Close acForm, oForm.Name
        End If
    Next

    MsgBox "Successfully updated audit trail in all forms.", vbInformation + vbOKOnly, gtstrAppTitle

Exit_tagsAllForms:
    Exit Sub

Err_tagsAllForms:
    MsgBox Err.Description, vbExclamation, "tagsAllForms Error " & Err.Number
    Resume Exit_tagsAllForms
End Sub

'Change tags to audit
Public Sub changeTag(sForm As String)

    
    On Error GoTo Err_changeTag
    Dim aO As AccessObject
    Dim fm As Access.Form
    Dim ct As Access.Control
    For Each aO In CurrentProject.AllForms
        If aO.Name = sForm Then
            Set fm = Forms(aO.Name)
            For Each ct In fm.Controls
                If isBound(ct) Then
                    ct.Tag = "audit"
                End If
            Next ct
            Set fm = Nothing
            If Not bIsLoaded Then
                On Error Resume Next
                DoCmd.Close acForm, aO.Name, acSaveYes
            End If
            Exit For
        End If
    Next

Exit_changeTag:
    Exit Sub

Err_changeTag:
    MsgBox Err.Description, vbExclamation, "changeTag Error " & Err.Number
    Resume Exit_changeTag
End Sub


'Check if control is bound
Public Function isBound(ctl As Control) As Boolean

    
    On Error GoTo Err_isBound
    Const cBoundControls As String = _
        "|Textbox|Combobox|Listbox|CheckBox|OptionButton|ToggleButton|"
    Dim ctlTypName As String, ctlSource As String
    ctlTypName = "|" & TypeName(ctl) & "|"
    isBound = False
    If InStr(1, cBoundControls, ctlTypName) <> 0 Then
        ctlSource = ctl.ControlSource
        If Len(ctlSource) > 0 Then
            If Left$(ctlSource, 1) <> "=" Then
                'the control is bound
                isBound = True
            End If
        End If
    End If

Exit_isBound:
    Exit Function

Err_isBound:
    MsgBox Err.Description, vbExclamation, "isBound Error " & Err.Number
    Resume Exit_isBound
End Function

Would appreciate any ideas please.

 

[Kayleigh]

To be honest I didn't find a clear reason why this code didn't run. But searching the web I found this post which I adopted instead of the 'currentProject.allforms' approach and it works fine. Have not seen any major difference between the two methods of referencing the forms.

 

[gemma-the-husky]

I am not sure without testing, but some of these tests will check all forms that exists, and others will check just forms that are open. In either case though, if you want to permanently change a tag you need to be in form design mode. You should be able to examine a tag without being in design mode. Maybe that's why you had different results. I think this is correct.