Drop Down Dropdown List Immediately

[Clint Marshall]

In a form, I've been unable to set a dropdown list to immediately drop
down when I begin typing in the list.
I'm working with a fairly long list of names (last, first) and, rather
than having to correctly type out the entire last name, followed by a comma
and then starting the first name, I'd like to be able to type part of the
last name , which will get me close, and then be able to arrow down and pick
the correct name off the list without having to click on the dropdown arrow.
What do I have to do to get the list to drop down as soon as I begin
typing?
Thanks!

 

[Dirk Goldgar]

In a form, I've been unable to set a dropdown list to immediately
drop down when I begin typing in the list.
I'm working with a fairly long list of names (last, first) and,
rather than having to correctly type out the entire last name,
followed by a comma and then starting the first name, I'd like to be
able to type part of the last name , which will get me close, and
then be able to arrow down and pick the correct name off the list
without having to click on the dropdown arrow. What do I have to do
to get the list to drop down as soon as I begin typing?
Thanks!

You could force the list to drop down as soon as the focus reaches the
combo box by using an event procedure for the control like this:

Private Sub Combo0_GotFocus()
Me.Combo0.Dropdown
End Sub

But you may not want to drop down the list if the user is just tabbing
through. You might be able to get the combo to drop only when the user
types in it by using code like this in the form's module:

'----- start of code for the form's module -----
Option Compare Database
Option Explicit

Dim mfDropped As Boolean ' module-level variable

Private Sub Combo0_Change()
If Not mfDropped Then
Me.Combo0.Dropdown
mfDropped = True
End If
End Sub

Private Sub Combo0_GotFocus()
mfDropped = False
End Sub


Private Sub Combo0_LostFocus()
mfDropped = False
End Sub

'----- end of code for the form's module -----

You'd have to replace "Combo0" with your own combo box's name, and make
sure the event properties for "On Change", "On Got Focus", and "On Lost
Focus" (on the control's property sheet) were set to "[Event
Procedure]".

 

[Clint Marshall]

Dirk:
That's great! Works beautifully, except for one glitch.
The problem happens when I go straight to the dropdown list by clicking
the dropdown arrow, instead of typing in letters and selecting from the
dropdown list. In this case, after I select an item, the dropdown list
flashes and comes right back, instead of going away. I assume it has to do
with the other things I'm doing to blank out the control and then return
focus to the control so you can pick another name, but I haven't been chase
it down.
Can you take a peek at my code and see if anything jumps out? I'm think
I'm confused on the sequence of which event happen in which order.
Thanks!
----------------------------
Option Compare Database
Option Explicit
Dim mfDropped As Boolean ' module-level variable

Private Sub cboFindFamily_AfterUpdate()
'Moves to Family Name text box and
'finds the record of whatever name is selected in the combo box
DoCmd.ShowAllRecords
Me!FamilyName.SetFocus
DoCmd.FindRecord Me!cboFindFamily

'Set value of combo box equal to an empty string
Me!cboFindFamily.SetFocus
Me!cboFindFamily.Value = ""

End Sub

Private Sub cboFindFamily_Change()
If Not mfDropped Then
Me.cboFindFamily.Dropdown
mfDropped = True
End If
End Sub

Private Sub cboFindFamily_GotFocus()
mfDropped = False
End Sub

Private Sub cboFindFamily_LostFocus()
mfDropped = False
End Sub
--------------------------------------------

In a form, I've been unable to set a dropdown list to immediately
drop down when I begin typing in the list.
I'm working with a fairly long list of names (last, first) and,
rather than having to correctly type out the entire last name,
followed by a comma and then starting the first name, I'd like to be
able to type part of the last name , which will get me close, and
then be able to arrow down and pick the correct name off the list
without having to click on the dropdown arrow. What do I have to do
to get the list to drop down as soon as I begin typing?
Thanks!

You could force the list to drop down as soon as the focus reaches the
combo box by using an event procedure for the control like this:

Private Sub Combo0_GotFocus()
Me.Combo0.Dropdown
End Sub

But you may not want to drop down the list if the user is just tabbing
through. You might be able to get the combo to drop only when the user
types in it by using code like this in the form's module:

'----- start of code for the form's module -----
Option Compare Database
Option Explicit

Dim mfDropped As Boolean ' module-level variable

Private Sub Combo0_Change()
If Not mfDropped Then
Me.Combo0.Dropdown
mfDropped = True
End If
End Sub

Private Sub Combo0_GotFocus()
mfDropped = False
End Sub


Private Sub Combo0_LostFocus()
mfDropped = False
End Sub

'----- end of code for the form's module -----

You'd have to replace "Combo0" with your own combo box's name, and make
sure the event properties for "On Change", "On Got Focus", and "On Lost
Focus" (on the control's property sheet) were set to "[Event
Procedure]".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk:
That's great! Works beautifully, except for one glitch.
The problem happens when I go straight to the dropdown list by
clicking the dropdown arrow, instead of typing in letters and
selecting from the dropdown list. In this case, after I select an
item, the dropdown list flashes and comes right back, instead of
going away. I assume it has to do with the other things I'm doing to
blank out the control and then return focus to the control so you can
pick another name, but I haven't been chase it down.
Can you take a peek at my code and see if anything jumps out? I'm
think I'm confused on the sequence of which event happen in which
order. Thanks!
----------------------------
Option Compare Database
Option Explicit
Dim mfDropped As Boolean ' module-level variable

Private Sub cboFindFamily_AfterUpdate()
'Moves to Family Name text box and
'finds the record of whatever name is selected in the combo box
DoCmd.ShowAllRecords
Me!FamilyName.SetFocus
DoCmd.FindRecord Me!cboFindFamily

