?
Solved

Prevent textboxes from being auto-trimmed

Posted on 2006-07-21
12
Medium Priority
?
796 Views
Last Modified: 2012-05-05
I've created a function on one of my continuous database forms that filters the results as you type in a textbox (using the onChange event).  The problem here is that when the event fires, it seems to momentarily cause the textbox to lose focus, which then has all spaces trimmed from the beginning and end of the box.  Because of this, a user cannot filter by more than one word because everytime a space is typed, it just disappears when the change event fires.

Does anyone know how to either: stop the onChange event from firing when a space is typed, prevent the textbox from losing focus, or stop Access from auto-trimming the field?

Thanks.
0
Comment
Question by:RandomPsychology
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 17154566
Hi RandomPsychology,

I'd be quite surprised if Access was trimming on its own. Are you sure you don't have a Trim() function in your code?


Pete
0
 
LVL 2

Author Comment

by:RandomPsychology
ID: 17154601
Quite sure.  Try it yourself.  Once focus leaves a field, all spaces on the end are trimmed.
0
 
LVL 58

Expert Comment

by:harfang
ID: 17154720
Hello Random,

Yes, this is how Access treats all text input. Trailing spaces are removed.

You do no want to prevent the on change event from firing, because that is the event you use, in fact. What you need to do is not leave the control at all. As long as the focus remains in the control, you can type anything you like.

Perhaps you can post here the code you use, and we can tweak it so that you do not need to mangle with the control focus?

Cheers!
(°v°)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:peter57r
ID: 17154761
It doesn't do the trim in A2003.
What version are you using?

(I agree that when you exit the control Access will trim, by the way)

Pete
0
 
LVL 2

Author Comment

by:RandomPsychology
ID: 17154872
I'm using Access 2003 (database version is Access 2000 format)

Here's the code that runs when the onChange event fires.

Private Sub txtSearchDesc_Change()
    On Error Resume Next
    If Me.txtSearchDesc = "" Then
        GlobalFilter(4) = ""
    Else
        GlobalFilter(4) = "[BlockDesc] Like '*" & Me.txtSearchDesc & "*'"
    End If
    Call WriteFilter
    Me.txtSearchDesc.SelStart = Len(Me.txtSearchDesc)
End Sub
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17155047

Looks like you are using a bound textbox then - i hadn't expected that, but I don't think it changes anything.
If you temporarily comment out your procedure and use:

Private Sub txtSearchDesc_Change()
    msgbox len(me.txtsearchdsc.text)
End Sub

 You will see that spaces stay in the mix until you exit the textbox.

Therefore as markus suggests, you current code must cause the focus to shift and when it shifts it does the trim.

Yiu need to look at WriteFilter.

Pete
 
0
 
LVL 2

Author Comment

by:RandomPsychology
ID: 17155247
I'll paste the code for WriteFilter too, but basically it's the method that causes the form to update based on the typed text.  

Private Sub WriteFilter()
    Dim FilterString As String
    Dim i As Integer
   
    For i = 0 To UBound(GlobalFilter)
        If Not FilterString = "" And Not GlobalFilter(i) = "" Then
            FilterString = FilterString + " AND "
        End If
       
        FilterString = FilterString + GlobalFilter(i)
    Next i
   
    If FilterString = "" Then
        Me.Form.FilterOn = False
    Else
        Me.Form.Filter = FilterString
        Me.Form.FilterOn = True
    End If
End Sub

As you can see, it doesn't shift the focus by any commands, unless the focus gets shifted when the form is filtered.  I also tried checking for a space " " at the end of the textbox and immediately exiting the sub if one was found (that is the onChange event subroutine), but that didn't work either, because it seems that either the space got trimmed out when brought into the subroutine, or the textbox doesn't "officially" add the character to the string until after the event completes (if that makes any sense whatsoever).

Anybody have some more ideas?
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 17155677
Ah, well. It doesn't touch the focus as such, but it does enforce a new filter. When that happens, the current control exits an enters again. I think there is no way to obtain what you want.

Typically, this sort of things would be done on a subform from the main form, or perhaps from an unbound control for a listbox.l However, you cannot do that from a control to the current form.

Sorry, you will have to redesign your form. Probably as a subform from a mainform containing all your "tools", including txtSearchDesc.

Good luck!
(°v°)
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 17163200
Although you can't filter a form, you can use it to filter a listbox.

The user could then select a record from teh listbox and see the rest of that records "details".

Does that make sense?

0
 
LVL 2

Author Comment

by:RandomPsychology
ID: 17176141
Yes, I thought about doing that, but it isn't really what I'm looking for.  I want to do a 'live-search' feature kindof like the one in the newer versions of Outlook
0
 
LVL 2

Author Comment

by:RandomPsychology
ID: 17177970
I did some redesign of my form (moving the data into a subform and putting my filter fields on the main form) and solved the problem.  I appreciate all the good comments, but I think the credit for this solution should go to harfang.  I guess this proves that "Live Filtering" is attainable in MS Access!!  Thanks everyone!
0
 
LVL 58

Expert Comment

by:harfang
ID: 17182124
Glad to help, good luck with your project!
(°v°)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question