Mail-Merge the current record

[HappyChap]

I hope someone can help; furthermore, please be aware that my knowledge of the correct terminology may not be 100% accurate but I do hope that you will afford me some understanding because of that.

I am using Word and Access 2000.

I have created some code (linked to a command button on a form) which opens a word document (with mergefields) and then performs the intended mail merge: the code is below.

However, I am in need of some help please with enabling the merge to take place only on the open record of the form; ie, if I am looking at record 23 and I press the command button, it merges only the data from record 23.

I have searched various forums and googled for 3 whole days this week to try and find the solution/s but to no avail; in addition, I do appreciate that many members may wish to suggest the use of a report instead of mail merge; however, I most definitely want to use mail merge and not a report.

I have come across a simple solution but it keeps bringing an error message up. I've set a criteria in the query to be:

[forms]![Reservations2]![Booking Reference]

...and this successfully brings up the query (in Datasheet View) with the data from the open record in the form.

However, when I click on the command button on my form, to perform the mail merge, it brings up a prompt box asking "Enter Parameter Value" for [forms]![Reservations2]![Booking Reference].

When I click OK on that, it also says that it couldn't merge the main document with the data source as the fields were empty...even though I know the query (in Datasheet view) is showing the data I need to merge.

Finally, my module opens up and highlights some of the code in yellow, suggesting an error ? I've attached a screenprint.

Can anyone figure out what is happening or should I forget this "simple" option as a solution ?

