InStr function

[yooper59]

I have been trying to rack my brain on why the InStr function works with a text box in access but not in a rich text box. Below is the code I'm trying to work with.

Function Finder() As String

' This function compliments the AutoKey F2
' The function will first search the current text box for prompted text "***"
' After searching the current text box, the function will then search remaining text boxes for "***" fields
' If no "***" fields are found, the function will return to the original textbox position

Dim VarInt, ElementTotal As Integer
Dim HomePos, myPosA, myPosC

'On Error GoTo Err_Finder_Click

If Left(Screen.ActiveControl.Name, 4) = "ProgressNote" Then
HomePos = Screen.ActiveControl.SelStart
If HomePos = 0 Then HomePos = 1
Forms![frm_main]![ProgressNote].Value = Forms![frm_main]![ProgressNote].Text
Else
Forms![frm_main]![ProgressNote].SetFocus
HomePos = 1
End If

myPosA = InStr(HomePos, Forms![frm_main]![ProgressNote].Value, "***")
MyPosB = InStr(HomePos, Forms![frm_main]![ProgressNote].Value, "{")
myPosC = InStr(HomePos, Forms![frm_main]![ProgressNote].Value, "}")

' Adjusts for user searching mid-{}
If myPosC > 0 And MyPosB = 0 Then
MyPosB = InStrRev(Forms![frm_main]![ProgressNote].Value, "{", myPosC)
End If

' Case 1: finds *** occuring before brackets or no brackets at all
If (myPosA > 0) And ((myPosA < MyPosB) Or Not (MyPosB > 0)) Then
Forms![frm_main]![ProgressNote].SelStart = myPosA
Forms![frm_main]![ProgressNote].SelLength = 3
GoTo Exiter
End If

' Case 2: finds *** occuring within brackets (i.e. myPosA is found, and myPosB is < myPosA or myPosB = 0)
If (myPosA > 0 And myPosC > 0 And MyPosB > 0) And (myPosA > MyPosB) Then
TempVars.Add "ListSelect", Right(Left(Forms![frm_main]![ProgressNote].Value, myPosC - 1), myPosC - MyPosB - 1)
TempVars.Add "FormTitle", "..." & Right(Left(Forms![frm_main]![ProgressNote].Value, MyPosB - 1), 50)
Forms![frm_main]![ProgressNote].SelStart = MyPosB
DoCmd.OpenForm "ListSelector", acNormal, , , , acDialog
Forms![frm_main]![ProgressNote].Value = Left(Forms![frm_main]![ProgressNote].Value, MyPosB - 1) & CStr(TempVars(0)) & Right(Forms![frm_main]![ProgressNote].Value, Len(Forms![frm_main]![ProgressNote].Value) - myPosC)
Forms![frm_main]![ProgressNote].SelStart = MyPosB
GoTo Exiter
End If

' Case 3: finds "{}" and before *** or no ***
If MyPosB > 0 And myPosC > 0 And (myPosC < myPosA Or Not (myPosA > 0)) Then
TempVars.Add "ListSelect", Right(Left(Forms![frm_main]![ProgressNote].Value, myPosC - 1), myPosC - MyPosB - 1)
TempVars.Add "FormTitle", "..." & Right(Left(Forms![frm_main]![ProgressNote].Value, MyPosB - 1), 50)
Forms![frm_main]![ProgressNote].SelStart = MyPosB
DoCmd.OpenForm "ListSelector", acNormal, , , , acDialog
Forms![frm_main]![ProgressNote].Value = Left(Forms![frm_main]![ProgressNote].Value, MyPosB - 1) & CStr(TempVars(0)) & Right(Forms![frm_main]![ProgressNote].Value, Len(Forms![frm_main]![ProgressNote].Value) - myPosC)
Forms![frm_main]![ProgressNote].SelStart = MyPosB
GoTo Exiter
End If

' Case 4: no "{}" or *** found; go to beginning and re-search
myPosA = InStr(1, Forms![frm_main]![ProgressNote].Value, "***")
MyPosB = InStr(1, Forms![frm_main]![ProgressNote].Value, "{")
myPosC = InStr(1, Forms![frm_main]![ProgressNote].Value, "}")

' Case 4a: finds *** which is present before list or list is not completely bracketed
If myPosA > 0 And (myPosA < MyPosB Or Not (MyPosB > 0) Or Not (myPosC > 0)) Then
Forms![frm_main]![ProgressNote].SelStart = myPosA - 1
Forms![frm_main]![ProgressNote].SelLength = 3
GoTo Exiter
End If

' Case 4b: finds list first, which is completely bracketed, and either *** does not exist or is after the start of the list
If (MyPosB > 0 And myPosC > 0) And (MyPosB < myPosA Or Not (myPosA > 0)) Then
TempVars.Add "ListSelect", Right(Left(Forms![frm_main]![ProgressNote].Value, myPosC - 1), myPosC - MyPosB - 1)
TempVars.Add "FormTitle", "..." & Right(Left(Forms![frm_main]![ProgressNote].Value, MyPosB - 1), 50)
Forms![frm_main]![ProgressNote].SelStart = MyPosB
DoCmd.OpenForm "ListSelector", acNormal, , , , acDialog
Forms![frm_main]![ProgressNote].Value = Left(Forms![NoteWriter].Controls("Note" & VarInt).Value, MyPosB - 1) & CStr(TempVars(0)) & Right(Forms![NoteWriter].Controls("Note" & VarInt).Value, Len(Forms![NoteWriter].Controls("Note" & VarInt).Value) - myPosC)
Forms![frm_main]![ProgressNote].SelStart = MyPosB
GoTo Exiter
End If

Forms![frm_main]![ProgressNote].SelStart = HomePos

GoTo Exiter

Err_Finder_Click:
MsgBox Err.Description
Resume Exiter

Exiter:
Exit Function

End Function

Continue reading...