Search Button for Memo Field, overflow error

Posted on 2012-08-23
Last Modified: 2012-09-11

I get this overflow error sometimes in the code below.  
The search term is actually contained in the memo field but it returns the error (attached).  

this is a follow up to a question:

How can I get rid of the error? (note that the search term is in the memo field but still gives error...sometimes though).  

Maybe someone can give me different code options for this search in a memo field that is rich text.  It doesnt seem to be very predictable and doesnt pinpoint the item found.  It si usually way off and highlights another term and not the search term.  If I change the setting from rich text to plain text then it works fine and search term is pinpointed exactly.

Private Sub cmdFindWord_Click()

  sWord = InputBox("Type word to find")
If Len(Trim(sWord)) > 0 Then

    sNote = Me.txtNotes
    'j = InStr(sNote, sWord)
    j = InStr(PlainText(sNote), sWord)
End If
If j <> 0 Then
    Me.txtNotes.SelStart = j - 1
    Me.txtNotes.SelLength = Len(sWord)
    MsgBox "Word " & Chr(34) & sWord & Chr(34) & " not found!", vbQuestion, "WORD SEARCH"
End If

End Sub

Open in new window

Question by:pdvsa
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Author Comment

    That is pretty nice looking form.  
    It did say:
    The sample database is concept-only. You will need to develop it further to use it with:
    "Memo fields where rich text is stored (tags may clash);"

    The field i am searching is Memo ppty.
    It seems as though it highlights but i dont think that will help me as i need the cursor to go directly to the search term and i need a "next" button to find the next occurrence.

    What do you think now?  Thank you
    LVL 74

    Accepted Solution

    Without a sample, I am a bit lost.

    <i need a "next" button to find the next occurrence.>
    In a broad sense, you would load a table with all your "search" terms.
    Then create a recordset from this table
    Then loop this table each time you clicked you "Next" button
    'Your Highlight Code

    Make sense?

    But again, it seems we need to address the error in your original question first...
    For that, a sample would be very useful.
    (you know the drill...)

    Author Closing Comment

    I will have to revisit again...thanks.  It is not that important at the moment.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now