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

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Feel free to come back if you need more help.

Reply | Quote