Automatically change from one character to another (1 Viewer)

[stu_c]

Hi all
odd question, I need to be able to find a VBA code to change text from a / to - is that possible?
for example if you have a REF number of
ABC/123/1 TO ABC-123-1
OR
A/2 to A-2

The reference numbers can contain any length of letters before or after the / with one or multiple /'s

thanks in advance

 

[Gasman]

Use Replace() function.

 

[jdraw]

stu-c,

Have you definitely identified which / have to be changed to -? All or just these ones???
Double check the scope of your proposed change-- you may end up with more issues than you have now.

 

[stu_c]

if I need to put it in the after update event can I just go

Public Function strReplace(varValue As Variant) as Variant

Select Case varValue

Case "/"
strReplace = "-"

End Select

End Function

 

[Gasman]

if I need to put it in the after update event can I just go

Public Function strReplace(varValue As Variant) as Variant

Select Case varValue

Case "/"
strReplace = "-"

End Select

End Function

No, that code does absolutely nothing? , even if it did, it would be overkill in my opinion.

Just use Me.Control = Replace(Me.Control,"/","-")

MS Excel: How to use the REPLACE Function (VBA)

This Excel tutorial explains how to use the Excel REPLACE function (in VBA) with syntax and examples. The Microsoft Excel REPLACE function replaces a sequence of characters in a string with another set of characters.

www.techonthenet.com

Perhaps add a test for null if that could happen?

 

[stu_c]

No, that code does absolutely nothing? , even if it did, it would be overkill in my opinion.

Just use Me.Control = Replace(Me.Control,"/","-")

MS Excel: How to use the REPLACE Function (VBA)

This Excel tutorial explains how to use the Excel REPLACE function (in VBA) with syntax and examples. The Microsoft Excel REPLACE function replaces a sequence of characters in a string with another set of characters.

www.techonthenet.com

Perhaps add a test for null if that could happen?

Thank you!, would you know how to replace a piece of text in an Email body to show a value form the form field?

 

[jdraw]

stu_c,

Step back and sort out the scope of your issue. Give us the 30,000 ft overview. Then describe the details of what you are trying to achieve. Tell us about the email and the text to be replaced. Give us the whole picture-- all the pieces.

 

[Gasman]

Thank you!, would you know how to replace a piece of text in an Email body to show a value form the form field?

Yes,in exactly the same way.
ObgEmail.Body = Replace(objEmail.Body,"yourplaceword",Me.ControlName)

 

[stu_c]

Yes,in exactly the same way.
ObgEmail.Body = Replace(objEmail.Body,"yourplaceword",Me.ControlName)

Hello Gasman
Thanks for the code, seems to be going into debug runtime error 424, and highlighting that code

Dim oWo  As Object 'Outlook.Application
    Dim oDoc1 As Object 'Word.Document
 
    
    'Set oWd = New Word.Application
    Set myOlApp = CreateObject("Outlook.Application")
    Set myitem = myOlApp.CreateItemFromTemplate("EmailTemplate.msg")
    myitem.Subject = "TEST TEMPLATE"
    myitem.display
    
    objEmail.body = Replace(objEmail.body, "123456789", 123)

 

[Gasman]

You substitute a string fro a string, not a string for a number.

Quit guessing. Look at the link I posted in the other forum.
Computers are stupid. YOU have to say exactly what you want and in exactly their terms.

Humans can see what you want, a computer cannot.

Also next time, give us the error message, not just the code. Do not expect us to look it up. Help us to help you.

Edit: I was giving you an EXAMPLE
I do not know your naming convention or object names.

SO, objEmailBody should be myitem.Body

You also need to understand any code offered. At present this is clearly not the case.

Not sure any of that code would work?

Where do you Dim the objects for Outlook and MailItem?, do you have Option Explicit at the top of every module.?
These are basic steps to VBA coding?

 

[jdraw]

stu_c,
Show all the code in this routine.
It appears that objEmail is not defined, nor set.
Also, this Replace(objEmail.body, "123456789", 123) is attempting to replace a String with a number.
Is that really what you are trying to do?
Would be better if you told us in plain English what you are trying to achieve? A data sample of the Before and After would be most helpful.

 

[Gasman]

