So this is what I've come up with. I've tried to keep it as simple as possible. This adds a drop down box with filtered results as you type in a text box. The list appears as you start typing and disappears when you move to another control. If you want it to appear when the text box gets focus you will need to add the On Focus event to the text box and copy the code from the On Change event.1. Create or use existing bound text box. Mine is called
txtDestination and is bound to the
Destination field of my table.
2. Add an unbound Listbox below it. I've called mine
lstDestinationDrowpdown and add a query to the
Row Source property of the Listbox. Mine looks like this.
Code:
SELECT DISTINCT [Group Journeys].Destination FROM [Group Journeys] WHERE ((([Group Journeys].Destination) Like "*" & [Forms]![Group Journeys1]![txtDestination].[Text] & "*")) ORDER BY [Group Journeys].Destination;
In this example my table is called
Group Journeys and my form is called
Group Journeys1. Sorry for the confusing names. They were like that already. I might fix them in the future.
3. Add the
On Change and
On Lost Focus events to the Textbox.
4. Add the
On Click and
On Enter events to the Listbox.
5. Copy the following vb code
Code:
Option Compare Database
' Updates the dropdown list and shows it
Private Sub txtDestination_Change()
Me![lstDestinationDropdown].Requery
Call Show_Custom_ListBox_Dropdown(Me![lstDestinationDropdown])
End Sub
' Closes the dropdown when the destination box looses focus
Private Sub txtDestination_LostFocus()
Call Hide_Custom_ListBox_Dropdown(Me![lstDestinationDropdown])
End Sub
' Sets the Destination to the selected item and returns focus to the Destination box
Private Sub lstDestinationDropdown_Click()
Me![txtDestination] = Me![lstDestinationDropdown]
Me![txtDestination].SetFocus
Call Hide_Custom_ListBox_Dropdown(Me![lstDestinationDropdown])
End Sub
' Stops the dropdown from disappearing when clicking in it.
Private Sub lstDestinationDropdown_Enter()
Call Show_Custom_ListBox_Dropdown(Me![lstDestinationDropdown])
End Sub
' Custom function to show the dropdown Listbox with formatting
Private Sub Show_Custom_ListBox_Dropdown(Box As ListBox)
With Box
If .ListCount = 0 Then
Call Hide_Custom_ListBox_Dropdown(Box)
Else
If .ListCount < 7 Then
.Height = .ListCount * 290 + 100
Else
.Height = 7 * 290 + 95
End If
Box.SpecialEffect = 4
End If
End With
End Sub
' Custom function to hide the dropdown list box and remove formatting that would make it still visible.
Private Sub Hide_Custom_ListBox_Dropdown(Box As ListBox)
Box.Height = 0
Box.SpecialEffect = 0
End Sub
That's about it. Change the names as necessary. The top 4 subs are the event handlers and the last 2 are custom functions to show and hide the list box.
Windows 7 64bit 
Access 2010 64bit
Reply With Quote
Originally Posted by Ajax
