Access Automatic Filtering Problem

[fbachri]

Hi,

I have a form with 1 textbox in the header form area and data in continuous form style in detail area. i would like to make an automatic filter. so when i type a character the data in the detail area would be filtered. for example: i type a letter A in the textbox then the data would be filtered with the data started with an A, and if i continue typing for example : ABB then the data would be filtered with the data started with ABB and so on.
Actually i already have a code for this, but there is a little weakness on the code, the code could not accept a SPACE character.
Below is the code:

Private Sub txtCari_Change()
If Trim(txtCari.Text) = "" Then
Me.FilterOn = False
Me.txtCari.SetFocus
Else
Me.Filter = "Pack Like '" & txtCari.Text & "*'"
Me.FilterOn = True
Me.txtCari.SelStart = 100
End If
End Sub


the code above fails when i type for example ABB then i press Space button. the character in the textbox only appear ABB without a SPACE character. it seems that the code automatically delete the SPACE character.

Please Advise how to solve this problem

regards,
fbachri

[cweiss]

You could try this:

Private Sub txtCari_Change()

If Len(Trim(txtCari.Text)) = 0 Then
Me.FilterOn = False
Me.txtCari.SetFocus
Else
Me.Filter = "Pack Like '" & txtCari.Text & "*'"
Me.FilterOn = True
Me.txtCari.SelStart = 100
End If

End Sub

It'll test the length after you trim the text. If only spaces are entered, the length will be zero and it will reset the filter, otherwise it'll filter for what you entered, including spaces.

[fbachri]

You could try this:

Private Sub txtCari_Change()

If Len(Trim(txtCari.Text)) = 0 Then
Me.FilterOn = False
Me.txtCari.SetFocus
Else
Me.Filter = "Pack Like '" & txtCari.Text & "*'"
Me.FilterOn = True
Me.txtCari.SelStart = 100
End If

End Sub

It'll test the length after you trim the text. If only spaces are entered, the length will be zero and it will reset the filter, otherwise it'll filter for what you entered, including spaces.

Thanks Cweiss,

but unfortunately your code still not work when i type character including space. the space character still automatically eliminated.

well i have upload the sample form of my access application. maybe you can help to check it.

this the link:

http://s63.yousendit.com/d.aspx?id=0...O3T0MRJMW8S3XT

thanks in advance
Regards,
fbachri

[CaptainD]

.SelStart sets the starting point to the end of the existing text so it kicks back to the last character.

http://msdn.microsoft.com/library/de...1df3adc5aa.asp

[cweiss]

Ah sorry, I misunderstood the problem.

Hmm, I've never run into that before. The only way I saw to work around it (and this probably isn't the best solution) was to create a form-level variable to hold the original value in the textbox, append the appropriate number of spaces to it, and then set txtCari back to that value after it finished filtering.

I had to move your code from the Change event to the AfterUpdate event to get it to function properly, but it could probably work in either with some tinkering.

This doesn't seem like an ideal solution, and really convoluted. I would think there would just be a property to keep it from truncating spaces. Hopefully somebody else knows if there is.

Here's the code I added to your form (can't upload the db here, too big):

Form level variable, placed at top

Dim cariText As String

Same code, but moved it to the AfterUpdate event and also added some code (in bold):

Private Sub txtCari_AfterUpdate()
 
If Len(Trim(txtCari.Text)) = 0 Then
Me.FilterOn = False
Me.txtCari.SetFocus
Else
Me.Filter = "Pack Like '" & txtCari.Text & "*'"
Me.FilterOn = True
Me.txtCari.SelStart = 100
End If
 
 txtCari = cariText
 
End Sub

Dunno if it has to go here, but figured it made the most sense. This handles setting cariText to the original value in the textbox:

Private Sub txtCari_BeforeUpdate(Cancel As Integer)
 
Dim theText As String
Dim LengthDiff As Integer
 
If Not IsNull(theText) Then
 
    theText = txtCari
    LengthDiff = Len(txtCari.Text) - Len(Trim(txtCari.Text))
 
    If LengthDiff > 0 Then
        cariText = theText & HowManyChars(LengthDiff)
    Else
        cariText = theText
    End If
 
End Sub

Function to return the number of characters entered at the end
of the line

Private Function HowManyChars(Chars As Integer) As String
 
    Dim t As String
    Dim x As Integer
 
    'Returns the number of spaces specified by chars.
    If Chars > 0 Then
        For x = 1 To Chars
            t = t & Chr(32)
        Next x
    End If
 
    HowManyChars = t
 
End Function
 

[fbachri]

Ah sorry, I misunderstood the problem.

Hmm, I've never run into that before. The only way I saw to work around it (and this probably isn't the best solution) was to create a form-level variable to hold the original value in the textbox, append the appropriate number of spaces to it, and then set txtCari back to that value after it finished filtering.

I had to move your code from the Change event to the AfterUpdate event to get it to function properly, but it could probably work in either with some tinkering.

This doesn't seem like an ideal solution, and really convoluted. I would think there would just be a property to keep it from truncating spaces. Hopefully somebody else knows if there is.

Here's the code I added to your form (can't upload the db here, too big):

Thanks again cweiss,

