Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access VBA - Form Load event called multiple times

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

Open in new window


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

Open in new window

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Debug your code to see why you get multiple executions and use a boolean/counter to make sure to execute your code only once
Load Event only gets called once, but now that I say that, I never checked to see if it gets called again when you modify a forms recordsource (would think it would).  Are you doing that?

Just put a stop in the procedure and see how many times you hit it (and when).

Jim.
SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You should pass the form object by reference and adjust the function to use this, not Forms(oFormName):

Public Sub oLoadToolTips(ByRef oForm As Form, Optional blnSwitchback As Boolean, Optional xFormCaption As String)

Open in new window

Then, call the function like this:

Private Sub Form_Load()
   
    oLoadToolTips Me
 
End Sub

Open in new window

Beside that, setting a break point and looking up the call stack is probably your best option.
Avatar of Murray Brown

ASKER

thanks Gustav, what code would I use to do that
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial