Combobox Dropdown Method Does Not Work With Control GotFocus Event

[SolemnWishes]

Hi all,



Having searched Google for hours to no avail, I have decided to post a thread.

Goal: Have the first control, which is a combobox, expand dropdown menu when the form is open.

Form Settings:

• Data Entry: Yes
• Cycle: Current Record

The code I have is as follows:

Private Sub txtBrandName_GotFocus()
    MsgBox "txtBrandName GotFocus Start"
    Me.txtBrandName.Dropdown
    MsgBox "txtBrandName Got Focus End"

End Sub

Of course the MsgBox commands are for troubleshooting and will be removed at the end. This code works fine when I manually set the focus to txtBrandName control by mouseclick or tab cycle. However, it seems to cancel out when the form is first opened. The following is what happens specifically:

1. Doubleclick the form from Access's Navigation sidebar
2. Form is not yet visible
3. "txtBrandName GotFocus Start" prompts
4. Hit Ok
5. Only Combobox expansion is visible (in other words, .Dropdown runs)
6. "txtBrandName GotFocus End" prompts
7. Hit Ok
8. Combobox expansion disappears and Form is visible at the same time

So on initial opening of the form, GotFocus and all the code runs, however it doesn't seem to keep. I don't know if there is another event that runs after the first control GotFocus fires, but maybe that's screwing it up?

This is not critical to the function of my database, but this simple code has become an obsession after hours and hours of research and trial-and-error coding.

For my sanity, I would appreciate any kind of help.

[June7]

I tested this on a new form with only the one combobox control and no other code.

Simply does not work. As you said, the dropdown doesn't hold.

I tried Enter event as well.

I tried code in form Current event that moved to a textbox then back to the combobox - still doesn't hold the dropdown.

Have to manually move into the combobox.

[SolemnWishes]

Hi again June7,

Yes, I also tested dropdown command and setfocus in all kinds of events.

It's frustrating, because it should be so straight forward.

[Missinglinq]

This is a known quirk, if you will, where the the first Control to get Focus is a Combobox and its DropDown is set to its OnFocus. I've worked on it, unsuccessfully, a number of times, as have many others, and the results is always the same...the dropdown appears to occur, briefly, then it retracts.

Linq ;0)>

[essaytee]

Firstly, I'm testing on Access 2010.

Created a new form, with a number of controls and one a combo box.

In the form's Open event

Private Sub Form_Open(Cancel As Integer)
    Me.cboBox.SetFocus
    SendKeys "{F4}"
End Sub

The dropdown of the combo box held until I clicked elsewhere on the form.

Steve.

[essaytee]

Firstly, I'm testing on Access 2010.

Created a new form, with a number of controls and one a combo box.

In the form's Open event

Private Sub Form_Open(Cancel As Integer)
    Me.cboBox.SetFocus
    SendKeys "{F4}"
End Sub

The dropdown of the combo box held until I clicked elsewhere on the form.

Steve.

Okay I take this back, slightly.

Re-read OP problem. I also noticed that the GotFocus event of the combobox was a little quirky. If I simply only did the SendKeys thing, yep nothing happened. If I included a msgbox command just before, the msgbox fired and upon clicking the Okay button the dropdown of the combo box dropped down and remained down until I clicked elsewhere on the form. In lieu of the msgbox command I replaced with some miscellaneous code (x = 10, debug.print) which worked but the SendKeys was bypassed.

After all that I didn't help after all.

Steve.

[Missinglinq]

As I said, a number of us have worked on this problem, off and on, for a number of years and I've never seen a successful workaround.

Linq ;0)>

[amrut]

The following code achieves the desired result with only a combo box control on the form.
What do experts say ?

Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 1
End Sub

Private Sub Form_Timer()
Me.TimerInterval = 0
Me.Combo0.SetFocus      'This line is not required in case of only one control on form
ActiveControl.Dropdown
End Sub

This one too works.

Private Sub Combo0_GotFocus()
Me.TimerInterval = 1
End Sub
Private Sub Form_Timer()
Me.TimerInterval = 0
ActiveControl.Dropdown
End Sub

[June7]

It is working! Always nice to learn something new. If I understand correctly, the TimerInterval property sets the time between repeated executions of the Timer event. Why this allows the dropdown to remain open escapes me.

[Missinglinq]

...Why this allows the dropdown to remain open escapes me...

I don't think it's 'remaining open'...it's dropping it down, over and over and over again, every 1/1000 of a second, so that it appears to 'remain open!'

And a hack that involves a Form that only has a single Control on it, hardly solves the problem!

Linq ;0)>

[amrut]

I don't think it's 'remaining open'...it's dropping it down, over and over and over again, every 1/1000 of a second, so that it appears to 'remain open!'

And a hack that involves a Form that only has a single Control on it, hardly solves the problem!

Tried to test if above is the case using Debug.Print in all the above events with only one control and more controls on the form. The results in immediate window showed that the event fired once only. The Form's timer interval is set to 0 in the timer event itself and hence it is firing once only.

[June7]

It works even if there are multiple controls. Still mystifies me why changing the timer interval and triggering the timer event allows the dropdown to remain open but I'll take it.

[SolemnWishes]

Hi all,

Happy holidays and thank you to everyone that contributed to the thread, especially amrut with the code.

The code works for my multi-combobox form as well. So that's great news!

Curiosity really kills the cat, so I did a bit of research on the Timer event and found this link, which says setting the TimerInterval to zero prevents the event from firing, yet it fires here. If someone could shed some light on this it would be interesting. Of course, this is completely unnecessary, so I will mark this thread solved!

[amrut]

TimerInterval to zero prevents the event from firing, yet it fires here.

The timer interval is set to zero in the Timer Event, so it fires once and prevents re-occurrence.

[WGlass]

Thank you everyone for the replies, information and a solution. I am impressed!!!!!