Auto Expand process for a list box

[batteam]

I know the 'Auto Expand' property only is available for combo boxes, but is there anything remotely similar that can be done to a list box to make it act in a similar way?

I have tried creating a text control, then having the user type in the value (e.g. last name) of the record they're looking for in the list box, then forcing the list box to select that record based on the name in the text box, and it does work but seems kind of a round about way and not very efficient. Any other ideas would be appreciated. Thanks in advance.

 

[TheAceMan1]

How are ya batteam . . .

Your Idea is on the right track. Just realize [blue]Auto Expand[/blue] is the same as [blue]filter as you type[/blue] and the frame work of what you need for the listbox should become apparent.

However (and perferred by me) this can be done with a combo. You just need to drop the list on the first character entry. Thereafter the user views the filtering as they type!

For testing just created a combobox on the same form with the same [blue]rowsource[/blue] as the listbox then perform the following:
[ol][li]In the [blue]Declaration Section[/blue] of the form, copy/paste the following:

[blue]Private IsDown As Boolean[/blue]

[/li]
[li]In the [blue]On Change[/blue] event of the combo, copy/paste the following:

[blue]   If Not IsDown Then
      Me![purple][b][i]ComboboxName[/i][/b][/purple].Dropdown
      IsDown = True
   End If[/blue]

[/li]
[li]Finally in the [blue]On Exit[/blue] event of the combo:

[blue]   IsDown = False[/blue]

[/li][/ol]
[blue]Perform your testing and let me know . . .[/blue]

See Ya! . . . . . .

 

[batteam]

Thanks AceMan1. But a couple of questions: Can you explain more about the 'Filter as you type' process you mentioned and how it applies to listboxes, if it does.

Also, if you use the combo box and Dropdown process, is there any way to ensure that the vertical scroll in the list box 'moves' down to the record selected? In my case, the listbox record gets selected OK, but then I have to manually scroll down to find it.

Thanks again.

 

[TheAceMan1]

batteam . . .

Sorry to get back so late. Its hard getting in a decent post at work.

[blue] Can you explain more about the 'Filter as you type' process you mentioned and how it applies to listboxes, if it does.[/blue]

Filter As You Type started out as a way to [blue]search for records[/blue] in comboboxes & listboxes that contain [blue]large recordsets[/blue]. Imagine attempting to scroll thru 50K records to find an entry!

The key to the method involves [blue]updating the criteria of an SQL statement in code used to update the rowsource.[/blue]Typically the text in a textbox is captured via the [blue]OnChange[/blue] event and the SQL is rewritten to the rowsource (this results in an automatic requery for the combobox or listbox). The criteria of the SQL references the data entry textbox. Lets say the search textbox is [blue]txtSearch[/blue]. The code in the OnChange event would look like:

[blue]   Dim SQL As String, DQ As String
   
   DQ = """"
   
   SQL = "SELECT Name " & _
         "FROM tblClients " & _
         "WHERE [Name] Like " & DQ & Me!txtSearch.Text & "*" & DQ & ";"
   Me!LBx1.RowSource = SQL[/blue]

The where clause changes to match Me!txtSearch.Text, setting the filter. Writing the new SQL to the rowsource is the final step (this triggers an auto requery of the listbox).

If the user types abcd the criteria changes per letter as follows:

[blue]WHERE [Name] Like "a*"
WHERE [Name] Like "ab*"
WHERE [Name] Like "abc*"
WHERE [Name] Like "abcd*"[/blue]

I think you get the Idea.

[blue]. . . is there any way to ensure that the vertical scroll in the list box 'moves' down to the record selected?[/blue]

Not sure about this but I do remember seeing threads on the matter. Try a search!

Realize your filtering here. Even though a combobox automatically selects the first match (typing in the combo textbox), it may still take an additional click of the mouse.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .