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

How to filter a form based on user input when the input can occur in any format?

P: 99
I have a form which displays a column named A_JobNo(e.g. value: "E1Y4000") from a table in a textbox.There is an input mask applied to this texbox:
>L\-0A\-0000
which makes the value appear like "E-1Y-4000" on the form.
Now, I need a textbox/combobox for users to enter a value in it so that the form is filtered based on that value.
What I am not able to understand is how do I apply filter as the value entered by users can be in any format.How do I search the record for the input or how to put any delimiter so that the value can be filtered?
Mar 16 '12 #1

✓ answered by NeoPa

If you are using a TextBox and the user is typing data in either format (IE. with or without the extra hyphens '-') then simply lose the hyphens from the value of the TextBox before using it in the filter. I'll give some example code which I hope you will find easy to convert to your situation. If you struggle then simply ask for clarification, but remember next time you ask a question that such information should be included if you want the help to be tailored specifically to your requirements :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtJobNoFilter_AfterUpdate()
  2.     Dim strFilter As String
  3.     Dim blnFilterOn As Boolean
  4.  
  5.     With Me
  6.         strFilter = Replace(Trim(.txtJobNoFilter), "-", "")
  7.         blnFilterOn = (strFilter > "")
  8.         .Filter = IIf(blnFilterOn, _
  9.                       Replace("[JobNo] Like '%F*'", "%F", strFilter), _
  10.                       "")
  11.         .FilterOn = blnFilterOn
  12.     End With
  13. End Sub

Share this Question
Share on Google+
22 Replies


NeoPa
Expert Mod 15k+
P: 31,417
Please clarify whether you are using a TextBox or a ComboBox as the answers to each are very different. Otherwise, the question is clear.
Mar 16 '12 #2

P: 99
I wish to implement the provision of intellisense in the textbox.Since I have no idea how to do that, I am using a combobox right now.
So, it's a combobox right now.
Mar 17 '12 #3

P: 51
'im a newbie in vba but..hehe..
'let me try this..
'PS: set your tablename and fieldname.
'create a textbox rename it to txtboxSearch
'put this code in your txtboxSearch code in action Change..
Expand|Select|Wrap|Line Numbers
  1. Dim StrToSearch As String
  2. StrToSearch = Trim(txtboxSearch.Text)
  3.  
  4. if StrToSearch <> "" Then
  5. my_tbl.Filter = "fld_name Like '" & StrToSearch & "%'"
  6.  
  7. Else
  8. my_tbl.Filter = "fld_name <> '123'"
  9. End If
'i hope it helps.. =)
Mar 17 '12 #4

P: 99
What's my_tbl? If that is supposed to be the name of a table, I doubt it will work.I just tried doing the same and found some compile error.Have you tried this code?
I think Trim() removes spaces.Will it also remove "-"?
Mar 17 '12 #5

P: 51
"string" = "string"

trim("string") = 's' 't' 'r' 'i' 'n' 'g'

you can get the value of each character input..

charsToCompare = trim(txtinput.text)

the like operator in sql is used like this..

select * from my_tbl where fld_name like 'charsToCompare%'

ex:
fld_name contains
allen, abby and anna

if we use the like operator..
select fld_name from my_tbl where fld_name like 'a%'

allen, abby, anna will be the results..

select fld_name from my_tbl where fld_name like 'ab%'

will result only abby.
Mar 17 '12 #6

P: 51
im not sure if this code works fine in vba..

because i used that code in vb6..

the only difference between the two is the database connection..

vb6 need a connection to connect with database..

vba is allready in the database..

maybe make some sql statements.. to manipulate data..

the codes that i've posted is the whole logic behind the filter method of ADODC. =)
Mar 17 '12 #7

NeoPa
Expert Mod 15k+
P: 31,417
If you are using a TextBox and the user is typing data in either format (IE. with or without the extra hyphens '-') then simply lose the hyphens from the value of the TextBox before using it in the filter. I'll give some example code which I hope you will find easy to convert to your situation. If you struggle then simply ask for clarification, but remember next time you ask a question that such information should be included if you want the help to be tailored specifically to your requirements :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtJobNoFilter_AfterUpdate()
  2.     Dim strFilter As String
  3.     Dim blnFilterOn As Boolean
  4.  
  5.     With Me
  6.         strFilter = Replace(Trim(.txtJobNoFilter), "-", "")
  7.         blnFilterOn = (strFilter > "")
  8.         .Filter = IIf(blnFilterOn, _
  9.                       Replace("[JobNo] Like '%F*'", "%F", strFilter), _
  10.                       "")
  11.         .FilterOn = blnFilterOn
  12.     End With
  13. End Sub
Mar 17 '12 #8

P: 51
wow finally got it.. that one is the replace fuction.. :))

im confused how to split, join, concat, replace each character on a string..

but in your example i finnaly got the logic in replacing character on a String..Thanks! :))
Mar 17 '12 #9

P: 99
Well, NeoPa's answer seems to be working fine,the only thing was that instead of line#11 I had to use :
Expand|Select|Wrap|Line Numbers
  1. Docmd.OpenForm Me.Name, , , '"criteria"
I do not understand why doesn't it work when I try using .FilterOn..Anyways, Mmy problem is solved.Thanks rekedtechie and NeoPa.I am glad :)
Mar 19 '12 #10

