Full Version: Mimic Combo Box Search In Continuous Form
UtterAccess Forums > Microsoft® Access > Access Forms
fritz
I've replaced a combo box with a popup form that allows greater search and sort flexibility.
The continuous form opens with focus set to the control which used to appear in the combo box and sorts the values alphabetically.
Users are asking to be able to type a key (letter in this case) and move down to the first record where that letter appears first in the string. Any suggestions on how to do this?
PS Oops, just realized I put this in query forum by mistake - is there a way to move it? Sorry for any confusion
theDBguy
Hi Harry,
Are you saying that you want the users to type something in the popup form and have the continuous form move to the matching record as they type? That sounds nice and doable but it might suffer from delay or flicker depending on how much records the form has.
Just my 2 cents... 2cents.gif
fritz
Hey DbGuy,
ort of. Let me describe again and see if more clear.
    continuous form opens (read only - all controls containing data are locked)
    records are sorted according to the specific control ([SampleNo] in this case)
    focus is set to [SampleNo] in the first record
    user presses a key
    form evaluates the key and moves down to the first record that matches the key in the first position
    subsequent keystrokes do nothing (although would be nice to keep refining the search further

I'm guessing the steps are something like:
the keystroke is captured, a recordset of the current records is created, the first record matching {keystroke like} is found, and the cursor moves to that bookmarked record
However, I don't know how to do it.
Peter Hibbs
Hi Harry,
Perhaps the Filter Demo in post #3 here will do what you want. It is not exactly the method you describe but it is similar enough.
Peter Hibbs.
fritz
Here's the code as I have it so far (returns error 3077 (Syntax error (missing operator) in expression.)
The Object I'm trying to search is [ProcNo].
Any thoughts on what I'm missing?
CODE
Private Sub SampleNo_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo SampleNo_KeyDown_Error
   Dim intKeyCode As Integer
    Dim rst1 As DAO.Recordset
    
    intKeyCode = KeyCode
    
    Select Case intKeyCode
        Case 38 'up arrow key
                DoCmd.GoToRecord , , acPrevious
        Case 40 'down arrow key
                DoCmd.GoToRecord , , acNext
        Case 64 To 123
            Set rst1 = Me.RecordsetClone
            With Form.RecordsetClone
                .FindFirst "ProcNo like '" & Chr(intKeyCode) & "'*"
                If Not .NoMatch Then Form.Bookmark = .Bookmark
                Close rst1
                Set rst1 = Nothing
                
            End With
    End Select

Side note: pressing up/down arrow keys causes the cursor to move up/down. I trap for 2105 error (eof or bof) and beep at user
{[censored] - sorry for multiple posts ... not sure how that happened}
polant
Hi
Since you do not allow data to be edited on the continuous form and basically you just want to display data, perhaps you would be better off using a listbox.
A listbox has built-in the behaviour you want, i.e. press a key and it takes you to the first row beginning with that key. Also, keep pressing the same key, will advance you to the next row that begins with that key. It is not so easy to implement this on a continuous form with code.
theDBguy
Hi Harry,
Can you post a zip copy of your db with test data?
John Spencer
The following line has an embedded syntax error that cannot be detected by the compiler
FindFirst "ProcNo like '" & Chr(intKeyCode) & "'*"
That last "single quote" mark should be after the asterisk (*) and not before the asterisk.
.FindFirst "ProcNo like '" & Chr(intKeyCode) & "*' "
This assumes that ProcNo is a text value and not numeric. If ProcNo is a number field then you would need a different Where string.
fritz
John, Thanks! that was it. I wondered when I wrote it where it should go and pla nned to try moving it, but forgot to do it
olant - I need greater contol on the fly over the records displayed. I thought about your point of repeating typing a letter to continue moving through and came up a solution. Here it is for anyone who is interested:
{note, ProcNo is a text field and the record source and Name are the same.}
CODE
Private Sub SampleNo_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo SampleNo_KeyDown_Error
    Dim intKeyCode As Integer
    Dim rst1 As DAO.Recordset
    
    intKeyCode = KeyCode
    
    Select Case intKeyCode
        Case 38 '+ moves focus up one record when up arrow is pressed
                DoCmd.GoToRecord , , acPrevious
        Case 40 '- moves focus down one record when down arrow is pressed
                DoCmd.GoToRecord , , acNext
        Case 64 To 123
            Set rst1 = Me.RecordsetClone
            With Form.RecordsetClone
                If Me.ProcNo Like Chr(intKeyCode) & "*" Then
                    .FindNext "ProcNo like '" & Chr(intKeyCode) & "*'"
                Else
                    .FindFirst "ProcNo like '" & Chr(intKeyCode) & "*'"
                End If
                If Not .NoMatch Then
                    Form.Bookmark = .Bookmark
                End If
                rst1.Close
                Set rst1 = Nothing
                
            End With
    End Select
    
On Error GoTo 0
   Exit Sub
SampleNo_KeyDown_Error:
    If err.Number <> 2105 Then 'captures EOF & BOF
        MsgBox "Error " & err.Number & " (" & err.Description & ")"
    Else
        Beep
    End If
End Sub
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.