Access XP to Word XP Mail Merge Problems 2

[DawsonUK]

Hello,

I am currently developing a database to hold records of Nurses in Access 97. However over the weekend, the entire network is moving from NT4 + Office 97 to Windows XP and Office XP. I have tested converting the database to XP and it works pefrectly with no problems, apart from one thing. Mail Merge.

I am having great problems with the Mail Merge functionality of Access and Word not being able to get the Data Source. This only seems to happen when the queries I anm making them have criteria in them using values from a form. It works fine on other queries though for some reason. This happens when trying to do the mail merge by using Tools > Office Links > Merge it with Word (or thereabouts).

Doing it through Word instead, by browsing for a datasource is just as bad, as when I try to choose a query, it only shows queries which do not have any criteria invloving values from forms.

Any example of what the mail merge is being used for to to mail all nurses in a particular specialism (drop down list in a form). The query for this would only show nurses in that specialism. Fine. But Word or Access won't let me mail merge from it.

Has anyone encountered a problem like this before? Or have any ideas how to fix it?

Any help would be grealy appreciated! If required I could send my database, or an example to show whats wrong.

David Dawson

 

[lameid]

I'm surprised you got that to work in office 97. I'd suggest having a query in your frontend for mailmerge and dynamically edit the SQL property of the query to include any necessary criteria as Word almost invariable opens a new instance of Access to do a merge (even in office 97) and therefore does not pickup the criteria in the other instance of access.

You could also try recording a macro in Word where you specify the mailmerge criteria and then copy that code and use automation to execute the Merge from access. Then in the code in access you can create your merge criteria based on the criteria. Either way ought to work.

 

[DawsonUK]

Hi, sorry to sound quite stupid, but how do you mean dynamically edit the query? Would this be done through a module or by using another method?

The second method you suggest wouldn't be too much use, as many users (around 40) could be using the database, and most of them arn't familiar with any form of coding or anything, just inputting data, pointing and clicking on things.

Thanks for the reply though, just that I wouldn't know where to start for writing a module or similar to change the SQL. I'm fairly competent in basic coding however, just nothing advanced.

David

 

[lameid]

Sorry, it took so long to get back... I finally took time off work this year (1.5 days) and I don't have internet access at home right now.

Anyway, the second method would work if you wrote the automation code and used a button that the users click on a form to open. Usually this is pretty simple if you understand object oriented programming... You use createobject to create a Word.Appplication object, then you copy the recorded macro from word and paste it in Access. Then you add qualifications to the Word code to use the Word application object. All you have to do then is tweak the code a bit to use some variable instead of some literal.

As for the first method you would use code to modify the SQL statement of a query (you'd have to understand both VBA and SQL).

I don't enough time to really help right now but these seem like popular subjects, so you can proably find something by searching in all the Acces forums.

 

[DawsonUK]

Hi, thanks for the reply!

I figured out a way to work it, by creating Make Table Queries, which simply take the results of the query and store them in a results table. (Front end of the database is stored locally).

The mail merge is then run off of these. This seemed to me the best option, as my VBA skills aren't too hot!

Thanks again for the help,

David