VB - StrLen and MERGEFIELD

The below Visual Basic code has two bugs I cannot figure out.
1) line 'a' should place whatever text is in the ExoRequest (merge information) and assign it to TheString. Is the document's MERGEFIELD different than Visual Basics?

2) line 'b' should see if TheString is empty or has text in it. If TheString has text THEN it should procede to 'c'.

Can someone help debug the below? My syntax is wrong on both lines.

--- Code ------------------------------------
a) TheString = {MERGEFIELD ExoRequest}
b) If (TheString) Than
c)    . . . . .
d) End If
----------------------------------------------
rw263Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gbahriConnect With a Mentor Commented:
Any update on this?
0
 
gbahriCommented:
Hi Rw263,

Try this code:
------------------------------------------------------------------------
Dim myField As Field
Dim TheString As String

For Each myField In ActiveDocument.Fields ' Loops through all fields
    If myField.Type = wdFieldMergeField Then ' if MergeField is found
        If InStr(1, myField.Code, "ExoRequest") <> 0 Then ' if ExoRequest if found
            TheString = myField.Result  ' Value is assigned to TheString ' Part A
           
            If TheString <> "" Then ' If TheString is not empty. Part B
                MsgBox "I found the Value of ExoRequest" ' Your Part C ....
            End If
            TheString = ""
             Exit For
        End If
    End If
Next
Set myField=Nothing
------------------------------------------------------------------------

Hope, this helps you a bit,

Thanks,

GBahri
0
 
rw263Author Commented:
GBahri -- Wow where did you learn all this?  You Rock!

so... If (TheString <> "") <=- WORKS!

as for TheString = {MERGEFIELD ExoRequest}....
  TheString = ActiveDocument.myField.Result.ExoRequest
or is it TheString = myField.Result.ExoRequest


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gbahriCommented:
Above code iterates through all fields present in active document to find Mergefields. If found, field result is assigned to a variable "TheString". If this variable contains some value, you can proceed with Step C (Part C of you Message).

Below line holds the Value:

TheString = myField.Result

Thanks,

GBahri
0
 
rw263Author Commented:
GBahri -- I have just ONE field present in the document. Can I just assign TheString to the ExoRequest, or must I search threw every field? Is this a limitation in Visual Basic? In Word, there is just ONE line to Merge a single field.
0
 
gbahriCommented:

If your document contains only one field code then you can use the following code:

activedocument.Range.Fields(1).Result

Thanks,

GBahri
0
 
rw263Author Commented:
You cannot call the field by name?? ActiveDocument.Range.ExoRequest.Result?
0
 
gbahriCommented:
Field object doesn't have any property like name associated with it. We have to call as elaborated in my last message.

Thanks,

GBahri
0
 
rw263Author Commented:
I tried your FIRST code snippet and got:
     A field calculation error occurred in record 1.
0
 
rw263Author Commented:
   Dim myField As Field
    Dim TheString As String
    For Each myField In ActiveDocument.Fields
        If myField.Type = wdFieldMergeField Then
            If InStr(1, myField.Code, "ExtractionRequest") <> 0 Then
                TheString = myField.Result
            End If
        End If
    Next
    If (TheString <> "") Then
     . . . . . . .

Causes:  A field calculation error occurred in record 1.
0
 
gbahriCommented:

