Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select different word docs for merge via combo box (2003 SP1)

    I use the code below to merge the data from a form into a word document (approval.doc) when the user clicks on a command button.

    Private Sub cmdMergeIt_Click()

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Approval"
    DoCmd.SetWarnings True

    Dim WordObj As Word.Document
    Dim strPathtoYourDocument As String

    strPathtoYourDocument = "C:Approvalsapproval.doc"
    Set WordObj = GetObject(strPathtoYourDocument)
    WordObj.Application.Visible = True
    WordObj.MailMerge.Destination = wdSendToNewDocument
    WordObj.MailMerge.Execute
    WordObj.Close wdDoNotSaveChanges

    Set WordObj = Nothing


    End Sub


    This all works great, however, I have a request to expand the functionality of the merge.

    The form used in this merge also contains a Combo Box Named "Vendor" that has a drop down list of vendor names linked to a table. The request is when "XYZ vendor" is chosen in the combo box, a different approval letter is used (let's call it approval2.doc). The data for approval2.doc comes from the same table approval.doc, its just is in a different format. So I need to incorporate some sort of if statement that says if the combo box says "XYZ Vendor" choose approval2.doc to do the merge, else, use approval.doc. In the future, there may be a need to to further associate distinct approval letters with specific vendors.

    Unfortunately I have only the most rudimentary knowledge of VBA, and this is beyond my capabilities. I have searched around for some examples of VBA code that might do this, but I have not found anything I could use, or made sense to me. Any ideas as to how to get this to work would be greatly appreciated.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    This won't be too hard, we can help you with it. Can you provide some info about the vendor combo box?
    - What is the Column Count?
    - What is the Row Source?
    - What are the fields (name, type) in the vendor table?

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    Vendor Combo Box info:

    Column Count - 1
    Row Source - Vendors

    And if I understand where you are going with this correctly, the relevant field in the vendor table is:

    Field Name - Vendor Name, Data Type - Text

    Or do you need all the field names in the vendor table? (there are quite a few).

    I really appreciate the help. Thanks so much!

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    I would not of thought of going that route. Makes perfect sense. I'll give it a whirl and let you know how things go.

    Thanks again!

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    Oh so close...

    When I hit the command button to initiate the merge, I get a pop up box stating "enter parameter value" - Document Name

    If I enter the DocumentID in the box and hit OK, it picks the correct approval document, and I never get the pop up box again when I try to print subsequent approval documents until I stop and restart Access

    If I don't enter the DocumentID in the box and hit OK, it still picks the correct approval document, and I never get the pop up box again when I try to print subsequent approval documents until I stop and restart Access

    If I hit "Cancel" I get the message: Run Time Error '3075' Syntax Error (missing operator) in query expression 'DocumentID = ', and debug points me to "strPathtoYourDocument = "C:Approvals" & DLookup("DocumentName", "tblDocuments", "DocumentID = " & Me.[Vendor Combo Box].Column(1))" in the code

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    No, this is fine. A way to be very flexible is as follows:

    Create a new table tblDocuments, with two fields:
    DocumentID: AutoNumber (primary key)
    DocumentName: Text

    Enter the names of the documents in the DocumentName field, one name per record. Assuming that all documents will be in the same folder, you don't need to include the path, so for example Approvals1.doc.

    Add a field DocumentID to the Vendors table (type: Number, Long Integer). If most vendors will share a specific document, set the Default Value of the field to the ID of that document.
    Create a relationship between Vendors and tblDocuments on DocumentID, with referential integrity enforced.
    Populate the new field with the appropriate DocumentIDs.

    Change the row source of the combo box to

    SELECT [Vendor Name], DocumentID FROM Vendors ORDER BY [Vendor Name]

    Change Column Count to 2, and set Column Widths to 1";0"

    Change the line

    strPathtoYourDocument = "C:Approvalsapproval.doc"

    in your code to

    strPathtoYourDocument = "C:Approvals" & DLookup("DocumentName", "tblDocuments", "DocumentID = " & Me.[Vendors Combo Box].Column(1))

    Substitute the correct names, for example for the combo box. The code will take the DocumentID from the hidden second column of the combo box (counting starts at 0) and use it to look up the document name in the tblDocuments table.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    There was an error in my reply. The Row Source for the combo box should be

    SELECT [Vendor Name], DocumentID FROM Vendors ORDER BY [Vendor Name]

    instead of

    SELECT [Vendor Name], DocumentID FROM Vendors ORDER BY [Document Name]

    Make sure that all field names are correct - I could only make a guess at them.

  8. #8
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    That did the trick. I need to create the new approval doc, and roll everything into production. My next project is to replace the error messages that pops up when they try to print an approval letter for an account that isn't approved with something that is a bit more user friendly - right now they get the error message: Word could not merge the main document with the data source because the data records were empty or no data records matched your query options" but that can wait for another day.

    Thanks for the help Hans, you're a hero. [img]/forums/images/smilies/smile.gif[/img]

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select different word docs for merge via combo box (2003 SP1)

    Feel free to come back if you need more help.

Posting Permissions

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