Jack,
O/P in another site and thread is trying to use Replace() to replace place words in a template, instead of using bookmarks, as I suggested that as an alternative.

Auto Fill Outlook template via VBA

Hi all I have a default Email template, I would like to create something in VBA that loads up the Email template, fills out the subject with data from the access form and also inputs data into a certain bookmark section is this possible? I have managed to get the template to open and input...

www.accessforums.net

Sort of crossposting now?

 

[jdraw]

Thanks Paul. I've asked a few times for a plain English description. OP seems reluctant but is now asking in multiple forums???

 

[Gasman]

Thanks Paul. I've asked a few times for a plain English description. OP seems reluctant but is now asking in multiple forums???

Well to be fair, it started as a genreal question on how to replace. I then suggested the same method could be used for the email template. in that other forum. Probably the way I would go as I have never used bookmarks in an email, even if they are available?

In fact I did exactly that when I had to take over another SSAFA division as Treasurer

            If intAccount = 3 Then
                strFooter = Replace(strFooter, "Divisional Treasurer, Swansea South & West", "Temporary Divisional Treasurer, Neath & Port Talbot")
            End If

 

[stu_c]

Hello Both
I haven't been reluctant, the reasons being is our forms have several sub forms that everything runs off and it was far easier to give an example rather than post the mammoth length of code to get to the sub form and i also provided the Runtime error 424

My Current Code for running the Email template is

Public Function FUNC_EmailHSDept()
    Dim oWo  As Object 'Outlook.Application
    Dim oDoc1 As Object 'Word.Document
 
    
 'Set oWd = New Word.Application
    Set myOlApp = CreateObject("Outlook.Application")
    Set myitem = myOlApp.CreateItemFromTemplate("C:\Templates\EmailToHS.msg")
    myitem.Subject = UCase([Forms]![FRM_TBLALL_CaseDetails].[Form]![IncidentNo])
    myitem.To = UCase([Forms]![FRM_TBLALL_CaseDetails]![SFRM_TBLALL_StaffDetails].[Form]![CmbStaffNo].Column(0))
    
   myitem.body = Replace(myitem.body, "ENTERHSREF", UCase([Forms]![FRM_TBLALL_CaseDetails].[Form]![ReportREF]))
   myitem.display

 End Function

I have attched a screenshot of the Email and where "ENTERHSREF" is where we need the ReportRef from the Access form

 

[Gasman]

So you Dim oWo as the object for outlook, then set myOlApp ?

Here s how I did mine, a good few years back. Not perfect but it works, plus everything is Dimmed.

You really need to start using Option Explicit

Had to trim it to get it posted, so some blocks of code are missing, however what you need and should have is shown there.

However see how I am using the actual names I dimmed?

Private Sub cmdEmail_Click()

    ' Now the Outlook variables
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim strSigPath As String, strSignature As String, strAttachFile As String
    Dim strHeader As String, strFooter As String, strBody As String, strTemplatePath As String, strAppdata As String
    Dim intBody As Integer, intAccount As Integer

      
        ' Create the message if first time we are in a different client or tran type.
        'Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
        With objOutlookMsg
            ' Set the category
            .Categories = "SSAFA"
            .Importance = olImportanceHigh
            ' Add the To recipient(s) to the message. (Also work out which account to send on 12/07/19)
            If rs!ClientDivision = "SSW" Then
                Set objOutlookRecip = .Recipients.Add("Jim Needs - Personal")
                objOutlookRecip.Type = olTo
                intAccount = 2
            Else
'                Set objOutlookRecip = .Recipients.Add("SSAFA West Glamorgan Branch")
                Set objOutlookRecip = .Recipients.Add("South West Wales SSAFA")
                objOutlookRecip.Type = olTo
                intAccount = 3
            End If
    ' Need to send using SSAFA 365 int = 15
            'intAccount = 15
            ' Add the CC recipient(s) to the message.
            If rs!CCOffice And rs!ClientDivision = "SSW" Then
'                Set objOutlookRecip = .Recipients.Add("SSAFA West Glamorgan Branch")
                Set objOutlookRecip = .Recipients.Add("South West Wales SSAFA")
                objOutlookRecip.Type = olCC
            End If
          
            ' Need to get the Case Worker name from table, might be deactivated, so not in recordset
            If rs!CaseWorker > 0 Then
                rsCW.FindFirst "[ID] = " & rs!CaseWorker
                If rsCW.NoMatch Then
                    strCaseWorker = ""
                Else
                    strCaseWorker = rsCW!Data
                End If
            Else
                strCaseWorker = ""
            End If

            If strCaseWorker <> "" Then
                Set objOutlookRecip = .Recipients.Add(strCaseWorker)
                objOutlookRecip.Type = olCC
            End If
          
            ' Add Glyn in as BCC for CMS update - 12/02/19
            ' Only if SSW and he is not the caseworker
            If rs!ClientDivision = "SSW" And strCaseWorker <> "Glyn Davies" Then
                Set objOutlookRecip = .Recipients.Add("Glyn Davies")
                objOutlookRecip.Type = olBCC
            End If

          
            ' Set the Format, Subject, Body, and Importance of the message.
            '.BodyFormat = olFormatHTML
            strClient = rs!Client


            If strType = "Payment" Then
                .Subject = " Payment Made - " & strClient
            Else
                .Subject = "Deposit Received - " & strClient
            End If
            ' Now start the email with header
            'iColon = InStr(strClient, ":")
            ' If iColon = 0 Then iColon = Len(strClient) + 1
            .HTMLBody = strHeader & "<table border = '0' cellpadding = '5' cellspacing = '5'>"
            '    .HTMLBody = .HTMLBody & "<td>" & "Client: " & strPadCol & Left(strClient, iColon - 1) & strEndPad
            'End If
            ' Set counter to zero for count of transactions
            intTransactions = 0
        End With

        Do While blnSameClientType
            strDate = rs!TransactionDate
            strType = rs!TranType
            str3rdParty = rs!ThirdParty
            strAmount = Format(rs!Amount, "Currency")
            'strBalance = Format(rs!Balance, "Currency")
            'strBalance = Format(DSum("Amount", "Emails", "CMS = " & rs!CMS & " AND ID <= " & rs!ID), "Currency")
            ' Now using unique key Ukey to get correct running balance for entries out of sequence
            dblBalance = DSum("Amount", "Emails", "CMS = " & rs!CMS & " AND format(TransactionDate,'yyyymmdd')& format(ID,'000000') <= '" & rs!Ukey & "'")
            strBalance = Format(dblBalance, "Currency")
            ' Missed in sequence dates was producing erroneous balances 240620
            'strBalance = Format(Nz(DSum("Amount", "Emails", "CMS = " & [CMS] & " AND ID <=" & [ID]), 0), "Currency")
            'Now Calculated on the fly
            'strBalance = Format(rs!Balance, "Currency") ' was Format(DSum("[Amount]", "Emails", "[CMS]=" & rs!CMS & " AND ID <= " & rs!ID), "Currency")
          
            ' Make strBalance Red if negative
            If dblBalance < 0 Then
                strBalance = "<font color=""Red"">" & strBalance & "</font>"
            End If
          
            strRef = rs!Reference
            strMethod = rs!Method
          
            'strDatetype = "Date "
            If strType = "Payment" Then
                str3rdPartyType = "Recipient:"
                strDatetype = "Date Paid:"
            Else
                str3rdPartyType = "From Donor:"
                strDatetype = "Received:"
            End If

            strNotes = Nz(rs!Notes, "")
      
      
            ' Now build the body of the message
          
            ' Make sure we have a colon in client, else use whole field
          
            ' Now add the variable data
            With objOutlookMsg
                .HTMLBody = .HTMLBody & strPad & str3rdPartyType & strPadCol & str3rdParty & strEndPad
                .HTMLBody = .HTMLBody & strPad & strDatetype & strPadCol & strDate & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Method:" & strPadCol & strMethod & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Reference:" & strPadCol & strRef & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Amount:" & strPadCol & strAmount & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Balance:" & strPadCol & strBalance & strEndPad
                ' Add any notes if they exist
                If Len(strNotes) > 0 Then
                    .HTMLBody = .HTMLBody & strPad & "Notes:" & strPadCol & strNotes & strEndPad

                End If
