Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Find and Replace (Access 97)

    Hello! I am interested in using a macro or code to find and replace phrases. For instance, I would like to store a form in a memo field with the areas to be replaced, like were the client name is to be inserted, to be typed in as a code. Example: Client Name would be in the form as clxxntnxmx. Then, you could do a serch for clxxntnxmx and replace it with the client name. I would like to do that automatically for about 10 fields. Client Name, Case Number, etc.

    My idea was to create the forms with the codes inserted were the real information goes, and then have a macro or VBCode run and replace the codes in the memo field automatically.

    Any ideas or easier ways? I don't know if I can type in the memo field like you can a text field, control: =trim("Client Name: "&[Client Name]) This would be a legal form, so.... It could get lengthy.

    Thanks in advance!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Find and Replace (Access 97)

    This sounds a good deal like doing a Word merge, where the main merge document contains field codes, and the merge process replaces the field codes with actual data from a record in the database. Is that the end result you are after?
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and Replace (Access 97)

    As Wendell wrote, a mailmerge in Word might be a good way to do this.

    If you want to keep it within Access, you could use code like this:

    ' Function to replace text in a string (in Access 2000 and higher, there is a built-in Replace function)

    Public Function ReplaceString(sIn, sWhat As String, sBy As String)
    Dim intPos As Integer
    Dim sResult As String
    If IsNull(sIn) Then
    ' The next instruction makes the function return Null if sIn is Null.
    ' If you want to return "" instead, omit this instruction.
    ReplaceString = Null
    Exit Function
    End If
    If sWhat = "" Then
    ReplaceString = sIn
    Exit Function
    End If
    sResult = sIn
    intPos = InStr(sResult, sWhat)
    Do While intPos > 0
    sResult = Left(sResult, intPos - 1) & sBy & Mid(sResult, intPos + Len(sWhat))
    intPos = InStr(intPos + Len(sBy), sResult, sWhat)
    Loop
    ReplaceString = sResult
    End Function

    ' Procedure to replace text in a field in a table

    Public Sub ReplaceCode(sTable As String, sField As String, sWhat As String, sBy As String)
    CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = ReplaceString([" & sField & "],'" & sWhat & "','" & sBy & "')"
    End Sub

    ' Call it like this:

    ReplaceCode "tblMyData", "MyMemo", "clxxtnxmx", Forms!frmSomething!txtClientName

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Find and Replace (Access 97)

    Hey guys, I appreciate it.

    Hans, you've helped me before several times. I'm alittle slow on code, but I'm gonna try it and see what happens. I maybe back with wierd questions! LOL!

    Thanks again guys!
    KST

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •