Problems passing string from Access to Word document with VBA

[jhawk]

I'm trying to use Access 2003 VBA to automatically fill in a Word form with text fields; however, in several instances, I'm having difficulty passing a parsed string to a field. The following code is an example of what I've been using.

strVerification = ""

If Forms!frmHazardsAndControls3_1.fraYesNo.Value = 1 Then
    
    'Control 3.a) is applicable
    If Forms!frmHazardsAndControls3_2.chk1.Value = -1 Then

        docWord.FormFields("chk3a").CheckBox.Value = True
        
        strVerification = strVerification & _
            "3.a)1. Venting analysis.#" & _
            "3.a)2. Review of Design.#" & _
            "3.a)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    'Control 3.b) is applicable
    If Forms!frmHazardsAndControls3_2.chk2.Value = -1 Then

        docWord.FormFields("chk3b").CheckBox.Value = True
    
        If Forms!frmHazardsAndControls3_2.txt3bEquivalentICD.Value <> "" Then
        
            docWord.FormFields("bmkControl3b").Result = _
                "(" & Forms!frmHazardsAndControls3_2.txt3bEquivalentICD.Value & ")"
                
        End If
    
        strVerification = strVerification & _
            "3.b)1. Venting analysis.#" & _
            "3.b)2. Review of Design.#" & _
            "3.b)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    'Control 3.c) is applicable
    If Forms!frmHazardsAndControls3_2.chk3.Value = -1 Then

        docWord.FormFields("chk3c").CheckBox.Value = True
    
        If Forms!frmHazardsAndControls3_2.txt3cEquivalentICD.Value <> "" Then
        
            docWord.FormFields("bmkControl3c").Result = _
                "(" & Forms!frmHazardsAndControls3_2.txt3cEquivalentICD.Value & ")"
                
        End If
        
        strVerification = strVerification & _
            "3.c)1. Venting analysis.#" & _
            "3.c)2. Review of Design.#" & _
            "3.c)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    'Control 3.d) is applicable
    If Forms!frmHazardsAndControls3_2.chk4.Value = -1 Then

        docWord.FormFields("chk3d").CheckBox.Value = True
        
        strVerification = strVerification & _
            "3.d)1. Venting analysis.#" & _
            "3.d)2. Review of Design.#" & _
            "3.d)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    If Forms!frmHazardsAndControls3_2.chk1.Value = 0 _
    And Forms!frmHazardsAndControls3_2.chk2.Value = 0 _
    And Forms!frmHazardsAndControls3_2.chk3.Value = 0 _
    And Forms!frmHazardsAndControls3_2.chk4.Value = 0 Then
    
        strVerification = "See Unique Hazard Report: (fill in HR number here)"
        
    End If
            
    docWord.FormFields("bmkVerification3").Result = strVerification
    
Else

    'Hazard is not applicable
    docWord.FormFields("bmkVerification3").Result = _
        "N/A, no intentionally vented containers."
        
End If

When I run the code, if more than one checkbox is selected, the string strVerification parses together quite nicely within Access (I've checked this with breakpoints and the Locals window in VBE), but...when it comes time to pass it to the Word document, nothing happens. The text field remains blank.

If only one check box is selected, there's no problem at all, and the string passes fine to the Word document.

Similarly, but a little more strangely, I've got other instances of code that are similar in structure, but up to 3 check boxes can be selected and the string will parse and pass to Word without any problem, but with 4 or 5 checkboxes selected, the string won't pass.

I've compared the different instances of code without finding any differences in the structure, and am really at a loss why this is happening.

Any idea what might be causing this, and how I can fix it? I'm not a professional developer, so even the most basic advice is appreciated.

Cheers.

 

[tranchemontaigne]

At first glance I can say that this APPEARS to be similar behavior to a special character problem. I would recomemend trying to populate the MS Word document with some simple, short strings to eliminate special character considerations.

At second glance I would consider the possibility of a code module size limitation. Try to copy/paste your function into a clean module and try again.
I don't know why, but my experience is that if there are too many lines of code in a single module, functions and subroutines will fail without generating any error.

I hope this helps.
________
FORD T PLATFORM HISTORY

 

[jhawk]

Thanks, tranchemontaigne. I tried both your suggestions, and I think I know what the problem is, but I still don't know how to fix it. It doesn't appear to be either a special character problem, OR a code size problem, but rather a string size problem.

After I threw the code into a new module, I removed anything that I thought might be considered a special character, and then simplified the text as much as possible, even cutting the length of the separate strings down. Funny enough, the string parsed fine and passed to the Word doc no problem. BUT, as soon as I starting putting the strings back to their original text, and therefore size, the parsed string would no longer pass.

It seems that as soon as I reach 255 characters, the string no longer passes to Word. I don't think this is a problem on the side of Word, since all of the text field references I use are suppose to be able to handle "Unlimited" text. I know, however, that in Access, there's something about a 255 character limit for any text. I suppose that passing it as a "Memo" format might work in theory, but I don't know how to manage that.

Any thoughts?

Thanks.

 

[tranchemontaigne]

Try type casting your string as a variant data type.
________
LIDO

 

[jhawk]

Try type casting your string as a variant data type.

Unfortunately, that didn't work. Same result as before.

 

[DCrake]

Have you actually established that the limit is 255 characters or not. If so you can get Access to send the output to a text file and then get word to read from the text file at the bookmark.

David

 

[jhawk]

Right now I can only assume that the limit is 255 characters. This was established through trial. Basically, I tried parsing strings together right up that limit, and so far anything larger hasn't passed.

How would you go about doing what you've suggested with printing to a text file and so forth?