Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2008
    Posts
    58

    Unanswered: Creating a Word doc from Access

    Hello,

    I'm trying to create a Word letter from Access using the document property method. The user interface is a tabbed form. One tab is "Contacts" which displays three types of contacts for each Project. I successfully created a Word doc with the contact information from this form by creating custom document properties in a Word template, mapping these to fields in the document, and then writing the Access form data to the document properties. This export code is activated by a command button located on the contacts form itself. I used the following code structure (I'm leaving a lot out for length's sake):

    Code:
    Dim strFieldName As string
    Dim prps As Object
    
    Set prps = doc.CustomDocumentProperties
    
    prps.Item("[WordField]").Value = Nz(Me![FieldName])
    So Access is pulling data from a field on the active form (the Me reference), and writing that data to the Document Property field specified in code.

    Now I'm trying to extend this functionality by pulling information from other forms that aren't active. My question is, what do I substitute for the "Me" reference? A DBLookup function? SQL? How would they be formatted?

    Here's a brief summary of the table structure:

    tblProjects: [ProjectName], [ProjectAddress], etc.

    tblContacts: [ProjectName], [ContactName], [ContactAddress], etc.

    tblMeasures: [ProjectName], [MeasureName], [MeasureCost], etc.

    The tables are related, but not indexed. Each table has it's own subform (a tab on the tabbed form). The fields I want to put in my Word document include information from all 3 tables shown above.

    Thanks in advance. Let me know if I need to clarify anything.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    what do I substitute for the "Me" reference? A DBLookup function? SQL? How would they be formatted?
    Either a DLookup or recordsets. I'd try DLookup first as it's simpler to code.

    An example of DLookup:

    DLookup("MeasureName", "tblMeasures", "[ProjectName] = 'myprojectname'")
    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

  3. #3
    Join Date
    Aug 2008
    Posts
    58
    Thanks, StarTrekker. Still no luck using the following code structure:

    Code:
    prps.Item("Measure1").Value = DLookup([MeasureName], tblMeasures, [ProjectName] = Me![ProjectName] And [MeasureNumber] = "Measure1")
    I wonder if there's an easier way to export a list of measures. The methodology I first used was to create fields called "Measure1", "Measure2", etc. and try and match those to the "MeasureNumber" field. The Measure Number is really meaningless. Is there some way to create a bulleted list based on query results, for example? Or a RecordSet?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your DLookup statement is almost correct - you need to frame the arguments with double quotes, though.

  5. #5
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by weejas
    Your DLookup statement is almost correct - you need to frame the arguments with double quotes, though.
    I tried that, and the results were the same. My DLookup fields don't go through to the Word doc. I've also tried creating a query with all of the necessary fields and referencing that instead. No luck.

    I think I'm going to create a separate pop-up form specifically for creating Word documents, and have all of my necessary fields included in the form's record source. It's cumbersome, but it should work.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    prps.Item("Measure1").Value = "Test"
    This will determine if there's a problem communicating with Word.

    MsgBox DLookup("MeasureName", "tblMeasures", "[ProjectName] = """ & Me.ProjectName & """ And [MeasureNumber] = " & Me.Measure1)
    (corrected). This will determine if there's a problem with your DLookup.
    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

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Duplicate post caused from server crash. Apologies.
    Last edited by StarTrekker; 08-28-08 at 23:21.
    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

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Duplicate post caused from server crash.
    Last edited by StarTrekker; 08-28-08 at 23:21.
    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
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Duplicate post caused from server crash.
    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

  10. #10
    Join Date
    Aug 2008
    Posts
    58
    Thanks ST. It was just a problem with my DLookups. I ended up creating a pop-up form where the user can select the recipient and type of letter from combo boxes. The key was to create a query with all the fields that a letter might possibly need, and use that as the pop-up form's record source. Works like a charm now. Thanks to all for the help!

    Edit: StarTrekker, that's a brilliant idea to use the MsgBox as a debug tool. I'm using that whenever I build new code now. Thanks!

  11. #11
    Join Date
    Aug 2008
    Posts
    58
    Ok, so now I'm feeling adventurous. As previously mentioned, many of my Projects have multiple Measures associated with them. What I'd like to do is have my code insert each associated Measure into the Word document as a bulleted list. Something like:

    Find "Measure1" from query.
    Write "Measure1" to document.
    Move to next record.
    Create new line in word.
    Write "Measure2" to document.

    And so on. I'm thinking that I'll need to use the DAO RecordSet framework for this. That's all new to me. Any ideas?

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, looping through a recordset should do the trick!

    Quote Originally Posted by JManSF
    Thanks ST. It was just a problem with my DLookups. I ended up creating a pop-up form where the user can select the recipient and type of letter from combo boxes. The key was to create a query with all the fields that a letter might possibly need, and use that as the pop-up form's record source. Works like a charm now. Thanks to all for the help!

    Edit: StarTrekker, that's a brilliant idea to use the MsgBox as a debug tool. I'm using that whenever I build new code now. Thanks!
    You're welcome!
    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

  13. #13
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by StarTrekker
    Yes, looping through a recordset should do the trick!
    Ok, at least I know I'm on the right track. So I feel comfortable enough writing the "do this then move to next record" part of the code. However, I really have no idea how to code "create new bullet in Word". I've aso never done any work with DAO or RecordSets before. How can I use my existing query as the source for a RecordSet?

  14. #14
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    In reverse order...
    To use a query as the source for a recordset, use it in the open statement:
    Code:
    rstData.Open qryYourQuery, CurrentProject.Connection, [CursorType], [LockType]
    To apply bullets is a little trickier. To find out the required commands, type a dew paragraphs of nonsense in a new Word document, and then record a macro to turn them into bullet points. (This trick also works for Excel, and is the single most valuable thing that I learned on the course!) You can then look at the code, and take the bits that you need into Access.

  15. #15
    Join Date
    Aug 2008
    Posts
    58
    Thanks weejas. I think I have an idea of it now. Will I need to write a statement that says to stop the MoveNext once it has reached the end of the RecordSet or does it know that automatically?

Posting Permissions

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