"A field calculation error occurred in record #" (where # is every record number) may appear when you use an IF field to include files whose names are specified in other merge fields, and any of the merge fields specified is blank.

Pls. refer this page to know why this error appear:

WD: A Field Calculation Error Occurred in Record . . .
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B120278

Thanks,

GBahri
0
 
rw263Author Commented:
I saw the microsoft knowledge support on the field calculation error, the problem is that my Visual Basic Knowledge is limited and I do not know how to rewrite the If Instr() line to correct the problem. How do I go about debugging?
0
 
gbahriCommented:
To trace the error, would it be possible for you to upload the document:
http://www.ee-stuff.com/

You will be able to link it to this question by pasting in the URL. Update when you have uploaded it.

Thanks,

GBahri
0
 
rw263Author Commented:
the ONLY visual basic code I have in my document is:

Private Sub Document_Open()
    If CommandBars("Control Toolbox").Controls(1).Caption = "&Exit Design Mode" Then
        ActiveDocument.ToggleFormsDesign
        CommandBars("Control Toolbox").Visible = False
    End If
    For Each myField In ActiveDocument.Fields
        If myField.Type = wdFieldMergeField Then
            If InStr(1, myField.Code, "ExtractionRequest") <> 0 Then
                TheString = myField.Result
                Exit For
            End If
        End If
    Next
    If (TheString <> "") Then
        If InStr(TheString, "Lower Left Central Incisor") > 0 Then
            CheckBoxLL1.Value = True
        End If
        . . . . same lines for each tooth . . . .
        If InStr(TheString, "Upper Right Deciduous Second Molar") > 0 Then
            CheckBoxURe.Value = True
        End If
    End If
End Sub
0
 
gbahriCommented:
I've tried above code but its working fine with Merge fields. Error is related to fields that you've used and their values are blank which is creating problem. It would be better if you can upload the file.

Thanks,

GBahri
0
 
rw263Author Commented:
The reason I didn't u/l the file is because I _think_ the error is coming from the commercial program I am using, that is "passing the ExtractionRequest Merge" info. Is that possible or MUST it be from THIS code snippet?

According to the MSFT documentation, the problem must be with the If Instr(1,myField.Code.... line. I will try and replace that if with a MsgBox myField.Code to see if I can find out which Merge paramater is causing the error. If I can bypass that or I can call it direct via TheString = activedocument.Range.Fields(#).Result, I just need to find out which '#' is the ExtractionRequest. This is going to be difficult, but I'll try to put in some debug code and get back with more information? Till then, I'm stuck :(.
0
 
rw263Author Commented:
Still can't figure it out, I put in some MsgBox'es and still can't figure out what is wrong.
uploaded file to: https://filedb.experts-exchange.com/incoming/ee-stuff/358-gbahri.zip
0
 
gbahriCommented:

Are you using this document as a main form for mail-merge?
If this is so, then update your code as below:

I've updated the code that will unprotect the form while iterating through fields and restore protection on its completion.
---------------------------------------------------------------------------
Private Sub Document_Open()
    If CommandBars("Control Toolbox").Controls(1).Caption = "&Exit Design Mode" Then
        ActiveDocument.ToggleFormsDesign
        CommandBars("Control Toolbox").Visible = False
    End If
    Dim myField As Field
    Dim TheString As String
    Activedocument.UnProtect   ' Password:="ABC"  Password, if any
     For Each myField In ActiveDocument.Fields
        If myField.Type = wdFieldMergeField Then
            If InStr(1, myField.Code, "ExtractionRequest") <> 0 Then
                TheString = myField.Result
                Exit For
            End If
        End If
    Next
activedocument.Protect Type:=wdAllowOnlyFormFields ' Password:="ABC" 'if any



'- Your code here
'-
'-
'-
'-
'-

End sub
---------------------------------------------------------------------------

Pls. revert if you still have any doubt

Thanks,

GBahri
0
 
rw263Author Commented:
The document is getting the merged fields from a commercial program. The document is NOT protected. If you notice on page two of the letter, there is the SAME merge field ExtractionRequest that merges great!
0
 
gbahriCommented:
Can you check database where this information is stored. Pls. check that no field remains blank.

Try this, if this doesn't work try removing Fields with "If condition" (Alt-F9 to reveal fields). If this succeeds then we'll have to see for another workaround.

Make a backup copy of this document before proceeding.

Thanks,

GBahri
0
 
rw263Author Commented:
My Document contained a Microsoft Word Object, ThisDocument... as well as Module1 under the Modules. As it turns out the third party commercial program adds Modules1, Module2 and Module3 under the Modules. On a guess, I removed the Module1 in MY document thinking that it may intefere with the third party one and . . . .

I received a new error message --
 Microsoft Visual Basic
 Run-time error '-2147024809' (80070057)':
 The index into the specified collection is out of bounds.
 Continue/End/Debug/Help (Continue and Debug are grayed out)

Any ideas??
0
 
rw263Author Commented:
GBahri -- any thoughts??
0
 
rw263Author Commented:
GBahri -- is it possible that the third party program is not passing the MERGEFIELD to Word soon enough? When I have the MsgBox debug code, the MERGEFIELD's are all blank.  That doesn't explain the above run-time error though? ANy ideas?
0
 
gbahriCommented:
Sorry for coming late to you (I was busy with other chores). This could be a possibility. I would suggest you to run this code after Third party application process is over. For that you'll have to replace "Private Sub Document_Open()" with some new sub procedure say, "Sub FieldCalculation()".

Thanks,

GBahri
0
 
rw263Author Commented:
Never figured out the answer to this, but Gbahri put in a lot of time so you get it. I'll go back to the drawing board.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.