How to regain focus to access application?

[ReMeE]

I use an access application to send emails through outlook. But when outlook
is maximized it gets the focus. How do I regain focus to my application with
VBA?

 

[Merle Nicholson]

I've done this, and it's messy. I do a couple of VBA routines that call
Windows API functions and step through the active task list, retrieve each
windows handle, then use that to get all windows title bars, and look for the
one you want, then set the Z-order of that window so it pops to the top. It's
completely reliable, and I've used variations of this for many years in both
VB and VBA BUT ...
1) For the long run, it may not be wise to use Windows API calls in say a
company critical application.
2) You have to know at least some of the Access application title, and it
needs to be unique among all the titles the user has running.
3) It's messy if it's mis-applied, possible causing windows crashes
(although I've never encountered it)

I'll willingly give you the routines BUT
1) I won't post them to the public
2) There won't be a great deal of explanation of how it works
3) You need to reassure me that your programming skills extend to
trouble-shooting API calls.
4) You can't blame me if it all goes wrong.

Solution #2 is to pass the Access application window handle to Outlook, then
write a routine in Outlook to bring that handle number to the top. So it'd be
simpler programming, but API calls nonetheless.

 

[Dirk Goldgar]

I've done this, and it's messy. I do a couple of VBA routines that
call Windows API functions and step through the active task list,
retrieve each windows handle, then use that to get all windows title
bars, and look for the one you want, then set the Z-order of that
window so it pops to the top. It's completely reliable, and I've used
variations of this for many years in both VB and VBA BUT ...
1) For the long run, it may not be wise to use Windows API calls in
say a company critical application.
2) You have to know at least some of the Access application title,
and it needs to be unique among all the titles the user has running.
3) It's messy if it's mis-applied, possible causing windows crashes
(although I've never encountered it)

I haven't ever had occasion to fool around with this, but if the code is
running from within Access, don't you have the Access application's
window handle from Application.hWndAccessApp, so can't you just call the
BringWindowToTop API function and pass it that window handle?

 

[Merle Nicholson]

Yes, that's the way to go and I failed to mention how to get the app handle
number, but frankly I don't think it's wise to give a complete turnkey code
solution to anyone using API calls; some of it needs to be "discovered".

I included the long way around just in case he was having problems passing
the handle or didn't have any control over that part of the process. I
probably should have put the first method last in my answer, and made
Solution #2 at the top.

 

[ReMeE]

Hello Dirk,

How does this 'Application.hWndAccessApp' work?

Thanks in advance,

greetings,

René

 

[Dirk Goldgar]

Hello Dirk,

How does this 'Application.hWndAccessApp' work?

HWndAccessApp is a property of the Access Application object. It
returns the window handle of the Access application. If you're up to
some API programming -- Windows API, that is, not the Access API -- you
can pass this to the appropriate Windows API function to bring the
Access application window to the top. In the context of your original
question, I don't know whether you'll be able to do this from Access
after sending your e-mails via Outlook or not -- you may have to get
Outlook to do it, as Merle Nicholson described.

Merle Nicholson's cautions apply.

If you want to try doing it from your Access application's code, you
could paste the following definition of the BringWindowToTop API
function into the declarations section of a standard module:

'----- start of API function declaration -----
Declare Function BringWindowToTop Lib "user32" _
(ByVal hwnd As Long) As Long

'----- end of API function declaration -----

Then you could call it from your code like this:

BringWindowToTop Application.hWndAccessApp

As I said, that may or may not accomplish what you want, but you could
try it.