asked on
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
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 SubPublic 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
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.