Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Access & Word Question

    Hi All,

    Is there a way to get the information displayed in access text box fields to display on a microsoft word letter/document by clicking a command button perhaps?

    For instance, i would like to have the "Company Name" "First Name" "Last Name" "Social Sec" "Company Code" fields from my database to automatically insert in the appropriate place on a pre-existing word document template by clicking a command button?

    Lets say I have record 1 of 1000 displayed. And I want to display record 1's information on my word document by clicking a command button, can that be done?

    How might it be done? Thanks!

  2. #2
    Join Date
    Aug 2008
    Posts
    58
    Funny you should ask, I just finished this exact project. Your first step is to create the template and format it as you wish. For the import fields, you have a couple of options. You can either create custom document properties and link them to fields in the document, or create Word bookmarks. Personally I think the DocProperty method is easier to control, but it does take a bit more work.

    Once you've done that, you can write some code to export data from whatever fields you desire and write them into the word document, and set that as the OnClick event of a command button. I can upload a code example if you like.

  3. #3
    Join Date
    Oct 2007
    Posts
    214
    Thanks for your reply. If you could please post the code that would be great.

    Also, if could elaborate on the docproperties is this something I change in word or access?

    Any examples of this process would be great. Thanks!

  4. #4
    Join Date
    Aug 2008
    Posts
    58
    Yes, you set document properties in Word. In Word 2007, Document properties are accessed by office button -> prepare -> Document Properties. Then click the drop-down properties button in the title bar of the properties window and select Advanced Properties. For earlier versions (if memory serves) it's under the Tools menu. Anyway, click on the Custom tab of the Advanced Properties window. Enter the name of your first field. It doesn't have to match the name of the field in your Access database that you want to import, but something similar makes your job easier. Leave the field type as Text no matter what. Make sure to type a space into the "Value" box and click "Add". Repeat this process for each field you wish to import from Access. Then simply go into your Word document and insert a Field wherever you want the imported data to appear. When the Field type window opens up, select Document Information from the menu on the left, then Document Property. You'll now see a list of the Document Properties, including any custom ones you've just created. Just make sure to clear the "preserve formatting on update" check box, otherwise it seems to mess up fields that have more than one word.

    Give me a few moments to put together a code example and I'll upload it.

    Edit: forgot to add, if you are trying to import any non-text data from Access, you'll need to create the formatting in Word's Field Codes. Hit Alt+F9 while in your Word doc and all the field codes will be displayed. They should look like {DOCPROPERTY "FieldName" \ format}. For example, a currency field would be {DOCPROPERTY "CurrencyFieldName" \ # $###,##0.00}
    Last edited by JManSF; 08-29-08 at 18:43.

  5. #5
    Join Date
    Oct 2007
    Posts
    214
    Thanks a million for the information.

    I will be trying this out later this evening. I may have questions if anything should arise.

    Thanks again

  6. #6
    Join Date
    Aug 2008
    Posts
    58
    WordExport example.pdf

    Here's a small sample of code. It's just the bare bones of what you need for the export. You can get a lot fancier with Error Handlers and additional user protection routines, if desired.

    J

  7. #7
    Join Date
    Oct 2007
    Posts
    214
    Hi,

    Tried the code and it reads user defined type not defined and highlights this part of the code ...appWord As word.Application...

    I'm using word 2003 ... Thanks!

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I still feel the need to ask why you would bother making a word document, why not just create an Access report?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I personally just open a Word Document and use the MailMerge option to "link" to an MSAccess table (or query) and used the Word MailMerge toolbar to insert the data fields where I wanted them and then save as a *.dot. And then I had a button on the MSAccess form which just opened the Word template.

    Here's an example of an mdb and a word dot. Opening the Word dot, left-click on the area above and show the "MailMerge" toolbar (or Click View -> Toolbars -> MailMerge). Click the <<abc>> to see the field names (which are surrounded by << and >> in the document). Use the "Insert Merge Fields" button (2nd to the left of the <<abc>> button (NOT the Insert Word Field but the next one to the left - make sure "Database Fields" is checked.) Click the <<abc>> to see the data and again, remember that, to navigate from record to record, you need to use the red < > buttons on the MailMerge toolbar. Edit as you need to and save as a *.doc. If you want to revise the *.dot, first save it as a different name, make your modifications, and then save it as the original name .dot (since you cannot save changes over a *.dot file.) You could do this all with a *.doc but I prefer to use a template (*.dot file) so users don't accidently make changes when viewing it and save them (they are forced to give it a different name) - so the *.dot is protected from accidental changes and is always the same when you pull it up the next time (unless again, you rename it, make the changes and then save it to the original *.dot name - or just delete and create a new *.dot file from scratch.) But if you want to make changes on the fly even when viewing the data in the document, simply make it a *.doc versus a *.dot file and open the *.doc file in the button code.

    See attached example. IMPORTANT: You MUST put both the mdb and both *.dot files in a folder called c:\HelpForOthers (otherwise in Word, you'll need to re-do the connection to the mdb data source (ie. 2nd button from left on the MailMerge toolbar - ie. "Open Data Source" and navigate and change to the new mdb location and select the table/query) and also change the code behind the buttons to open the *.dots in whatever folder you put them in.) Also note, you cannot link to a query with criteria in it (hence the make table routine to make the: CurrentWordRecord table and open the current record in Word.) If you're doing this all with your mdb and document, simply use the MailMerge wizard as it walks you through the 3-4 steps to connect to your mdb table/query. Then use the code in the example mdb to open your document.

    Note: You can also just open the WordMailMergeToCustomerMDB.dot without opening MSAccess.

    I like the code in the pdf you supplied JManSF but it is a bit of programming whereas it seems easier to just use the Tools -> Letters and Mailings -> MailMerge wizard.

    Last Note: You need to close the *.dot files before you can make changes to the mdb (since a connection is established when the *.dot's are open).
    Attached Files Attached Files
    Last edited by pkstormy; 08-30-08 at 03:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    JManSF,

    Please see my PM to you. I posted both our examples in the code bank but I will immediately remove your example if you want me to. I just thought it warranted sharing this kind of info in the code bank.
    Last edited by pkstormy; 08-30-08 at 03:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by benz1984
    Hi,

    Tried the code and it reads user defined type not defined and highlights this part of the code ...appWord As word.Application...

    I'm using word 2003 ... Thanks!
    Whenever you see that "User-defined type not defined" message, it means you have a missing reference.

    In the Visual Basic window, go to Tools -> References and make sure the box next to "Microsoft Word 12.0 Object Library" is checked.

  12. #12
    Join Date
    Aug 2008
    Posts
    58
    Re: Paul,

    Mail Merge is certainly an option here, and it is easier to implement, but it doesn't allow you to customize the output document for individual records. That's fine if you are creating a mass mailing or sheets of labels, but it is not suitable for personalized correspondence (and you can't save each output as a separate file). My application required the customization freedom that is only available through the DocProperties or Bookmarks method.

    ST,

    Using a report is also an option. I prefer using Word for highly formatted documents (especially since my templates were already available).
    Last edited by JManSF; 09-02-08 at 14:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •