Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Netherlands
    Posts
    10

    Unanswered: Update RecordSource on Keypress ??

    In a form, I have a listbox with all records.
    I want to limit the recordsource of it .
    I'm trying to do this on every keypress in a unbound textbox (named strFilter), creating a new recordsource with a Where clause

    Private Sub strFilter_KeyPress(KeyAscii As Integer)
    Dim stWhere As String
    stWhere = "((tTable.fField) like '*' & '" & strFilter & "' & '*' )"
    Me.ListBox.RowSource = "SELECT tTable.Id," & _
    " tTable.fField "& _
    " FROM tTable " & _
    " WHERE " & stWhere
    Me.Listbox.Requery
    End Sub

    The idea is that on every keypress the amount of records in the listbox is getting smaller, so it gets easier to pick one record.

    My problem is that my textbox shows only one letter and returns to the first position

    help please ???...

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't know where this gets us but:

    i tried...
    private sub inBox_keyPress(keyAscii as integer)
    msgbox keyAscii
    end sub
    - inBox behaved normally (i.e. accumulated successive chars typed)

    then i tried...
    private sub inBox_keyPress(keyAscii as integer)
    outBox.value = inbox.value
    end sub
    - inBox behaved normally, outBox took the .value of inBox BEFORE the first _keyPress and froze there

    finally, i tried...
    private sub inBox_keyPress(keyAscii as integer)
    outBox.value = outBox.value & chr$(keyAscii)
    end sub
    - inBox & outBox behave normally

    ...consider feeding a hidden strFilter box with keyAscii from another visible box.

    izy

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    gotit - it's a lost/regained focus problem: here is a workaround:

    private sub inbox_keyPress(keyAscii as integer)
    outBox.value = outBox.value & chs$(keyAscii)
    end sub

    private sub outbox_afterUpdate()
    outbox.setfocus 'to simulate your lost focus when filter/requery

    'do your filter & requery stuff here

    inbox.setfocus
    inBox.value = outBox.value
    end sub

    izy

  4. #4
    Join Date
    Jan 2003
    Location
    Netherlands
    Posts
    10
    Many thanks for your reply!
    I'm going to try it tonight.

    greetings from holland,
    Henny

  5. #5
    Join Date
    Jan 2003
    Location
    Netherlands
    Posts
    10

    Thumbs up

    Izy, WE did it!

    Your last code did not do the job, but you have pointed me in the right direction.
    You were right about the focus, but I found a way to stay in the strFilter textbox.

    I have created an additional (invisible) textbox, called strOutbox.

    Then, the only code I had to write is:


    Private Sub strFilter_KeyPress(KeyAscii As Integer)

    ' catch the backspace
    If strOutbox <> "" Then
    If Val(KeyAscii) = 8 Then strOutbox = Left(strOutbox, Len(strOutbox) - 1)
    End If

    ' catch all other characters
    strOutbox.Value = strOutbox.Value & IIf(Val(KeyAscii) > 31 And Val(KeyAscii) < 127, Chr$(KeyAscii), "")

    ' redefine the rowsource of the listbox
    Listbox.RowSource = "SELECT tTable.Id," & _
    " tTable.fField "& _
    " FROM tTable " & _
    " WHERE ((tTable.fField) Like '*' & '" & strOutbox & "' & '*' )" & _
    " ORDER BY tTable.fField;"
    'Listbox.Requery ' we don't need this...
    End Sub

    Thanks again for your time.

    Henny.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •