CODEWITHSUNDEEP
ms-accessvbams-access-2013
Writex
Writex

Reputation: 43

Sorting Access form by typing a single letter

I'm seeking advice for sorting an Access (2013) form's data source by accepting a single letter typed by the user. This question is more about a design strategy more than actual code, although code samples may be helpful as well.

The system I developed for a client has a form with a dataset that is sorted by a set of parameters I defined. All works and I have provided some real-time resorting methods for the users which can dynamically re-sort the bound dataset as needed. Again, all is working fine.

However, the system they used prior to this (a green screen terminal application believe it or not) had one feature they still miss very much and which should be easy to implement. That feature is to see the data on the screen of customers, for example, (which is a very long list) and to be able to either (1) type the first letter of the last name to automatically "scroll down" to that part of the list or (2) to filter the dataset for that first letter ALL BY TYPING JUST THAT LETTER. I proposed creating an unbound field they could mouse to, type a letter and hit enter but the feedback was that it takes time to mouse to, click, type and press enter. I proposed making buttons of each letter they could click to accomplish the same goal but again the same feedback.

This feature does make sense. If you have a list of 8,000 customers and you want to get quickly to the people with a last name starting with the letter "R" for instance, mousing to a scroll bar and dragging can become tedious for users doing this all day long. Instead, it would be a good idea if they could simply press the R key and go straight there.

I suspect the answer is simple but I just haven't found it. Is it setting a keydown event somewhere?

Upvotes: 0

Views: 698

Answers (2)

Nathan Tuggy
Nathan Tuggy

Reputation: 2244

Set the form's KeyPreview property to True to give it a first shot at any keystrokes, then add a KeyDown event handler to the form like this (untested):

Private Sub Form_KeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer)
    Dim NoTextBoxFocused As Boolean
    ' Make sure we're not stealing text from focused control
    NoTextBoxFocused = TypeName(ActiveControl) <> "TextBox" And _
                       TypeName(ActiveControl) <> "ComboBox"
    If KeyCode >= vbKeyA And KeyCode <= vbKeyZ And NoTextBoxFocused Then    
        ' PSEUDOCODE: Sort here, presumably case-insensitive;
        '   otherwise, check for capitals with this:
        '   If (Shift And acShiftMask) <> 0 Then

        KeyCode = 0     ' Turns off further handling
    End If
    ' Otherwise, fall through without doing anything else
End Sub

You might also want to expand this to a full incremental search, complete with a label or textbox shown only when searching which filters progressively as the users type in the first few letters of whatever they want. If you show a textbox, make sure it's easy to start a new incremental search (with Esc or perhaps just a timeout). That should help wean them off missing the old program.

Upvotes: 1

Parfait
Parfait

Reputation: 107587

Simply use any control (combobox, listbox, textbox, button) and in an AfterUpdate Or OnClick Event run an ApplyFilter embedded macro or vba RunCommand using an SQL LIKE clause:

ComboBox: Letter Filter
Row Source: "A";"B";"C"; ... "Z"
Row Source Type: Value List

ApplyFilter
   Filter Name: <blank>
   Where Condition: ="[LastName] LIKE '" & [Forms]![Customers]![LetterFilter] & "*'"
   Control Name: <blank>

Alternatively, with same controls and trigger events, update the form's recordsource:

 Recordsource: SELECT * FROM [Customers] 
              WHERE [LastName] LIKE [Forms]![Customers]![LetterFilter] & '*'

OR synonymous query using Left()

 Recordsource: SELECT * FROM [Customers] 
                  WHERE Left([LastName], 1) = [Forms]![Customers]![LetterFilter]

OR SQL query in VBA:

 Forms!Customers.Form.RecordSource = "SELECT * FROM [Customers] 
                  WHERE [LastName] LIKE '" & [Forms]![Customers]![LetterFilter] & "*'"

Remember setting RecordSource is not in VBA but the query window

Upvotes: 0

Related Questions