Return focus to Access (Access2007) (1 Viewer)

[AOB]

Hi there,

I have a database which connects to another application (by creating an object) for the purposes of reading/writing data. Part of the code involves creating an instance of this application, and then taking input from the user via an Access form.

However, when I load the form, the focus stays with the other application, and the user has to 'click into' the form in order to reset the focus to the form and key the relevant details. Not the end of the world, granted, but frustrating over time and something I'd like to iron out.

I originally had this process in Excel, and used the following code to change the focus back to Excel (so that the user could key directly into the form)

Dim ExcCap As String
ExcCap = Application.Caption
 
' ...assorted code, including creating the object to 'house' the other application...
 
' Return focus to Excel
AppActivate ExcCap

But this doesn't apply to Access as it utilises the Application.Caption / AppActivate method to identify the appropriate window to switch back to. In Access, I don't have a caption to reference.

I've tried setting the focus in the Form_Load event but it doesn't bring the form 'to the fore'

Private Sub Form_Load()
   Me.SetFocus
End Sub

Any suggestions?

Thanks

AOB

 

[CJ_London]

You need to set the form border style to dialog - if you are using docmd.openform then you can set this as a parameter to the function call - acDialog.

You may also need to set the forms popup and modal properties to true as well

 

[AOB]

Thanks CJ

For which method are you referring?

(AppActivate <caption> or SetFocus?)

The form is set up exactly as you described but it still won't return the focus to Access when it is loaded and I use Me.SetFocus in the load event?

Thanks

AOB

 

[AOB]

Incidentally, when I use AppActivate instead...

Private Sub Form_Load()
    AppActivate Me.Caption
End Sub

I get an error :

Run-time error '5':
Invalid procedure call or argument

I think the problem is that I'm not trying to set the focus from one object within Access to another object within Access. I'm trying to set the focus back to Access generally, from a separate application entirely...

 

[CJ_London]

Sorry, I am confused by your terminology.

I have a database which connects to another application (by creating an object) for the purposes of reading/writing data. Part of the code involves creating an instance of this application, and then taking input from the user via an Access form.

I assumed both 'database' and 'another application' were both access databases.

 

[AOB]

Hi CJ,

Apologies for the lack of clarity.

No, the 'other application' is not an Access database, it is an entirely separate piece of software (an emulated session for a mainframe system)

My database passes commands back and forth to & from this system.

It sits minimised in the taskbar; there is no requirement to have it open & visible, merely available for commands & screen-scraping.

So I create the instance using GetObject / CreateObject, minimise it and return the focus to Access for the form input. I can then refer to it using the object variable it is assigned to.

But the focus doesn't automatically return to the open form; the user has to click into it first. I want to remove that necessity and automatically return the focus to the form (or, perhaps more accurately, to Access) from the VBA (which runs throughout)

As I mentioned before, when I ran this through Excel, I could use AppActivate to allow the VBA to identify the appropriate window (using the caption) to bring back the focus. That option isn't available to me in Access (or at least doesn't appear to be).

I'm looking for an Access equivalent of what I achieved in Excel.

Does this make sense?

Thanks (apologies again)

AOB

 

[CJ_London]

hmm, you are in territory I'm not too familar with.

I've had a quick look around and all the examples appear to use a shell command before appactivate and use the result from the shell call when calling appactive.

Here are some links
http://www.pcreview.co.uk/forums/appactivate-t1638473.html
http://msdn.microsoft.com/en-us/library/office/gg278643.aspx

 

[AOB]

Hi CJ,

Posted a reply (or at least thought I did but it doesn't seem to have gone through)

Really appreciate you taking the time to search for a solution to this problem; obviously I've been trawling the 'net trying to discover how it's done but to no avail. But thank you very much for the links.

I can't use the Shell command as that, by definition, creates a new instance of the referred application. My database is already running so I don't want to create another instance of it.

I'm staggered that there isn't an in-built function for returning focus to the parent application (i.e. the database from which the VBA is running) if it is lost to a separate window.

I did give this a whirl :

Private Declare Function BringWindowToTop Lib "user32" (ByVal _
         hwnd As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias _
         "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName _
         As Any) As Long
 
Private Sub Form_Load()
   BringWindowToTop (FindWindow(vbEmpty, Me.Caption))
   Me.SetFocus
End Sub

But it only works when debugging (i.e. when Access already has the focus anyway!); at run-time, the same problem persists (Access, and the form in question, are visible, but not 'active' and the user has to 'click in' to get the focus back)

I'm stumped, I would've thought this was a straightforward task!

 

[AOB]

CJ (and anyone else with an interest...)

Found a solution - seems the problem is that by putting the AppActivate command in the Form_Load event, the form isn't actually available to activate yet, hence the error.

But if I use AppActivate on another form, which is already open, just before I call the command to open the input form, it switches the focus back to Access.

Thus :

AppActivate "Caption Of Already Open Access Form", False
DoCmd.OpenForm "frmInputForm", acNormal, , , , acDialog

Ideally, I would prefer to use

AppActivate Me.Caption, False

in a suitable event that would fire just after the form is opened but I can't find one.

Anyway - might be of help to someone so thought I'd post it...

Cheers

AOB

 

[bejaranoangel]

Hello!
this line worked for me!!
AppActivate Me.Caption
Thanks a lot.
Regards.