Forum Discussion
Activating or shifting focus from Access to Word
Dear Sir
I have an Access DB in which I have stored Addresses of clients, I have concatenated all address fields in a Memo field. I have placed a button on the form which when I click copies the address from Memo field onto clipboard, this is working fine. I have another button which when I click shifts the focus to an already opened word document. The problem/s is/are sometimes this works fine i.e when I click button focus from Access shifts to Word, but sometimes it doesn't work at all. The other scenario when it doesn't work is when word document is minimised. I can see toggle happening in taskbar but focus doesn't shifts. I am using following code attached with click event of button:
Application.activate "Microsoft Word"
And when word document is not open it gives error.
Any help would be much appreciated.
Thank you.
The problem is that "shifting focus" from one running application to another, in this case, from Access to Word, is not a reliable method, so a different approach is likely to be safer and more stable. That said, your specific circumstances do restrict your options as well.
What you'll need to do is obtain the Windows Handle for the open Word document (there should be a Windows API to do that) and use that information to select and activate it. Not something I've done, but I suspect it is possible.
Asad_khan1971This is a pretty high risk strategy, as you're learning.
It will be much more effective to fully automate this entire process from Access VBA.
In other words, instead of relying on application Windows (which may or may not get focus as you expect), directly open the proper Word document from the Access VBA module and set a reference to that document. Then copy the appropriate text directly into that open Word document.
I can think of at least two ways to implement this, depending in part on what the end goal is and what the content of the Word doc is.
Have you considered using the Mail Merge feature of Word to retrieve the data from your Access accdb?
- Dear George Hepworth,
Thank you for your reply and suggestions.
Let me paint you whole picture.
1 The document (it is an appointment letter) which needs address pasting is downloaded from an in house web based software, the document is not allowed to be saved on local machine, it is temporarily loaded for the purpose of printing and editing and ineeds to be uploaded back to the same software.
2. Because of changing nature of addresses they could not be attached with the online database.
3. It is a long list of addresses, large number of them have similar sort of words in them and these lists are stored in Word table and one address is in multiple rows. Searching and copy pasting is difficult.
4. What I have done is imported all address in Access Table and then concatenated them in a memo field. Then with a search form which has combo box containing code names I display the whole address in memo field on a resultant form, the address is then copied on clipboard by clicking a button, then by clicking another button the focus should be shifted to already downloaded (opened) document and then ultimately copied address is pasted by the user at a specific place in document.
5. Everything is working except shifting the focus to already opened and displayed document.
6. We can't do the mail merge as document is individually downloaded from web-based software, it cannot be saved locally, and are individually uploaded back after printing and editing. Please keep in mind these are individual appointment letters.
6. My Software's sole purpose is to search address and copy paste in the opened document.
Hope above would help you to understand my predicament.
Thank you
Regards.
Asad_khan1971 What you need is Word Automation...essentially you instruct Word where to place the text you want...and it produces the final document with the info from Access but with the Formatting from Word.
- With the greatest of respect, I don't need more than shifting the focus from Access to Word after I have copied the address.
I think either I am not able to convey my problem or may be it is not possible to do what I want I my situation i.e. shifting the focus from Access to Word.
Anyway thank you very much for your suggestions which may be helpful in another situation.
RegardsThe problem is that "shifting focus" from one running application to another, in this case, from Access to Word, is not a reliable method, so a different approach is likely to be safer and more stable. That said, your specific circumstances do restrict your options as well.
What you'll need to do is obtain the Windows Handle for the open Word document (there should be a Windows API to do that) and use that information to select and activate it. Not something I've done, but I suspect it is possible.