NeoPa
Expert Mod 15k+
P: 31,417
That's because you are opening a new form rather than filtering the current form. Your question didn't indicate you were opening a separate form, hence the code wasn't designed to illustrate that. Please note that it is very important to include the whole question in the question. I know that sounds obvious, but nevertheless you're not doing that. You need to do that to avoid wasting yours, and everyone else's, time (and the chance that your threads will be deleted as too poor of course).
Mar 19 '12 #11

P: 99
I wasn't opening the new form untill I found nothing working when I used the .
Expand|Select|Wrap|Line Numbers
  1. Filter
and
Expand|Select|Wrap|Line Numbers
  1. .FilterOn
property.So, I tried opening a new form just now and it somehow happend to work.The first post is the complete question.
Mar 19 '12 #12

NeoPa
Expert Mod 15k+
P: 31,417
The different answers represent entirely different situations.
  1. If the form you are filtering is the same one as the form that the TextBox control is on then setting the .Filter & .FilterOn properties is the only way for this to work correctly.
  2. If they are on different forms then calling DoCmd.OpenForm() is the only correct solution.

I cannot imagine that this is something that changes between posts, so I can only imagine that you are using solution B for problem A. If that is the case then I repeat - This is not a proper solution for that problem. If you have problem A then you need to look at getting solution A to work properly rather than attempting to use solution B.
Mar 19 '12 #13

P: 99
But I am opening the same form again.Just that I am using the replace funtion so that strFilter serves as the criteria and opening the same form with this criteria.This is because no filtering happens if I'm simply using .FilterON property.If this is not the solution then I think I need to continue working on this problem.But I wonder how is everything working then.
Mar 20 '12 #14

NeoPa
Expert Mod 15k+
P: 31,417
It will work HiGu, but it is a clumsy approach and cannot be guaranteed in all circumstances (when using multiple instances of the same form for instance).

I'm not sure why you weren't able to get the other approach to work. It's relatively straightforward. Are you sure you set both the .Filter and .FilterOn properties? They work together, and both must be set to change the filtering of the current form.
Mar 20 '12 #15

P: 99
Yes I did both but no data is displayed then.Has it got anything to do with the same properties in the properties pane?Do I need to set
Expand|Select|Wrap|Line Numbers
  1. Filter On Load = Yes
?
Mar 20 '12 #16

P: 99
Isn't the above code applicabtle for a combobox?
Mar 20 '12 #17

P: 99
Can I change the recordsource of the form everytime I want filtering?That should be simple.There is one more problem,the main question to filter based on user input..If I input values in a particular format I get errors saying the text is not valid.
Mar 20 '12 #18

NeoPa
Expert Mod 15k+
P: 31,417
HiGu:
Do I need to set
Expand|Select|Wrap|Line Numbers
  1. Filter On Load = Yes
That wouldn't even be possible.

HiGu:
Isn't the above code applicabtle for a combobox?
??? No idea what you're trying to ask. 'above code' could refer to so many things.

HiGu:
Can I change the recordsource of the form everytime I want filtering?That should be simple.
It's possible, but not simple. Far more complex than adjusting the filter, and a fundamentally clumsy approach.

The idea, if you have a problem with something, is to fix that something. If you always look for ways around your problem you will build up an experience library of problems rather than one of solutions and better understanding.

It seems clear you are unable to identify what is wrong with the solution I've suggested. I strongly suspect that it's due to your failing to follow the instructions correctly (which could be for many reasons), but without reliable information from you I cannot say for sure.

I'm hesitant to suggest anything as the differences between opening the form again (which you say worked) and refiltering the current form are so minor I have no idea how one could work and the other not if all you report is true. It doesn't make sense.
Mar 20 '12 #19

NeoPa
Expert Mod 15k+
P: 31,417
HiGu:
There is one more problem,the main question to filter based on user input..If I input values in a particular format I get errors saying the text is not valid.
Guessing games. Oh good. I like them. Now I wonder what format you might be talking about...

Actually, if you want some help with this it might be a better idea to explain what you're talking about ;-)
Mar 20 '12 #20

P: 99
1. There is no input mask that I am using.I simply want to allow the user to enter something like E-2y-4999 which means the user should be able to enter '-' also in the combobox or any text.However, if I try to enter any such value and press 'enter' I get a messagebox saying that "the text you entered is not in the list"
2. I just happened to add this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdApplyFilterSort
  2.  
to NeoPa's Post#8 and I was prompted to give a paramete to the dialog box.I gave the parameter and guess what!It filtered!This was soothing.However no one would like to be prompted to add a parameter.But I am happy atleast it worked somehow but only not for all the values in the combobox.The only problem will be filtering when the value is null.
Mar 21 '12 #21

P: 99
What I tried just now is setting the LimitToList property to NO and now I see the combobox doesn't give any error on user input infact things are working fine but the only problem is that there will not be any intellisense now.
Mar 21 '12 #22

NeoPa
Expert Mod 15k+
P: 31,417
@HiGu

You keep moving the question. These new issues are separate issues and should be dealt with separately. I cannot keep commenting on every choice you make which is nothing to do with what I've suggested. Your choices are largely counter-intuited and I have no idea where they come from.

Questions on Bytes are supposed to be single questions and not a running project support. If you haven't got this to work yet then you are welcome to continue with it here, but only if you go back to the question and remove all the other things you've tried that I haven't suggested. If you want to try them again later after this thread is finished with then be my guest, but while I'm spending time with you on this you must not make changes that don't reflect what is discussed in the thread.

If you think about it I'm sure it will be obvious to you, too, that that is not a very clever way to proceed.
Mar 23 '12 #23

Post your reply

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