'Set value of combo box equal to an empty string
Me!cboFindFamily.SetFocus
Me!cboFindFamily.Value = ""

End Sub

Private Sub cboFindFamily_Change()
If Not mfDropped Then
Me.cboFindFamily.Dropdown
mfDropped = True
End If
End Sub

Private Sub cboFindFamily_GotFocus()
mfDropped = False
End Sub

Private Sub cboFindFamily_LostFocus()
mfDropped = False
End Sub

I expect it's happening because you are explicitly setting the focus
away from the combo (with "Me!FamilyName.SetFocus"), triggering
cboFindFamily_LostFocus, and then setting it back again (with
"Me!cboFindFamily.SetFocus"), triggering cboFindFamily_GotFocus. But
you don't really need to move the focus about in order to find the
record. You can do this (or something very like it) instead:

Private Sub cboFindFamily_AfterUpdate()
' Finds the record of whatever name is selected in
' the combo box

DoCmd.ShowAllRecords

Me.Recordset.FindFirst _
"FamilyName = " & Chr(34) & Me!cboFamily & Chr(34)

Me!cboFindFamily.Value = Null

End Sub

Note that I embedded quotes -- Chr(34) -- around the value from
cboFamily because I guessed that this is a text value that we're
searching for. Note also that the reference to "Me.Recordset" is only
valid in Access 2000 or later. If you're using Access 97 you have to
modify the code slightly, to search using the .RecordsetClone and
..Bookmark properties. Just let me know if you need that.

 

[Clint Marshall]

Dirk:
This worked fine, but the only change in behavior is that now, after
finding the selected record, the focus moves to the Family Name field.
That's not a problem, but it still didn't resolve the problem. It still
leaves the dropdown list in place when you use the dropdown arrow to invoke
the list and use the mouse to select a family.
Is there any way to explicitly turn off the dropdown list? When I hit
<esc>, the dropdown disappears.
Why would it behave differently if the list is invoked by typing a
character or by clicking on the dropdown?
In the interest of full disclosure, the only other form-wide code is:

 

[Dirk Goldgar]

Dirk:
This worked fine, but the only change in behavior is that now, after
finding the selected record, the focus moves to the Family Name field.
That's not a problem, but it still didn't resolve the problem. It
still leaves the dropdown list in place when you use the dropdown
arrow to invoke the list and use the mouse to select a family.
Is there any way to explicitly turn off the dropdown list? When I
hit <esc>, the dropdown disappears.
Why would it behave differently if the list is invoked by typing a
character or by clicking on the dropdown?
In the interest of full disclosure, the only other form-wide code
is: -------------
Private Sub Form_load()
Me!cboFindFamily.SetFocus
End Sub

It's a bit tricky, because the order of events for a combo box is
different when it's updated with a mouse as opposed to by the keyboard.
But try this version.

'----- start of code for the form's module -----
Option Compare Database
Option Explicit

Dim mfDropped As Boolean ' is combo dropped down?
Dim mfAfterUpdate As Boolean ' combo's AfterUpdate event fired?


Private Sub cboFindFamily_Change()
If mfAfterUpdate Then
mfAfterUpdate = False
Else
If Not mfDropped Then
Me.cboFindFamily.Dropdown
mfDropped = True
End If
End If
End Sub


Private Sub cboFindFamily_GotFocus()
mfAfterUpdate = False
mfDropped = False
End Sub


Private Sub cboFindFamily_LostFocus()
mfDropped = False
End Sub


Private Sub cboFindFamily_AfterUpdate()
' Finds the record of whatever name is selected in
' the combo box

DoCmd.ShowAllRecords

Me.Recordset.FindFirst _
"FamilyName = " & Chr(34) & Me!cboFamily & Chr(34)

' Keep combo Change event from dropping the list.
mfAfterUpdate = True

Me!cboFindFamily.Value = Null
If mfDropped Then
' Force the dropdown list to retract.
Me!FamilyName.SetFocus
Me!cboFindFamily.SetFocus
End If

End Sub


Private Sub Form_Load()
Me!cboFindFamily.SetFocus
End Sub
'----- end of code for the form's module -----

 

[Clint Marshall]

That did it, Dirk!
Thank you very much for putting in so much effort to make this work!
Are you aware of a resource that would help me to better understand the
order of events for things like this? I'd love to be smarter so I can be
more of a resource and less of a drain!
Thanks again!

-Clint

 

[Dirk Goldgar]

That did it, Dirk!
Thank you very much for putting in so much effort to make this work!
Are you aware of a resource that would help me to better understand
the order of events for things like this? I'd love to be smarter so
I can be more of a resource and less of a drain!
Thanks again!

Normally I'd say, search the help file for "order of events", but alas!
the help file in Access 2000 and 2002 is so bollixed up it's very hard
to find the information. Even when you do find it, it's ometimes wrong,
as when it says "The Change event doesn't occur [...] when you select an
item from the combo box list." The help file for Access 97 is a very
good resource -- I learned most of what I know about Access from reading
it and trying things out -- but these later versions have not been
nearly so good, at least not when it comes to looking for a particular
bit of information.

If you can bring yourself to read the help topic by topic according to
the table of contents, though, you'll learn a lot. You might also
consider getting a book or two on Access VBA programming. A Google
Groups search of these Access newsgroups will turn up lots of
recommendations. There's one by authors Smith and Sussman, I think,
that I've frequently seen recommended for people who are new to VBA
programming, and there's always the "developer's bible", the _Access
(version) Developer's Handbook_, from Sybex. That latter is a huge book
(two volumes in recent versions), slanted toward the experienced
developer, but it's wonderfully comprehensive.