Murray Brown
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
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
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.
Just put a stop in the procedure and see how many times you hit it (and when).
Jim.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
Then, call the function like this:Private Sub Form_Load()
oLoadToolTips Me
End Sub
Beside that, setting a break point and looking up the call stack is probably your best option.
ASKER
thanks Gustav, what code would I use to do that
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.