'                ' Add blank line for next set
                .HTMLBody = .HTMLBody & strBlankLine & strBlankLine
            End With
          
            'Now update the record
            rs.Edit
            rs!EmailStatus = "Sent"
            rs!EmailDate = Date
            rs.Update

            ' Now get next record
            rs.MoveNext
            ' Has client or tran type changed?
            If Not rs.EOF Then
                If strClientType = rs!Client & rs!TranType Then
                    blnSameClientType = True
                Else
                    blnSameClientType = False
                End If
            Else
                blnSameClientType = False
            End If
            ' Increment the counter
            intTransactions = intTransactions + 1
        Loop                                     ' End blnClientType loop
      
        ' Now add the footer and amend subject to indicate how many transactions in email
        With objOutlookMsg
            .Subject = .Subject & " - " & intTransactions & " " & strType
            If intTransactions > 1 Then
                .Subject = .Subject & "s"
            End If
          
            ' Need to amend the footer depending on account being used intAccount = 2 = SSW, 3 is NPT
            If intAccount = 3 Then
                strFooter = Replace(strFooter, "Divisional Treasurer, Swansea South &amp; West", "Temporary Divisional Treasurer, Neath &amp; Port Talbot")
            End If
          
            ' Now add the footer
            .HTMLBody = .HTMLBody & "</table>" & strFooter
            '.Importance = olImportanceHigh  'High importance
            'Debug.Print strHeader
            'Debug.Print .htmlbody
            'Debug.Print strFooter
            ' Resolve each Recipient's name.
            For Each objOutlookRecip In .Recipients
                'Debug.Print objOutlookRecip.Name
                objOutlookRecip.Resolve
            Next
  
            ' Should we display the message before sending?
            .SendUsingAccount = objOutlook.Session.Accounts.Item(intAccount)
            If blnDisplayMsg Then
                .Display
            Else
                .Save
                .Send
            End If
        End With
  
          
    Loop
    ' Switch off the filter and release recordset object, and go back to record we were on
    ' Me.FilterOn = False
    SetStatusBar ("Emails created.....")
    DoCmd.GoToRecord , , acGoTo, lngCurrentRec
    cmdRequery_Click
Proc_Exit:
    Set objOutlook = Nothing
    Set objOutlookMsg = Nothing
    Set objOutlookRecip = Nothing
    Set objOutlookAttach = Nothing
    Set rs = Nothing
    Set rsCW = Nothing
    Set db = Nothing
    SetStatusBar (" ")
    Exit Sub
  
Err_Handler:
    MsgBox Err.Number & " " & Err.Description
    Resume Proc_Exit



End Sub

 

[stu_c]

So you Dim oWo as the object for outlook, then set myOlApp ?

Here s how I did mine, a good few years back. Not perfect but it works, plus everything is Dimmed.

You really need to start using Option Explicit

Had to trim it to get it posted, so some blocks of code are missing, however what you need and should have is shown there.

However see how I am using the actual names I dimmed?

Private Sub cmdEmail_Click()

    ' Now the Outlook variables
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim strSigPath As String, strSignature As String, strAttachFile As String
    Dim strHeader As String, strFooter As String, strBody As String, strTemplatePath As String, strAppdata As String
    Dim intBody As Integer, intAccount As Integer

     
        ' Create the message if first time we are in a different client or tran type.
        'Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
        With objOutlookMsg
            ' Set the category
            .Categories = "SSAFA"
            .Importance = olImportanceHigh
            ' Add the To recipient(s) to the message. (Also work out which account to send on 12/07/19)
            If rs!ClientDivision = "SSW" Then
                Set objOutlookRecip = .Recipients.Add("Jim Needs - Personal")
                objOutlookRecip.Type = olTo
                intAccount = 2
            Else
'                Set objOutlookRecip = .Recipients.Add("SSAFA West Glamorgan Branch")
                Set objOutlookRecip = .Recipients.Add("South West Wales SSAFA")
                objOutlookRecip.Type = olTo
                intAccount = 3
            End If
    ' Need to send using SSAFA 365 int = 15
            'intAccount = 15
            ' Add the CC recipient(s) to the message.
            If rs!CCOffice And rs!ClientDivision = "SSW" Then
