Filtering by keystroke

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a number of list boxes and subforms displaying records from tables.
There seems to be an automatic filter which allows the user to type a key
with the focus in the listbox or subform, and the list will move to the
beginning of that letter. eg if "x" is typed the listbox / subform will go
to the beginning of the "x"s in the list. I don't know much on how filters
work but I assume there is a property that is set to allow this. My problem
is that the lists in some cases are very long. I would like to filter more
than one keystroke. eg type "XX" and that would go to the records beginning
with "xx", and maybe even more refining. "xxxxa" etc .... How do I do this?
Will I need to write code to concatenate key preview values and apply the
filter via code? (Sorry, I originally started as a VB programmer and can
figure out how to do this in VB but in Access they sometimes have nice little
automatic features which mean I dont have to write complex code)
 
-----Original Message-----
I have a number of list boxes and subforms displaying records from tables.
There seems to be an automatic filter which allows the user to type a key
with the focus in the listbox or subform, and the list will move to the
beginning of that letter. eg if "x" is typed the listbox / subform will go
to the beginning of the "x"s in the list. I don't know much on how filters
work but I assume there is a property that is set to allow this. My problem
is that the lists in some cases are very long. I would like to filter more
than one keystroke. eg type "XX" and that would go to the records beginning
with "xx", and maybe even more refining. "xxxxa" etc .... How do I do this?
Will I need to write code to concatenate key preview values and apply the
filter via code? (Sorry, I originally started as a VB programmer and can
figure out how to do this in VB but in Access they sometimes have nice little
automatic features which mean I dont have to write complex code)
.
Click to expand...
Hi Gemma,
consider creating a combobox to use as a filter for your
list. A great resource for this is the Access Web...

http://www.mvps.org/access/forms/frm0028.htm

Luck
Jonathan
 
I am not looking to create any additional controls. I am looking to filter
on my original list box. Can anyone else help? Or is my question a bit
obscure? Basically I have seen a system work in the way I described but I
was not able to see the source code.

I want the user to be able to type on the keyboard while the focus is on a
list box and the keystrokes used to filter the list.
 
OK, have found a solution if anyone is interested.

I use the keypress event for the listbox. A string has been defined at the
module level (i.e.global) This is used to concatenate the keyascii
characters. I use the Chr function to convert the ascii character number to
a string.
using the module string I update the rowsource property for the listbox
using a where clause and LIKE, and requery the listbox. This filters the
listbox based on the concatenated keyascii values. I clear the 'filter' when
the user presses the escape key. (no. 27)

Private Sub List0_KeyPress(KeyAscii As Integer)

If KeyAscii = 27 Then 'escape
strSearchString = "SELECT CustomerID, CustName FROM tblCustomer;"
List0.RowSource = strSearchString
List0.Requery
strSearchString = ""
strConcat = ""
Else
If KeyAscii > 31 And KeyAscii < 127 And KeyAscii <> 34 Then
strSearchString = "SELECT CustomerID, CustName FROM tblCustomer "
strConcat = strConcat & Chr(KeyAscii)
strSearchString = strSearchString & "where CustName LIKE '" & strConcat &
"*';"
List0.RowSource = strSearchString
List0.Requery
End If
End If

End Sub


A bit long winded but it works. But is there an easier way?
 
Back
Top