Converting string to variables (1 Viewer)

[TimW]

Hi All
I'm looking for a bit of guidance. I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet)
In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following

    strleftB = Chr(34) & " & me."
    strRightB = " & " & Chr(34)
    strTemplate = Replace(strTemplate, "[", strleftB)

I then get a string but in need to convert part of the string into variables, before I capture the correct output for my email

"<div>RFC Submission: <strong>" & [COLOR="Purple"]me.ChangeID[/COLOR] & ", </strong> " & [COLOR="purple"]me.Details[/COLOR] & "</div>"

My question is: what is the best way to split the string into strings and variables

 

[CJ_London]

What does you string look like before it is split?

The easiest way is probably to have in your string [Param1], [Param2] etc where it is due to appear and then use a replace. But you need to set up your string correctly first

 

[TimW]

Hi CJ
Thanks for replying.
Sorry for not being clear. The sample I posted has not been split. Its one string in code. The question was how to split it so the parts that look like variables (highlighted) actually become variables.
I have read elsewhere on the forum that this cannot be done
however, not to be deterred...
I was thinking about splitting the string into an array. Then I would need to identify which part was a string and which part a variable. One 'solution' may be to create a table or list so each variable looked up a corresponding 'reference' for example:

 if arr(i) = "CurrentStage" then arr(i) = me.currentStage.Value

A downside to this is that I would need to do this for every variable that can be used on the form/template (currently 44 fields) and I have already split the main data table into two to exclude the fields I did not want available on the template.
Then I would have to reconstruct the string (with the values of the variables) as an output.
Perhaps that is a not very elegant solution?

 

[TimW]

Update:
I have now worked out how to use the Eval function
I originally tried it using the Me. tag. If I replace me with the full monty "Forms!frmMain!CurrentStage" then I get a value.

My plan is to use "Forms!frmMain! " & variableName and then put that in a eval function.
I will still need to split the original string into an array. and if I compare each string to the field list --> if found it is a variable and if not its a string

sounds like a plan...

Watch this space!

 

[CJ_London]

you said

In the form the template is required, I lookup the template required and I get the string

and this is the string I am referring to

"<div>RFC Submission: <strong>" & me.ChangeID & ", </strong> " & me.Details & "</div>"

This string has been split and (ignoring formatting) will be interpreted as something like

"RFC Submission: 1, Some detail"

but this does depend on the context it is being used

I would expect your template (ignoring formatting) to look something like

"Dear [CustomerName], I am pleased to advise you of [Details]"

And on your form you would have controls for CustomerName and Details, plus others.

If the template is selected first, then it can be 'scanned' for fields required, and then either hide or disable the controls not required.

 

[CJ_London]

Ah - so you are using it as a query calculated field. Be aware you cannot use the split function in a query, you will need to create a public function in a module and pass through the different elements of the array.

Alternatively build the full query sql string in VBA and execute that

 

[TimW]

Re: Converting string to variables [Solved]

Hi
This is a working version of code that I need. I need to go through and add any error handling that is required but it does basically what I require

Private Sub cmdEmail_Click()
    ' string handling dims
    Dim strTemplate As String
    Dim criteria As String
    Dim i As Integer
    Dim arrResult() As String
    Dim fResult As String
    Dim TBL_NAME As String
    Dim FRM_NAME As String
    Dim strCtrl As String
    ' email dims
    Dim appOutlook As New Outlook.Application
    Dim msg As Outlook.MailItem
    Dim strBody As String
    Dim strSubject As String
    Dim strcc As String
    Dim strTO As String
    
    ' assign constants
    TBL_NAME = "Data"
    FRM_NAME = "Forms!frmMain!"
    
    rResult = vbNullString ' start with nothing

    ' lookup stage template for stage current rfc is at
    criteria = "[StageNo]=" & Me.CurrentStage
    strTemplate = DLookup("StageResult", "tblStageTemplate", criteria)

    arrResult = Split(strTemplate, "|")
    
    ' loop through string array
    For i = LBound(arrResult) To UBound(arrResult)
        ' find if string is also a field name
        If FieldExists(arrResult(i), TBL_NAME) Then
            ' if so replace with value in field
            strCtrl = FRM_NAME & arrResult(i)
            arrResult(i) = Eval(strCtrl)
        
        End If
        ' reconstruct original string
        fResult = fResult & arrResult(i)
        
    Next i
    
    
    Debug.Print strTemplate
    Debug.Print fResult
    
    ' enter fResult in email
  
    strSubject = Nz(Me.ChangeID, vbNullString)
    
   
   strcc = "**@yyy.com"
'   strTO = "To email addresses go here"
   
   'Create new mail message
   Set msg = appOutlook.CreateItem(olMailItem)
   With msg
      .To = strTO
      .Subject = strSubject
      .HTMLBody = fResult
   '  .Body = strBody '' used htmlbody instead :-)
      .cc = strcc
      .Display
      
   End With
        
    
End Sub

This is the input string (in a basic test form), the users will determine the actual strings from the Template form

<div>RFC Submission: |ChangeID|,  |Title|</div>

<div> |Originator|,</div>

<div><u>Result of the CCB review:</u>|DateAllocated|</div>

<div> </div>

The following is the output I am getting to put into the email

<div>RFC Submission: RFC2014022,  Shortage on diode</div>

<div> Simon Wells,</div>

<div><u>Result of the CCB review:</u>22/07/2014</div>

<div> </div>

BTW The FieldExists function comes from: <http://forums.devarticles.com/microsoft-access-development-49/check-if-a-field-exists-in-a-table-using-vba-58559.html>

 

[CJ_London]

OK, so you have it working which is the main thing