By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Access Automatic Filtering Problem

P: 5
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
May 2 '06 #1
Share this Question
Share on Google+
15 Replies


P: 36
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.
May 2 '06 #2

P: 5
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
May 3 '06 #3

100+
P: 135
.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
May 3 '06 #4

P: 36
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
Expand|Select|Wrap|Line Numbers
  1. Dim cariText As String
Same code, but moved it to the AfterUpdate event and also added some code (in bold):
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCari_AfterUpdate()
  2.  
  3. If Len(Trim(txtCari.Text)) = 0 Then
  4. Me.FilterOn = False
  5. Me.txtCari.SetFocus
  6. Else
  7. Me.Filter = "Pack Like '" & txtCari.Text & "*'"
  8. Me.FilterOn = True
  9. Me.txtCari.SelStart = 100
  10. End If
  11.  
  12. txtCari = cariText
  13.  
  14. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCari_BeforeUpdate(Cancel As Integer)
  2.  
  3. Dim theText As String
  4. Dim LengthDiff As Integer
  5.  
  6. If Not IsNull(theText) Then
  7.  
  8.     theText = txtCari
  9.     LengthDiff = Len(txtCari.Text) - Len(Trim(txtCari.Text))
  10.  
  11.     If LengthDiff > 0 Then
  12.         cariText = theText & HowManyChars(LengthDiff)
  13.     Else
  14.         cariText = theText
  15.     End If
  16.  
  17. End Sub
Function to return the number of characters entered at the end
of the line
Expand|Select|Wrap|Line Numbers
  1. Private Function HowManyChars(Chars As Integer) As String
  2.  
  3.     Dim t As String
  4.     Dim x As Integer
  5.  
  6.     'Returns the number of spaces specified by chars.
  7.     If Chars > 0 Then
  8.         For x = 1 To Chars
  9.             t = t & Chr(32)
  10.         Next x
  11.     End If
  12.  
  13.     HowManyChars = t
  14.  
  15. End Function
  16.  
May 3 '06 #5

P: 5
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
May 4 '06 #6

100+
P: 135
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCari_KeyUp(KeyCode As Integer, Shift As Integer)
  2.  
  3. If Len(Trim(txtCari.Text)) = 0 Then
  4.     Me.FilterOn = False
  5.     Me.txtCari.SetFocus
  6.  
  7. Else
  8.     If KeyCode = 13 Then
  9.         Me.Filter = "Pack Like '" & Trim(txtCari.Text) & "*'"
  10.         Me.FilterOn = True
  11.         Me.txtCari.SelStart = 100
  12.         Me.txtCari.SelStart = Len(txtCari.Text)
  13.     End If
  14.  
  15. End If
  16.  
  17.  
  18. End Sub
  19.  
May 4 '06 #7

P: 5
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCari_KeyUp(KeyCode As Integer, Shift As Integer)
  2.  
  3. If Len(Trim(txtCari.Text)) = 0 Then
  4.     Me.FilterOn = False
  5.     Me.txtCari.SetFocus
  6.  
  7. Else
  8.     If KeyCode = 13 Then
  9.         Me.Filter = "Pack Like '" & Trim(txtCari.Text) & "*'"
  10.         Me.FilterOn = True
  11.         Me.txtCari.SelStart = 100
  12.         Me.txtCari.SelStart = Len(txtCari.Text)
  13.     End If
  14.  
  15. End If
  16.  
  17.  
  18. End Sub
  19.  
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.
May 4 '06 #8

100+
P: 135
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)
May 4 '06 #9

P: 36
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.
Attached Files
File Type: zip Sample.zip (35.8 KB, 197 views)
May 4 '06 #10

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCari_Change()
  2.  
  3. If Len(Trim(txtCari.Text)) = 0 Then
  4.   Form_frm_SubPack.Filter = ""
  5.   Me.txtCari.SetFocus
  6. Else
  7.   Form_frm_SubPack.Filter = "Pack Like '" & txtCari.Text & "*'"
  8.   Form_frm_SubPack.FilterOn = True
  9.   Me.txtCari.SelStart = 100
  10. End If
  11.  
  12. End Sub
May 4 '06 #11

P: 5
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
May 4 '06 #12

100+
P: 135
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?
May 4 '06 #13

P: 36
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.
May 4 '06 #14

100+
P: 135
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!
May 4 '06 #15

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

Thanks :)
May 4 '06 #16

Post your reply

Sign in to post your reply or Sign up for a free account.