'                Set objOutlookRecip = .Recipients.Add("SSAFA West Glamorgan Branch")
                Set objOutlookRecip = .Recipients.Add("South West Wales SSAFA")
                objOutlookRecip.Type = olCC
            End If
         
            ' Need to get the Case Worker name from table, might be deactivated, so not in recordset
            If rs!CaseWorker > 0 Then
                rsCW.FindFirst "[ID] = " & rs!CaseWorker
                If rsCW.NoMatch Then
                    strCaseWorker = ""
                Else
                    strCaseWorker = rsCW!Data
                End If
            Else
                strCaseWorker = ""
            End If

            If strCaseWorker <> "" Then
                Set objOutlookRecip = .Recipients.Add(strCaseWorker)
                objOutlookRecip.Type = olCC
            End If
         
            ' Add Glyn in as BCC for CMS update - 12/02/19
            ' Only if SSW and he is not the caseworker
            If rs!ClientDivision = "SSW" And strCaseWorker <> "Glyn Davies" Then
                Set objOutlookRecip = .Recipients.Add("Glyn Davies")
                objOutlookRecip.Type = olBCC
            End If

         
            ' Set the Format, Subject, Body, and Importance of the message.
            '.BodyFormat = olFormatHTML
            strClient = rs!Client


            If strType = "Payment" Then
                .Subject = " Payment Made - " & strClient
            Else
                .Subject = "Deposit Received - " & strClient
            End If
            ' Now start the email with header
            'iColon = InStr(strClient, ":")
            ' If iColon = 0 Then iColon = Len(strClient) + 1
            .HTMLBody = strHeader & "<table border = '0' cellpadding = '5' cellspacing = '5'>"
            '    .HTMLBody = .HTMLBody & "<td>" & "Client: " & strPadCol & Left(strClient, iColon - 1) & strEndPad
            'End If
            ' Set counter to zero for count of transactions
            intTransactions = 0
        End With

        Do While blnSameClientType
            strDate = rs!TransactionDate
            strType = rs!TranType
            str3rdParty = rs!ThirdParty
            strAmount = Format(rs!Amount, "Currency")
            'strBalance = Format(rs!Balance, "Currency")
            'strBalance = Format(DSum("Amount", "Emails", "CMS = " & rs!CMS & " AND ID <= " & rs!ID), "Currency")
            ' Now using unique key Ukey to get correct running balance for entries out of sequence
            dblBalance = DSum("Amount", "Emails", "CMS = " & rs!CMS & " AND format(TransactionDate,'yyyymmdd')& format(ID,'000000') <= '" & rs!Ukey & "'")
            strBalance = Format(dblBalance, "Currency")
            ' Missed in sequence dates was producing erroneous balances 240620
            'strBalance = Format(Nz(DSum("Amount", "Emails", "CMS = " & [CMS] & " AND ID <=" & [ID]), 0), "Currency")
            'Now Calculated on the fly
            'strBalance = Format(rs!Balance, "Currency") ' was Format(DSum("[Amount]", "Emails", "[CMS]=" & rs!CMS & " AND ID <= " & rs!ID), "Currency")
         
            ' Make strBalance Red if negative
            If dblBalance < 0 Then
                strBalance = "<font color=""Red"">" & strBalance & "</font>"
            End If
         
            strRef = rs!Reference
            strMethod = rs!Method
         
            'strDatetype = "Date "
            If strType = "Payment" Then
                str3rdPartyType = "Recipient:"
                strDatetype = "Date Paid:"
            Else
                str3rdPartyType = "From Donor:"
                strDatetype = "Received:"
            End If

            strNotes = Nz(rs!Notes, "")
     
     
            ' Now build the body of the message
         
            ' Make sure we have a colon in client, else use whole field
         
            ' Now add the variable data
            With objOutlookMsg
                .HTMLBody = .HTMLBody & strPad & str3rdPartyType & strPadCol & str3rdParty & strEndPad
                .HTMLBody = .HTMLBody & strPad & strDatetype & strPadCol & strDate & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Method:" & strPadCol & strMethod & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Reference:" & strPadCol & strRef & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Amount:" & strPadCol & strAmount & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Balance:" & strPadCol & strBalance & strEndPad
                ' Add any notes if they exist
                If Len(strNotes) > 0 Then
                    .HTMLBody = .HTMLBody & strPad & "Notes:" & strPadCol & strNotes & strEndPad

                End If
'                ' Add blank line for next set
                .HTMLBody = .HTMLBody & strBlankLine & strBlankLine
            End With
         
            'Now update the record
            rs.Edit
            rs!EmailStatus = "Sent"
            rs!EmailDate = Date
            rs.Update

            ' Now get next record
            rs.MoveNext
            ' Has client or tran type changed?
            If Not rs.EOF Then
                If strClientType = rs!Client & rs!TranType Then
                    blnSameClientType = True
                Else
                    blnSameClientType = False
                End If
            Else
                blnSameClientType = False
            End If
            ' Increment the counter
            intTransactions = intTransactions + 1
        Loop                                     ' End blnClientType loop
     
        ' Now add the footer and amend subject to indicate how many transactions in email
        With objOutlookMsg
            .Subject = .Subject & " - " & intTransactions & " " & strType
            If intTransactions > 1 Then
                .Subject = .Subject & "s"
            End If
         
            ' Need to amend the footer depending on account being used intAccount = 2 = SSW, 3 is NPT
            If intAccount = 3 Then
                strFooter = Replace(strFooter, "Divisional Treasurer, Swansea South &amp; West", "Temporary Divisional Treasurer, Neath &amp; Port Talbot")
            End If
         
            ' Now add the footer
            .HTMLBody = .HTMLBody & "</table>" & strFooter
            '.Importance = olImportanceHigh  'High importance
            'Debug.Print strHeader
            'Debug.Print .htmlbody
            'Debug.Print strFooter
            ' Resolve each Recipient's name.
            For Each objOutlookRecip In .Recipients
                'Debug.Print objOutlookRecip.Name
                objOutlookRecip.Resolve
            Next
 
            ' Should we display the message before sending?
            .SendUsingAccount = objOutlook.Session.Accounts.Item(intAccount)
            If blnDisplayMsg Then
                .Display
            Else
                .Save
                .Send
            End If
        End With
 
         
    Loop
    ' Switch off the filter and release recordset object, and go back to record we were on
    ' Me.FilterOn = False
    SetStatusBar ("Emails created.....")
    DoCmd.GoToRecord , , acGoTo, lngCurrentRec
    cmdRequery_Click
Proc_Exit:
    Set objOutlook = Nothing
    Set objOutlookMsg = Nothing
    Set objOutlookRecip = Nothing
    Set objOutlookAttach = Nothing
    Set rs = Nothing
    Set rsCW = Nothing
    Set db = Nothing
    SetStatusBar (" ")
    Exit Sub
 
Err_Handler:
    MsgBox Err.Number & " " & Err.Description
    Resume Proc_Exit



End Sub

I am genuinely really confused now, I have minimal knowledge of VBA and I think it is best I forget it as it appears to be well above my knowledge thank you all for the help with the first bit

 

[Gasman]

I am genuinely really confused now, I have minimal knowledge of VBA and I think it is best I forget it as it appears to be well above my knowledge thank you all for the help with the first bit

It is not rocket science, really, just logic.

You declare an object and give it a name. Then you use that name when you refer to that object.

What you are doing is equivalent to naming your child Fred, then calling him Jimmy.

Sad, really as all you need is a simple Replace() function which works as

Something = Replace(Something,"WhatToFind","WhatToReplace")

Where Something is the object where the data is being replaced, in the above case I am replacing WhatToFind with WhatToReplace, but you can use variables to store that data.

If you had looked at that link I posted on the other site, you would see EXACTLY how to use it?

If you give up at the first hurdle, especially for something so simple, then you will not get very far with Access believe me.

 

[stu_c]

Hi Gasman
I have done
myitem.body = Replace(myitem.body, "ENTERHSREF", UCase([Forms]![FRM_TBLALL_CaseDetails].[Form]![ReportREF]))

but I keep getting another error code of Run time error "287"

 

[Gasman]

And 287 is?
Why make us lookup the description?
Your form reference looks wrong to me?
If this is being run from that form use Me.
Why make things difficult for yourself?