Your code now could handle a character with space but unfortunately the aim of make the filter automatic is could not be reached. because we need to type it first all the word then press ENTER then the filter will work.
What i would like is (maybe i illustrated again :) ) :
for example:
I want to tipe "ABBOTIC IV" in the text box so when
1. I type the first character A in the textbox the details area automatically
filtered with data started with an A
2. then i type the second character so the textbox would be AB then again
the details area automatically filtered with data started with AB
3. then i type the third character so the textbox would be ABB then again
the details area automatically filtered with data started with ABB
4. then i type the fourth character so the textbox would be ABBO then again
the details area automatically filtered with data started with ABBO and so
on.
the filter work without have to press the ENTER button, the filter process is automatically work when there is a change happen in the textbox. everytime you press a character in your keyboard, the code automatically filter the data with all characters in the textbox.
the first code (the code that put in the CHANGE event) works ok until i finish type ABBOTIC, but when i press the space button to get the text become "ABBOTIC I". it's always automatically the space character deleted. so i only can type ABBOTICI than ABBOTIC I.
That's actually the problem i have with the code.

I hope, my explanation is understandable :)

Please Advise

[CaptainD]

I played with your sample program for a while and tried different methods to get around the "SelStart" issue with no sucess. The problem is, when you filter the form the text box loses focus and then regains focus after the form is filtered. That is why the text gets "highlighted" and without .Selstart, when you type it over writes the text entered. You don't have that problem with using a combobox or sending the information to the filter from a click event.

One possible solution would be to use the enter key to send the information to the filter instead of a click event.

Private Sub txtCari_KeyUp(KeyCode As Integer, Shift As Integer)
 
If Len(Trim(txtCari.Text)) = 0 Then
    Me.FilterOn = False
    Me.txtCari.SetFocus
 
Else
    If KeyCode = 13 Then
        Me.Filter = "Pack Like '" & Trim(txtCari.Text) & "*'"
        Me.FilterOn = True
        Me.txtCari.SelStart = 100
        Me.txtCari.SelStart = Len(txtCari.Text)
    End If
 
End If
 
 
End Sub
 

[fbachri]

I played with your sample program for a while and tried different methods to get around the "SelStart" issue with no sucess. The problem is, when you filter the form the text box loses focus and then regains focus after the form is filtered. That is why the text gets "highlighted" and without .Selstart, when you type it over writes the text entered. You don't have that problem with using a combobox or sending the information to the filter from a click event.

One possible solution would be to use the enter key to send the information to the filter instead of a click event.

Private Sub txtCari_KeyUp(KeyCode As Integer, Shift As Integer)
 
If Len(Trim(txtCari.Text)) = 0 Then
    Me.FilterOn = False
    Me.txtCari.SetFocus
 
Else
    If KeyCode = 13 Then
        Me.Filter = "Pack Like '" & Trim(txtCari.Text) & "*'"
        Me.FilterOn = True
        Me.txtCari.SelStart = 100
        Me.txtCari.SelStart = Len(txtCari.Text)
    End If
 
End If
 
 
End Sub
 

Thanks CaptainD,

Your code has the same result with the latest cweiss's code. maybe thats the possible way for my problem if i want the code could filter the data with character that have a SPACE character.
But i'm still have a curiosity how to make it full automatic without have to pressing the ENTER button.
Well, this kind of filter was inspired by a database application that use in my office. i'm just trying to get the idea in a microsoft Access form. a simple one i think but it's quiet difficult in the reality.

[CaptainD]

I have written VB 6.0 programs that work that way but MS Access and it's VBA are different then VB in many ways.

I also tried using SQL strings as a recordsource and still run into the same problem, changing the recordsource moves the focus to the form and back (after setting all the other tab stops to false)

[cweiss]

Hmm, well, this is a different approach but it seems to work, put the section you want to filter in a subform, then filter the subform. I attached the DB so you could see what I'm talking about. You'll have to adjust it to get it to look like you want.

[cweiss]

Also, I forgot to put to make this modification, but you need to change this part of your code (in bold):

Private Sub txtCari_Change()
 
If Len(Trim(txtCari.Text)) = 0 Then
  Form_frm_SubPack.Filter = ""
  Me.txtCari.SetFocus
Else
  Form_frm_SubPack.Filter = "Pack Like '" & txtCari.Text & "*'"
  Form_frm_SubPack.FilterOn = True
  Me.txtCari.SelStart = 100
End If
 
End Sub

[fbachri]

Hmm, well, this is a different approach but it seems to work, put the section you want to filter in a subform, then filter the subform. I attached the DB so you could see what I'm talking about. You'll have to adjust it to get it to look like you want.

Wow thanks a lot cweiss, Finally my problem is solved.
Great trick...never realize that trick in my mind :)

Thanks,
fbachri

[CaptainD]

cweiss, I tried to download your fix but it says the file is corupt.

Reguardless, you are still using SelStart to place the curser at the end of the text. It should be removing the space per (
http://msdn.microsoft.com/library/d...a1df3adc5aa.asp), since it appears to be working, do you have any idea why?

[cweiss]

I've never used .SelStart (never knew about it til now :D ), so I'm not sure.

As near as I could tell though, whenever a textbox is updated it automatically truncates any leading spaces. Since his code was in the Change event, and referencing the same form, the textbox would automatically update and truncate the space.

Apparently this doesn't happen if you filter a subform.

Basically what I did was add a subform with the two textboxes he wanted to filter, and changed the code to set the filter for the subform instead of Me.

[CaptainD]

cweiss, your code you posted still shows, SelStart

Private Sub txtCari_Change()

If Len(Trim(txtCari.Text)) = 0 Then
Form_frm_SubPack.Filter = ""
Me.txtCari.SetFocus
Else
Form_frm_SubPack.Filter = "Pack Like '" & txtCari.Text & "*'"
Form_frm_SubPack.FilterOn = True
Me.txtCari.SelStart = 100
End If

End Sub

That's why I'm cunfused, Hmmmm, as long as it works, good job!

[cweiss]

Ahh sorry, that was in his original code, I just saw no need to modify it.

Thanks :)