Search field in search form will not take a space ???

[ksor]

I have made me a very nice search form - in fact I think I saw the idea in here, but unfortunately I can't find it again ;-((



It's a form with a textbox in the form footer and as you type the shown records changes to only show records with the text in the search field - that's what search forms do - right ?

I experienced that the search field CAN'T take a SPACE - the form just flickers and the search field stays the same (as before pressing the space) - I can't find WHERE the space key is suppressed.

Here are the KeyDown event and I have KeyPreview on the form for moving the focus from record to record with Ctrl+PdDn/Up:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyEscape
           If Me.Dirty Then Me.Undo   ' Cancel any changes
           DoCmd.Close acForm, Me.Form.Name
        Case vbKeyPageUp
            If Shift = acCtrlMask Then
                On Error Resume Next
                DoCmd.GoToRecord Record:=acPrevious
                On Error GoTo 0
            End If
        Case vbKeyPageDown
            If Shift = acCtrlMask Then
                On Error Resume Next
                DoCmd.GoToRecord Record:=acNext
                On Error GoTo 0
            End If
        Case vbKeyReturn
            Form_Close Me, ""
    End Select
End Sub

And here are the Change event for the search field:

Private Sub txtSearchFor_Change()
    Me.Requery
    Me.txtSearchFor.SetFocus
    If Me.Recordset.RecordCount > 0 And Not IsNull(Len(Me.txtSearchFor)) Then
        Me.txtSearchFor.SelStart = Len(Me.txtSearchFor)
    End If
End Sub

All the 'filtering' is done in the query with a criteria like this ON ALL THE FIELDS shown in the search form:

Like "*" & [forms]![PERSON Generelt opslag på personer]![txtSearchFor] & "*"

How can I get this to accept a space key too - in fact I can't see why it shouldn't !

Any ideas ?

[Bulzie]

Add 1 or 2 more quotes to the line?

Like ""*" & [forms]![PERSON Generelt opslag på personer]![txtSearchFor] & "*""

[davegri]

Add this sub:

Private Sub txtSearchFor_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 32 Then
        txtSearchFor = txtSearchFor & Chr$(32)
        txtSearchFor.SelStart = Len(txtSearchFor)
    End If
End Sub

[Micron]

the search textbox is unbound, yes? And not a calculated control, right? And you are not allowing the form filter to be applied?
If not, I'd say that's why. Otherwise, perhaps because you need the .Text and not .Value (which you are using by default).
Also I don't get this - IsNull(Len(Me.txtSearchFor))
If the control is Null or contains an empty string, then the length is 0. Thus your IF condition includes a test for IsNull(0), which can never be true. If it is 6 characters long, IsNull (6) can never be true either, but a control that never had data and hasn't been navigated off of and has text in it is still Null. See next...

Did you step through this and watch what's happening? My guess is that even with characters typed in the search box, it is still Null. Why?
Because that's how it starts out, and the control remains Null until you leave it (otherwise, it never gets updated). If you are leaving it, then sorry, I missed that.
Not sure why you're checking if it's Null anyway, but then we can't see the rest of what's going on around it. Are you sure you shouldn't be checking the length of the .Text property value? At the risk of repeating myself, Len(Me.Textbox) should return Null in your situation.

[ksor]

Add this sub:

Private Sub txtSearchFor_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 32 Then
        txtSearchFor = txtSearchFor & Chr$(32)
        txtSearchFor.SelStart = Len(txtSearchFor)
    End If
End Sub

Thx - that did the job !

[ksor]

the search textbox is unbound, yes? And not a calculated control, right? And you are not allowing the form filter to be applied?
If not, I'd say that's why. Otherwise, perhaps because you need the .Text and not .Value (which you are using by default).
Also I don't get this - IsNull(Len(Me.txtSearchFor))
If the control is Null or contains an empty string, then the length is 0. Thus your IF condition includes a test for IsNull(0), which can never be true. If it is 6 characters long, IsNull (6) can never be true either, but a control that never had data and hasn't been navigated off of and has text in it is still Null. See next...

Did you step through this and watch what's happening? My guess is that even with characters typed in the search box, it is still Null. Why?
Because that's how it starts out, and the control remains Null until you leave it (otherwise, it never gets updated). If you are leaving it, then sorry, I missed that.
Not sure why you're checking if it's Null anyway, but then we can't see the rest of what's going on around it. Are you sure you shouldn't be checking the length of the .Text property value? At the risk of repeating myself, Len(Me.Textbox) should return Null in your situation.

The solution with the KeyUp event works fine - but anyway ...

The search textbox is unbound and not calculated and no filter.

The second half of the IF-condition is there to secure NOT to do this:

Me.txtSearchFor.SelStart = Len(Me.txtSearchFor)

if then Me.txtSearchFor is empty.

[Micron]

Well, I still don't get the check for IsNull(Len(Me.txtSearchFor)) unless all you're doing is trying to determine if the entry starts with a space.
If you start with a space, the test will be IsNull(Null). If you're not checking for that, then for "apple" the test will be IsNull(5), which will never be true. What I have found from playing with your code is that anything that causes the control to be updated (requery, refresh or checking the record count) removes the space regardless if the event is KeyUp or OnChange. That is, no doubt by design because a table field should never end in a space, nor should the field contain only a space as its value.

In other forums, the question seems to be 'why is allowing the space necessary in a find as you type feature?' because the wildcard search takes care of it. However, if you want to return "Acme Widgets" with a search string "Acme W" then you definitely need a hack, or the search becomes a button click on the text box in order to prevent the other events from stripping the space - as long as it's not at the end of the string.