Many thanks


Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("H:\enjoy120210\CorrespondenceLetters\MailMerge\BrksDednsEM.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Reservations.
objWord.MailMerge.OpenDataSource _
Name:="H:\enjoy120210\Reservations.mdb", _
LinkToSource:=True, _
Connection:="QUERY MasterDataSource", _
SQLStatement:="SELECT * FROM [Reservations2]"
'Execute the mail merge.
objWord.MailMerge.Execute
' Close BrksDednsEM.doc
Word.Documents("BrksDednsEM.doc").Close (Word.WdSaveOptions.wdSaveChanges)

End Function

 

[ypma]

I am no expert in ac2000 but I also would create a query as the data source for the word document with the criteria of the booking reference as you have done. When you mention you clicked OK when asked for the value of the booking reference did you enter the booking reference you required ? Check that the query you created is named in the SQL SELECT FROM .
Another test you could try is to enter the Booking Reference directly into the criteria of the data source query and save. Next run the mail merge directly from Word to access your data source and it should bring up the single document with the Booking Reference you entered in the criteria Final thought, is the word document pointing to the current database and path you are working on .?
Regards

 

[HappyChap]

Thanks ever so ypma for your reply; I'm glad I'm on the right tracks in setting up the query.

I've entered the booking reference and clicked OK and it then prompts me for the booking reference again. Once I click OK, it performs the mail merge and I get the mail merged document for that specific record...success, in part.

However, I really don't want to have to enter the reference once (or even twice) for this process to work. In addition, this "workaround" is flawed because as soon as I click the command button, the module window opens and you can't see the booking reference field on the form.

I've attached a screenprint of the query in Design View with the criteria showing just in case you (or anyone else) can see if I've done something wrong.

Thanks for the pointer re the "SQL SELECT FROM"; I've changed that to what it should be:

SQLStatement:="SELECT * FROM [MasterDataSource]"

...it just goes to show that a little knowledge can be dangerous...well, definitely in my case !!!

Just a thought, should the SQL Statement point to the "Booking Reference" field (not all fields) of the query or am I just talking rubbish ?

With regard to the entering of the booking reference directly into the criteria of the data source query, I would really like to avoid any other user intervention (it may not always be me using the system) and I would like the button to trigger off all the tasks.

Finally, yes, the word document is pointing to the current database and path I'm working on; would that make a difference ? On that point, I have submitted a separate thread about how to close the second copy of the database which opens to perform the merge.

Thanks in advance, I hope you (or someone else) can help please.

 

[ypma]

As mentioned I have never used ac2000 to mail merge but it appears that you have two data sources. The connection should be the same as the Select from .,in this case the query, which is made up of all the fields you require for your Word Doc..

I would get the query working as you intend first and then you will at least know the the data source is in place.
Some compilers use a master copy of the database linked to a copy of the data. and then create an MDE for the users, so you can see the document would would have to be redirected to the users drive and path.

May be obvious have you added a word reference ?
[FONT=&quot]With the module still open in Design view, click References on the Tools menu. Add the Word 9 Object Library to the list of available references. If the Object Library is not on the list, click the Browse button and locate the file Msword9.olb. [/FONT]

Failing this there are some example on the web .

Regards

 

[HappyChap]

Thanks ypma, I've changed the connection to:

objWord.MailMerge.OpenDataSource _
Name:="H:\enjoy120210\Reservations.mdb", _
LinkToSource:=True, _
Connection:="QUERY MasterDataSource", _
SQLStatement:="SELECT * FROM [MasterDataSource]"

...so that should crack that.

I've entered the criteria in the query (as shown in the previous attachment) and when I open the query in datasheet view it shows the data for the record that is currently open in "Reservations" so that's all good...that would say to me that the query (as the data source) is in place and working fine.

Just a thought, should the SQL Statement point to the "Booking Reference" field (not all fields as it currently does) of the query or am I just talking rubbish ?

I'm sorry if I'm being thick but I don't understand your paragraph about the master source ?

I can confirm that the reference is in place.

I've searched online for three days now but have yet to find anything that helps me.

It seems like there could be a simple solution to this problem as each component stage that I have created seem to be OK but it's possibly something to do with when Access talks to Word or the other way around.

Any help would be appreciated.

 

[ypma]

Just a thought, should the SQL Statement point to the "Booking Reference" field (not all fields as it currently does) of the query or am I just talking rubbish ?
The answer to that is No as its the Data source and its content that is required for your merge Word will only be able to access the record you selected .

Do not worry about my comment concerning Masters I assumed you would be encrypting your data base .


This link might be of use to you.
http://support.microsoft.com/kb/212329

also if you have a sample of the Northwind data base on your 2000 disc you should be able to test the example below.

[FONT=&quot]Example: Mail Merge a Microsoft Access Query with a Word Document[/FONT]
[FONT=&quot]The following example opens a Word document called C:\MyMerge.doc and runs a mail merge by using the Customers table in the Microsoft Access sample database Northwind.mdb as its data source. The following sample code assumes that the main document for the merge, C:\MyMerge.doc, already exists. [/FONT]

1. [FONT=&quot]Start Microsoft Access and open any database, or create a new one. [/FONT]
2. [FONT=&quot]Create a module and type the following procedure: [/FONT]

[FONT=&quot]3. [/FONT][FONT=&quot]Function MergeIt()[/FONT]
[FONT=&quot]4. [/FONT][FONT=&quot] Dim objWord As Word.Document[/FONT]
[FONT=&quot]5. [/FONT][FONT=&quot] Set objWord = GetObject("C:\MyMerge.doc", "Word.Document")[/FONT]
[FONT=&quot]6. [/FONT][FONT=&quot] ' Make Word visible.[/FONT]
[FONT=&quot]7. [/FONT][FONT=&quot] objWord.Application.Visible = True[/FONT]
[FONT=&quot]8. [/FONT][FONT=&quot] ' Set the mail merge data source as the Northwind database.[/FONT]
[FONT=&quot]9. [/FONT][FONT=&quot] objWord.MailMerge.OpenDataSource _[/FONT]
[FONT=&quot]10.[/FONT][FONT=&quot] Name:="C:\Program Files\Microsoft " & _[/FONT]
[FONT=&quot]11.[/FONT][FONT=&quot] "Office\Office\Samples\Northwind.mdb", _[/FONT]
[FONT=&quot]12.[/FONT][FONT=&quot] LinkToSource:=True, _[/FONT]
[FONT=&quot]13.[/FONT][FONT=&quot] Connection:="TABLE Customers", _[/FONT]
[FONT=&quot]14.[/FONT][FONT=&quot] SQLStatement:="SELECT * FROM [Customers]"[/FONT]
[FONT=&quot]15.[/FONT][FONT=&quot] ' Execute the mail merge.[/FONT]
[FONT=&quot]16.[/FONT][FONT=&quot] objWord.MailMerge.Execute[/FONT]
[FONT=&quot]17.[/FONT][FONT=&quot]End Function[/FONT]

Regards

 

[HappyChap]

Thanks for that...I didn't think the SQL statement needed changing but I am very new to all of this so thought I'd better check.

Thanks for the link, I've seen that and used that code (hence the same name of "MergeIt()") to create my code.

I've tested the code and all it does is bring up merged documents for all of the records; unfortunately, it doesn't bring up just the merged document for the open record which is what I'm after.

When I add the criteria to the query, that's when the problems within the mail merge seem to happen; however, the query does successfully return the data for that open record.

Have I got the criteria correct and in the right place ?

I'm at a loss as to what's going wrong here.

 

[ypma]

It would appear that the word doc is not getting its data from the filtered query. I would now test the word side of the link Save and rename your original doc and create a new doc with the original name and create a new merge doc.for the test you only need a few fields including the Booking Reference . For the test you will have to enter a Booking reference in the query before you Merge.the word doc.You should now get only the one record, If you still get all records the merge is getting its data from another source ?


After thought , All records may be showing because the Filter in the query is not saved. To test this type a Booking Reference into the criteria and save. Now do try to open the word doc. I know this not the solution you are trying to achieve but it could help us to discover why all records are showing once that is sorted we can try something else.
Best of Luck

 

[HappyChap]

Yes, I agree.

I set up the new document but when it came (via the Mail Merge Helper) to open the data source, it brings up the prompt box asking "Enter Parameter Value" for [forms]![Reservations2]![Booking Reference].

To be able to enter a booking reference in the query, per the suggestion in your first paragraph, I assume you mean that I put that number in place of the criteria "[forms]![Reservations2]![Booking Reference]".

If I've understood that correctly then, having put that number in there, it successfully merges the data from the record with that reference number; obviously though, I don't want to have to enter the reference number anywhere to enable this merge to take place.

However, I see that your second paragraph suggests doing just what I've describved so maybe I'm not understanding the difference between the different suggestions in your two paragraphs ?

 

[ypma]

If i understand you correctly you entered the ID directly into the criteria of the query and then opened the word document and the correct record appeared in word ?

The next step to confirm that the query opens with your original criteria. When you enter the ID number does the query open with the correct record..

If all these test work the fault would appear to be that word is ignoring you input of id when requested.
Please keep me informed

Regards

 

[HappyChap]

Hello ypma

I have entered the Booking Reference (eg 13012) into the criteria of the query and when I run the query, it brings up just that record.

I've also entered the criteria [forms]![Reservations2]![Booking Reference] into the criteria of the query and when I run the query, it too brings up just that record.

However, in neither case can I get it merge to word showing just that record.

The only way I can get the mail merge to work to just the open record of the form is by entering the Booking Reference number each time it prompts me with "Enter Parameter Value" for [forms]![Reservations2]![Booking Reference].

As said before, I don't want to have to enter the reference number anywhere to enable this merge to take place.

Can you help please ?

 

[ypma]

I do not know why it not working . I would use a work around by adding a yes/no tick box to the Table and then to the form . click this new box of the record you wish to Mail merge, all the other forms would be would not be ticked. Now add the new yes/no box to your Master-source query.
enter True or -1 one of them should work in the criteria of the new tick box , Now you can test the qry and it should only open the record you ticked. Then proceed to open the Word doc.
I hope this now works


Regards

 

[HappyChap]

Hello ypma

Thanks for that suggestion, not ideal, but I may consider it if I can't get a (with respect) more appropriate solution.

Thanks for your help and time.

Best wishes