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

[HiGu]

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?

[NeoPa]

Please clarify whether you are using a TextBox or a ComboBox as the answers to each are very different. Otherwise, the question is clear.

[HiGu]

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.

[rekedtechie]

'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..

Dim StrToSearch As String
StrToSearch = Trim(txtboxSearch.Text)

if StrToSearch <> "" Then
my_tbl.Filter = "fld_name Like '" & StrToSearch & "%'"

Else
my_tbl.Filter = "fld_name <> '123'"
End If

'i hope it helps.. =)

[HiGu]

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 "-"?

[rekedtechie]

"string" = "string"

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

you can get the value of each character input..

charsToCompare = trim(txtinput.t ext)

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.

[rekedtechie]

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. =)

[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 :

Private Sub txtJobNoFilter_AfterUpdate()
    Dim strFilter As String
    Dim blnFilterOn As Boolean

    With Me
        strFilter = Replace(Trim(.txtJobNoFilter), "-", "")
        blnFilterOn = (strFilter > "")
        .Filter = IIf(blnFilterOn, _
                      Replace("[JobNo] Like '%F*'", "%F", strFilter), _
                      "")
        .FilterOn = blnFilterOn
    End With
End Sub

[rekedtechie]

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! :))

[HiGu]

Well, NeoPa's answer seems to be working fine,the only thing was that instead of line#11 I had to use :

Docmd.OpenForm Me.Name, , , '"criteria"

I do not understand why doesn't it work when I try using .FilterOn..Anyw ays, Mmy problem is solved.Thanks rekedtechie and NeoPa.I am glad :)

[NeoPa]

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).

[HiGu]

I wasn't opening the new form untill I found nothing working when I used the .

Filter

and

.FilterOn

property.So, I tried opening a new form just now and it somehow happend to work.The first post is the complete question.

[NeoPa]

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.

[HiGu]

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.

[NeoPa]

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.