KeyDown: goto record with first letter of key pressed

[steveg242]

Hi,

Maybe this is obvious but I'm having trouble getting started. I searched through the forums but couldn't quite find what need. I'm looking for a simple routine, preferably in the On Key Down or or On Key Press event of a form to quickly navigate to a record based on the key pressed.

I have a form with names in a table layout (continuous form).

If the user is on the name field, I'd like it to navigate to the first record that contains first letter of the key pressed. So if a user presses "K", the form will go to the first record that starts with a K.

Yes, the names in the form are alphabetical, but in a list with over 100 names, this could help speed things up just a bit and save a lot of scrolling and looking.

I was thinking of using KeyCode and I already have an event to navigate by the arrow keys so I'm considering building on that.

If KeyCode = 40 Then ' Down arrow
    DoCmd.GoToRecord acForm, Me.Name, acNext
ElseIf KeyCode = 38 Then ' Up arrow
    DoCmd.GoToRecord acForm, Me.Name, acPrevious
End If

I am thinking to use a Select Case and add the key codes for all the letters, but I'm not quite sure how to identify the appropriate record.

 

[missinglinq]

The prolific Allen Browne has a hack to do just that thing:

http://allenbrowne.com/AppFindAsUType.html

Linq ;0)>

 

[steveg242]

Thanks, missinglinq

And, sorry for the lengthy time to respond...I am finally digging back into this...

It's a good feature, but rather than filtering the data, what I'm attempting to do is type a letter and navigate specifically to that record. So if you're on the name field, and press "Z", it will go to the first record of a name that starts with a Z (if there are any).

 

[nanscombe]

You could try something like ...

Private Sub yourKeyPressField_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strCharacter As String
strCharacter = Chr(KeyCode)

If (strCharacter >= "A") And (strCharacter <= "Z") Then
    
    With Me.RecordsetClone
        
        .FindFirst "[yourSearchField] like '" & strCharacter & "*'"
        If .NoMatch Then

            MsgBox "No records start with '" & strCharacter & "'"

        Else

            Me.Bookmark = .Bookmark
            Me.yourKeyPressField.SetFocus

        End If
    
    End With
End If

End Sub

 

[steveg242]

Awesome! Big thanks!
I knew it had to be something rather simple, I just couldn't think how to do it. That did the trick.

 

[Mihail]

May I ask how to plan to edit the data if, when you press a key, you will jump to other and other and other... record ?
I think that Nigel's code is useful, but you need also to check if one of the "shift" key is pressed. If not, no jump.

 

[nanscombe]

The way my code works is that the cursor needs to be in a specific textbox, possibly in the Form header or footer, to work. Anywhere else and it won't jump.

The KeyDown event (the link does say Form but it works for other controls as well) passes both Keycode (which key is pressed) and Shift information separately so, whether the shift key is pressed or not, the Keycode will be the same.

I use the Setfocus method to jump back to Textbox, for another key press, otherwise the next time a key was pressed you would edit the record.

 

[Mihail]

@Nigel
Now I understand how you think.

But still I think that, by using a "shift" key, this can be done from the "regular" text box (that text box that is bound to the field). And still I think that this is a better approach because avoid one more text box and the using of mouse to select that text box. Simple, if the user press CTRL+Key the jump is done, if press only the Key he edit the record. Of course the user must be instructed about this but a tip text should be enough.

You already know that I accept any criticism .

 

[nanscombe]

It could be down by using the Control key, I've just tried it.

However "Ctrl A" selected all of the records, "Ctrl S" saved the form, "Ctrl P" tried to print the records.

You can, however, over-ride the in-built Access functionality to get it working by setting the Form's Key Preview property to Yes. But, as I said, you lose the usual functionality.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strCharacter As String, isControlPressed As Boolean
isControlPressed = ((Shift And acCtrlMask) > 0)
If Not isControlPressed Then Exit Sub

strCharacter = Chr(KeyCode)

If (strCharacter >= "A") And (strCharacter <= "Z") Then
    KeyCode = 0 ' Makes Access ignore the key
    
    With Me.RecordsetClone
        
        .FindFirst "[yourSearchField] like '" & strCharacter & "*'"
        If .NoMatch Then
            MsgBox "No record begining with '" & strCharacter & "'"
        Else
            Me.Bookmark = .Bookmark
        End If
    
    End With
End If

End Sub

 

[nanscombe]

You could, of course, add a toggle button to change the Form's Key Preview property to turn the search capability on and off if you still needed to use the Ctrl shortcuts for printing etc.

 

[Mihail]

I have attempted to kiss myself because your answers.
Thank you ! (with a smile )

 

[nanscombe]

What you do in private is your own business.

(Yes, I may have an odd sense of humour. )