troubleshooting Question

Access VBA - Form Load event called multiple times

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVBA
7 Comments2 Solutions21 ViewsLast Modified:
Hi

I am calling the following procedure in the form load event but it seems to be called multiple times. Is it something to do with the fact that I am manipulating form captions etc

Private Sub Form_Load()
   
    oLoad
 
End Sub

Public Sub oLoadToolTips(ByVal oFormName As String, Optional blnSwitchback As Boolean, Optional xFormCaption As String = "")

   
   Dim oFormCaption As String
   Dim oFormCaption_New As String
   
   'On Error GoTo EH
   'If xFormCaption = "" Then
       oFormCaption = Forms(oFormName).lblFormCaption.Caption
   'Else
       'oFormCaption = xFormCaption
   'End If
   

   Dim N, i, t As String
   Dim oTabTooltip As String
   Dim oCriteria As String
   Dim oGeneralCriteria As String
   Dim ctl As Object
   Dim oControlName As String
   Dim oControlText As String
   Dim oDB_Folder As String: oDB_Folder = CurrentProject.Path
   
   Dim TT_Version As String
   Dim TT_FormCaption As String
   Dim TT_Tab As String
   Dim TT_ControlName As String
   Dim TT_ToolTip As String
   
   Dim oSessionText As String
   Dim oTheText As String
   Dim arrSplit As Variant
   Dim j As Integer
   Dim S As String
   Dim oPart As String
   Dim oPageCaption As String

   'First Translate the form Caption

    If oLanguage <> "EN" Then
        'FENCH (FR) etc. So need to look at English Critera to pull eg French translation..
        oCriteria = "[Form Caption (English UK)] = '" & SQLSafe(oFormCaption) & "'"
        Debug.Print oCriteria
        oFormCaption_New = Nz(DLookup("[Form Caption (Session Language)]", "[Tooltips and Translations]", oCriteria), "")
    Else
        'ENGLISH (EN) so here we need to find English based on the English or if it is a switch back on the session language
        'So if you are changing back to English you are doing a switchback
        If blnSwitchback = True Then '// SWITCH BACK TO ENGLISH BASED ON SESSION LANGUAGE CONTROL TEXT
            oCriteria = "[Form Caption (Session Language)] = '" & SQLSafe(oFormCaption) & "'"
            Debug.Print oCriteria
            oFormCaption_New = Nz(DLookup("[Form Caption (English UK)]", "[Tooltips and Translations]", oCriteria), "")
        Else
            'oCriteria = "[Form Caption (English UK)] = '" & SQLSafe(oFormCaption) & "'"
            'Debug.Print oCriteria
            'oFormCaption_New = Nz(DLookup("[Form Caption (English UK)]", "[Tooltips and Translations]", oCriteria), "")
            oFormCaption_New = oFormCaption 'Simply use the label that holds the form caption name
        End If
    End If
    
    Forms(oFormName).Caption = oFormCaption_New
    Forms(oFormName).lblFormCaption.Caption = oFormCaption_New 'Important for switchback
  
    For Each ctl In Forms(oFormName).Controls
   
   'https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa224135(v=office.11)
   
        'If ctl.ControlType = acTextBox Or ctl.ControlType = acLabel _
        'Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then 'Or ctl.ControlType = acImage Then
        
        'If ctl.Name = "sLicenceNo" Then Stop
        
        'ElseIf ctl.ControlType = acSubform Then colCtrlReq frm(ctl.Name).Form
        Dim H As String
        
        If ctl.ControlType = acTextBox Then GoTo Jump2 'This is necessary because text box tooltips get populated wit tab page names
        
        If ctl.ControlType = acLabel Or ctl.ControlType = acCommandButton Or ctl.ControlType = acPage Or ctl.ControlType = acTabCtl Then
        '//https://docs.microsoft.com/en-us/office/vba/api/access.accontroltype
        
            oSessionText = ""
            
            oControlName = ctl.Name
            
            'Stop

            If ctl.ControlType = acTextBox Then
                'oControlText = Nz(ctl.Value, "")
                'Forms(oFormName).Controls (oControlName)
            ElseIf ctl.ControlType = acLabel Then
                oControlText = Nz(ctl.Caption, "")
                'Forms(oFormName).Controls(oControlName).Caption
            ElseIf ctl.ControlType = acCommandButton Then
                oControlText = Nz(ctl.Caption, "")
            'ElseIf ctl.ControlType = acPage Then
                'oControlText = Nz(ctl.Caption, "")
            ElseIf ctl.ControlType = acTabCtl Then
                GoTo JumpToTab
            End If
            
                                                                                         
            '++++++++++++++ First check if this has a general match without a form name +++++++++++++++++++++++++++++++++
            oGeneralCriteria = "[Form Caption (English UK)] = 'ALL FORMS'"
            
            If oLanguage = "EN" Then
                 oGeneralCriteria = oGeneralCriteria & " And [Control (English UK)] = '" & SQLSafe(oControlText) & "'"
                 Debug.Print oGeneralCriteria
                 oSessionText = Nz(DLookup("[Control (English UK)]", "[Tooltips and Translations]", oGeneralCriteria), "")
                 t = Nz(DLookup("[Tooltip (English UK)]", "[Tooltips and Translations]", oGeneralCriteria), "")
            Else
                 oGeneralCriteria = oGeneralCriteria & " And [Control (English UK)] = '" & SQLSafe(oControlText) & "'"
                 Debug.Print oGeneralCriteria
                 oSessionText = Nz(DLookup("[Control (Session Language)]", "[Tooltips and Translations]", oGeneralCriteria), "")
                 t = Nz(DLookup("[Tooltip (Session Language)]", "[Tooltips and Translations]", oGeneralCriteria), "")
            End If

            If oSessionText <> "" Then 'If "ALL" is in the form name then use
                'oSessionText = oLookForGeneralNameMatch
                GoTo Jump
            End If
            '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                   
            If oLanguage <> "EN" Then
                'FRENCH (FR) etc. So need to look at English Critera to pull eg French translation..
                oCriteria = "[Form Caption (English UK)] = '" & SQLSafe(oFormCaption) & "'"
                oCriteria = oCriteria & " And [Control (English UK)] = '" & SQLSafe(oControlText) & "'"
                Debug.Print oCriteria
                t = Nz(DLookup("[Tooltip (Session Language)]", "[Tooltips and Translations]", oCriteria), "")
                oSessionText = Nz(DLookup("[Control (Session Language)]", "[Tooltips and Translations]", oCriteria), "")
            Else
                'ENGLISH (EN) so here we need to find English based on the English or if it is a switch back on the session language
                'So if you are changing back to English you are doing a switchback
                If blnSwitchback = True Then '// SWITCH BACK TO ENGLISH BASED ON SESSION LANGUAGE CONTROL TEXT
                    oCriteria = "[Form Caption (Session Language)] = '" & SQLSafe(oFormCaption) & "'"
                    oCriteria = oCriteria & " And [Control (Session Language)] = '" & SQLSafe(oControlText) & "'"
                    Debug.Print oCriteria
                    t = Nz(DLookup("[Tooltip (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                    oSessionText = Nz(DLookup("[Control (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                Else
                    oCriteria = "[Form Caption (English UK)] = '" & SQLSafe(oFormCaption) & "'"
                    oCriteria = oCriteria & " And [Control (English UK)] = '" & SQLSafe(oControlText) & "'"
                    Debug.Print oCriteria
                    t = Nz(DLookup("[Tooltip (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                    oSessionText = Nz(DLookup("[Control (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                End If

            End If
JumpToTab:
            '// Now deal with TAB pages
            If ctl.ControlType = acTabCtl Then
        
                 For Each tabPage In ctl.Pages
                        If ctl.Type = acPage Then
                            oPageCaption = tabPage.Caption
                            oControlText = oPageCaption
                            'MsgBox oPageCaption
                            If oLanguage <> "EN" Then
                                oCriteria = "[Form Caption (English UK)] = '" & SQLSafe(oFormCaption) & "'"
                                oCriteria = oCriteria & " And [Control (English UK)] = '" & SQLSafe(oControlText) & "'"
                                'Debug.Print oCriteria
                                oTabTooltip = Nz(DLookup("[Tooltip (Session Language)]", "[Tooltips and Translations]", oCriteria), "")
                                oSessionText = Nz(DLookup("[Control (Session Language)]", "[Tooltips and Translations]", oCriteria), "")
                            Else
                                If blnSwitchback = True Then '// SWITCH BACK TO ENGLISH BASED ON SESSION LANGUAGE CONTROL TEXT
                                    oCriteria = "[Form Caption (Session Language)] = '" & SQLSafe(oFormCaption) & "'"
                                    oCriteria = oCriteria & " And [Control (Session Language)] = '" & SQLSafe(oControlText) & "'"
                                    'Debug.Print oCriteria
                                    oTabTooltip = Nz(DLookup("[Tooltip (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                                    oSessionText = Nz(DLookup("[Control (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                                Else
                                    oCriteria = "[Form Caption (English UK)] = '" & SQLSafe(oFormCaption) & "'"
                                    oCriteria = oCriteria & " And [Control (English UK)] = '" & SQLSafe(oControlText) & "'"
                                    'Debug.Print oCriteria
                                    oTabTooltip = Nz(DLookup("[Tooltip (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                                    oSessionText = Nz(DLookup("[Control (English UK)]", "[Tooltips and Translations]", oCriteria), "")
                                End If
                            End If
                            If oSessionText <> "" Then 'Exclude tab pages where there is nothing found in TTT
                                tabPage.Caption = oSessionText
                                tabPage.ControlTipText = oTabTooltip
                            End If
                        End If
                  Next
                  
                  '// Note this Jump point had to be put here because the tab control tooltips where being applied to the search box labels
                  GoTo Jump2
                  
            End If
    
Jump:
            'If InStr(t, "View or edit") > 0 Then Stop
            If Len(t) > 0 Then
                Forms(oFormName).Controls(oControlName).ControlTipText = t
            End If
            
                       
            If Len(oSessionText) > 0 Then

                Forms(oFormName).Controls(oControlName).ControlTipText = t
                
                If InStr(oSessionText, "||") > 0 Then
                    
                    arrSplit = Split(oSessionText, "||")
                    S = ""
                    For j = 0 To UBound(arrSplit)
                        oPart = arrSplit(j)
                        If S = "" Then
                            S = oPart
                        Else
                            S = S & vbCrLf & oPart
                        End If
                        Debug.Print S
                    Next j
                    oSessionText = S
                End If
                
                'oSessionText = Replace(oSessionText, "No.", "N°")
                
                
                If ctl.ControlType = acTextBox Then
                     'Forms(oFormName).Controls(oControlName).Value = oSessionText
                     Forms(oFormName).Controls(oControlName).Value = "" '// NOTE this was causing a disruption
                ElseIf ctl.ControlType = acLabel Then
                     Forms(oFormName).Controls(oControlName).Caption = oSessionText
                ElseIf ctl.ControlType = acCommandButton Then
                     Forms(oFormName).Controls(oControlName).Caption = oSessionText
                End If
'// Note this Jump point had to be put here because the tab control tooltips where being applied to the search box labels
Jump2:
            End If
            
            'i = DLookup("[Image Path And Name]", "tblAdminForms", oCriteria)
       
            'If T <> "" Then
                'ctl.Caption = T
            'End If
            
            'If ctl.ControlType = acImage And i <> "" Then
                'i = Replace(i, "DatabaseFolder", oDB_Folder)
                'ctl.Picture = i
            'End If
            
            'MsgBox oControlName & " = " & oSessionText
  
        End If
    Next
    
    
      For Each ctl In Forms(oFormName).Controls
          If ctl.ControlType = acTextBox Then
              'oControlName = ctl.Name
              'Forms(oFormName).Controls(oControlName).ControlTipText = ""
              ctl.ControlTipText = ""
          End If
      Next
      Forms(oFormName).Repaint

    'Exit Sub
    
'EH:

    'MsgBox Err.Description & ": " & " modToolTips"
    
End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros