Combo box filtering

[Tezzie]

I have a table called 'Parts', with a field called description. I have a combo box looking at this field and for arguments sake lets say the following items are in the table

Bolt 13mm
Bolt 15mm
Screw 25mm

While I can start typing in the description and get the first match that access finds, what im looking for is the combo box to dropdown with a refinded selection ie,

At the moment if I type bol the dropdown gives all 3 items. I would prefer the dropdown to limit the items displayed to only

Bolt 13mm
Bolt 15mm

Theres a shop nearby who's invoicing system / POS that does this and I was hoping to making something like it.

Can you help?

Many thanks

Terry

 

[hillsy]

I use this on a form with a list box and text boxes above each column. This will work for a combo box.

The idea behind this is that when a user types something in a textbox/combo and keeps typing, a list box in the case which I use, filters out everything based on what the user has typed.

*********************************************************

Option Compare Database
Option Explicit
Dim prevText As TextBox, str As Byte

Public Function FilterMe()
On Error Resume Next
Dim I As Integer, strsql As String, strFilter As String, valFieldName(1), valCriteria(1)
'This function will filter a list or combo box based on what is typed in a list box.
'Changing the valFieldName(?) and valCriteria(?) to a higher number will indicate that there are more text boxes
'that require filtering. Using txtFind1 as the active control

'In the case of a combo box to replace the list box, List0 will be replaced by ComboBoxName and txtFind1 will be ComboBoxName

If str <> 32 Then
valFieldName(1) = &quot;TABLENAME.FIELDNAME&quot; 'Enclose table and field name in quotes

If Me.ActiveControl.Name = &quot;txtFind1&quot; Then valCriteria(1) = Me.txtfind1.Text: Set prevText = Me.txtfind1

For I = 1 To 1 'Start at 1 and loop through the same amount as valFieldName(?) and valCriteria(?)
If Len(valCriteria(I)) <> 0 Then
valCriteria(I) = valCriteria(I) & &quot;*&quot;
strFilter = strFilter & valFieldName(I) & &quot; LIKE '&quot; & valCriteria(I) & &quot;'&quot;
End If
Next I

If Len(strFilter) = 0 Then
List0.RowSource = &quot;COMBO SELECT STATEMENT&quot; 'Replace this with the entire rowsource of the combo box
'e.g. &quot;SELECT tblOrders.* from tblOrders;&quot;
Me.Refresh
Else
strsql = &quot;COMBO SELECT STATEMENT WHERE &quot; & strFilter 'Replace this with the rowsource of the combo box and include the WHERE &quot; & strFilter bit
'e.g. &quot;SELECT tblOrders.* FROM tblOrders WHERE &quot; & strFilter
List0.RowSource = strsql
Me.Refresh
prevText.SetFocus
prevText.SelStart = Nz(Len(Me.ActiveControl))
End If

End If
End Function

Public Function ClearTextbox(ctlText As TextBox)
On Error GoTo MyEH

If (prevText.Name <> ctlText.Name) Then
Me.txtfind1 = Null

End If
Exit Function

MyEH:
Set prevText = ctlText
Resume

End Function


Private Sub txtfind1_KeyDown(KeyCode As Integer, Shift As Integer)
str = KeyCode
End Sub

Private Sub txtfind1_KeyUp(KeyCode As Integer, Shift As Integer)
str = KeyCode
End Sub

*********************************************************

On the property page of the combo box, put this in each property (not in code but directly in the property page)

Property: On Change
Type: =FilterMe()

Property: On Got Focus
Type: =ClearTextBox([txtFind1])

Property: Allow AutoCorrect = No

Property: Key Down and Key Up - the code for this is above.

There is a bit of rework in this but it does work.

This will not only requery the combo/list box, but also give you that type ahead feature as you go (the cursor moves to the right of the words typed, not back to the left of the box).

I hope this makes sense because explaining it on here is not that easy.