VBA opening a form, want to set focus, feels like everything have been tried out

[Martin Lang]

Hi All,

Thanks for reading this :)

I have a form A that consists of a main form A and a sub form A.

In sub form A, I have a field which I can double click. Then, Main form B opens up with a search field. Sub form B has a query as recordset. The query use the search field value as a filter, and the filter is updated every time text is entered into the search field.

What I want to happen is to set focus on the search field once form b is opened. I have tried this code.

Private Sub VismaID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Form B", acNormal
Forms![Form B].[Search].SetFocus
End Sub 

I have also tried a combination of

1.

Private Sub VismaID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Form B", acNormal
End Sub 

and
2. a OnEvent in Form B (tried all of them)

I have used F8 to go stepwise throug the whole code, and what happens then is that the macro first opens the form B, runs through the onevent and then goes back to Sub Dblclick which then ends.

However, I am unable to set focus on the search field either way.

I have a feeling it got to do something with the subform B... somehow. Because, if I hit ALT+Tab twice, the search field gets focus.

Any suggestions on where to troubleshoot or solve it?

Thanks in advance.

Martin

[jimatqsi]

If I remember correctly, you have to set the focus to form A first, then set the focus to an object on Form A.

Jim

[Martin Lang]

@jimatqsi
Hey Jim,

Thanks for having a look on this.

My goal is to set focus on a object on Form B.

Do I understand you correctly if I say you suggest

1. Set focus to Form B
2. Set focus on an object on Form B

I tried this code

Private Sub VismaID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Søke etter vismaråvarer", acNormal
Forms![søke etter vismaråvarer].SetFocus
Forms![søke etter vismaråvarer]!Søk.SetFocus
End Sub

Søk = search object
[Søke etter vismaråvarer] = Form B

Well, I had tried that as well.... not working :)

[jimatqsi]

It looks to me like you got it right, except ... you are opening Form B and then setting the focus on Form B (where it already is) and then setting focus to an object on Form B.

Shouldn't lines 3 and 4 refer to a different form, Form A?

Jim

[Martin Lang]

@jimatqsi
Well, the thing is that it looks to me that Form B does not have the focus, since the code in Form A continue to run after Form B has been opened...

if not, this code should have worked ??

Private Sub VismaID_DblClick(Cancel As Integer) 
DoCmd.OpenForm "Form B", acNormal 
Forms![Form B].[Search].SetFocus 
End Sub  

Form A is not of interest once Form B has been opened. The Private Sub VismaID_DblClick is run from Form A.

[Steven Kogan]

Opening a form with the double-click event has the unfortunate effect of always returning focus to the calling form at the end of the event. I'd like a solution to that problem. A side-effect is that the opened form appears on the screen, but keystrokes go to the calling form.

A workaround is using the click event instead (or some other event). If you open the form from a click event then the focus automatically goes to the form you are opening.

[Martin Lang]

@Steven Kogan
Hehe, Nice to know. Makes me feel better about not being able to solve the thing.

Hmm, a workaround would be nice, cause it feels user-safer to have doubleclick event...

Anyone in the forum with a solution to it, or is the only way to use a different event like Steven suggests?

[Steven Kogan]

I'm interested in hearing other solutions, but here is one idea.

This is an odd workaround, untested: The double-click event can have code at the end to set the form timer to something like a quarter second. The double-click event would end, then the form timer event would fire. The timer event would set the form timer to no longer fire (set the timer to zero), and then it would open the other form... I'm not sure, but I think this would leave the focus on the other form.

There are several drawbacks to this approach, one being that between the double-click event and the timer event the user might perform another action. Try using a very low value for the form timer.

I hope someone has a more proper solution to using the double-click event to open another form and set the focus to that form.

[Martin Lang]

Good idea, I will try out in some few days. Will come back to you with the results.

In the meantime, if other people have suggestions as well, they are very welcome to share them :)

Martin

[NeoPa]

You may find that there is more of a problem due to the order that your FormB controls appear in. Nevertheless, this should resolve the issue :

Option Compare Database
Option Explicit
 
Private Sub VismaID_DblClick(Cancel As Integer)
    Call DoCmd.OpenForm("[Søke etter vismaråvarer]", acNormal)
    Call DoCmd.SelectObject(acForm, "[Søke etter vismaråvarer]", False)
    Call Forms("[Søke etter vismaråvarer]").Søk.SetFocus
    Cancel = True
End Sub

I didn't have a form with subforms to play with. If you find further problems then attach a copy of the database in 2003 or earlier version and I'll look further into it for you.

[Steven Kogan]

Recreating the error in a new database was worthwhile.

The problem seems to occur with the double-click event on a textbox control.

With a label control focus goes to the other form properly. I haven't tested with other control types.

I've created a new database with two forms and put in code to open the second form using the double-click event. With a label control double-click event the second form automatically gets the focus. I'm also able to set focus on the subform succesfully.

With a textbox control the focus stays on the calling form.

Attached is the zipped Access 2003 test database. The textbox control to double-click is in Form1's subform.

It would be great to get the double-click event of the textbox control to work such that the opened form gets focus.

[NeoPa]

I'll have a look at it for you Steven.

[Steven Kogan]

Ah! I've got it figured out.

Add the line:
Cancel=True

That fixes it.

I think the focus returns to the calling form to perform the double-click event of selecting the word that was double-clicked.

[NeoPa]

That sounds right. I really should have included that in my posted code anyway. Good spot :)

[Martin Lang]

Thanks again NeoPa, and Steven too for the final touchline :)

Works great!

About the order, the search field is in the form header (the only field as well) while the subform is in the "detail area".

Will save that string for later use. Couldnt ever never found out that by myself. Really!

Thanks' :)

Martin

[NeoPa]

Martin Lang: About the order, the search field is in the form header (the only field as well) while the subform is in the "detail area".

I'm pleased that worked for you. Sometimes switching between controls in different sections can be tricky. Especially using TAB. In such cases you first have to give focus to the new section, then go from there. It seems this wasn't necessary in this case.