ACC: How to Use MS Word to Edit and Spell Check a Memo Field

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

You can use a word processor to edit memo fields stored in Microsoft Access tables. Then you can check the spelling and grammar in your data. This article describes how to use Microsoft Word for Windows to edit memo fields.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic," in version 2.0.

MORE INFORMATION

Note the following information before trying the example:

• Microsoft Word for Windows must have the DOS TEXT Import/Export filter installed.
• Only the most basic formatting will be preserved because Memo fields do not support tabs, margins, fonts, or other fancy formatting.
• Memo fields are limited to 32,000 characters (about 8-12 pages in Microsoft Word for Windows). Characters exceeding this limit will be truncated.
• The subdirectory containing WINWORD.EXE should be included in the path statement in your AUTOEXEC.BAT file or you can include the path in the following line of code in the example that follows:

        X% = Shell("c:\word6\WINWORD.EXE TEMP.TXT", 3)
  

• This example is presented in the simplest terms. If you open the Microsoft Word document and return to Microsoft Access before you save the file, you will receive an "Access denied" error message when the Access Basic code tries to open the document that is already currently open in Microsoft Word.

1. Create the following function in a new module.

         '******************************************************
         'Declarations section of the module
         '******************************************************
   

         Option Explicit
   

         '=====================================================
         'The following function EditMemo() calls Microsoft
         'Word for Windows
         '=====================================================
         Function EditMemo (MyEditBox As Control)
         Dim X%, LineInFile$, NewLine$, FileNum%, CarriageReturn_LineFeed$
   

         CarriageReturn_LineFeed$ = Chr$(13) & Chr$(10)
         FileNum% = FreeFile
   

         Open "TEMP.TXT" For Output As #FileNum%
   

         'Save the text to temporary file for Microsoft Word for WINDOWS
   

         If Not IsNull(MyEditBox) Then
            Print #FileNum%, MyEditBox
         Else
            MsgBox "There is no text to edit. Abort."
            Exit Function
         End If
   
         Close #FileNum%
   
         X% = Shell("WINWORD.EXE TEMP.TXT", 3)
   
         MsgBox "Press Any Key to Return" ' required to pause the function
   
         'Assume completion of the edits in Microsoft Word. Now read the text
         'and place it back into the edit control.
   
         FileNum% = FreeFile
         Open "TEMP.TXT" For Input As #FileNum%
   
         NewLine$ = ""
         Do While Not EOF(FileNum)
            Line Input #FileNum%, LineInFile$
            If NewLine$ <> "" Then
               NewLine$ = NewLine$ & CarriageReturn_LineFeed$
            End If
            NewLine$ = NewLine$ & LineInFile$
         Loop
         MyEditBox = NewLine$
   
         Close #FileNum%
   
         End Function
   
   

2. Create a new blank form based on the Employees table.

3. Add two text box controls (Last Name and Notes) to the form and assign the following properties:

         Object: Text box
         ------------------------
         ControlName: Last Name
         ControlSource: Last Name
   

         Object: Text box
         ------------------------------
         ControlName: Notes
         ControlSource: Notes
         OnDblClick: =EditMemo([Notes])
   

4. Save the form and switch to Form view.

5. Double-click the Memo field to start Microsoft Word for Windows. You will be presented with the Convert File dialog box. Select Text Only, and then choose the OK button.

6. Perform the operations you want.

7. Quit Microsoft Word for Windows and save the document. Note that Microsoft Access loads the changes into the Memo field.

REFERENCES

For a solution that replaces the MsgBox routine, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q99940
   TITLE     : ACC: How to Wait for a Shelled Process to Finish