checking the last word in a textbox

Question

i have an access form consisting of a textbox , i need to check the last word of it and if this word is one of many words (array or a table column ) do an action , and this check will occurs in after_update event , something like

Private Sub textbox_AfterUpdate()

 Dim txt As String
 Dim lastword As String

  txt = TextBox.Value

 lastword= Right(txt, Len(txt) - InStrRev(txt, " "))

 if lastword in (array() or column in a table) then

    ' do an action

 End If

 End Sub

we can also us an external function , could you help me with it ??

Answer 1

Looks like you got the function for the last word already... Now for the search in an array and table use this:

Function isInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

and

Function isColumnName(stringToBeFound As String, tableName As String) As Boolean
    Dim db As Database
    Dim rs1 As DAO.Recordset
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset(tableName)
    isColumnName = False
    Dim fld As DAO.Field
    do until rs1.EOF
        if rs1.Fields.Item(0).Value = stringToBeFound then
            isColumnName = true
            exit loop
        end if
        rs1.moveNext
    loop
    Set fld = Nothing
End Function

usage:

if isInArray(lastWord, youArray) or isColumnName(lastWord, "yourTable")
    MsgBox "The word is already used!"
end if