searching from table in a form

[honnour]

Hello,

I have a table that contains mobile phones.
In table there are two fields. One is ID and the other is mobile phone.
Mobile phones are like 555-555 55 55 in my table.
What I want to do is, user will have a text box. and he/she will enter the number like 5555555555 ( not like the format in my table) and will click the "search" button. Then If there is a phone like that in my table the output will be the ID of that phone number, otherwise it will give error like " this phone doesnt exist"

How can I do this?

Thank you

 

[rodmc]

the search function (on this site) is your friend

 

[honnour]

I'am a noob user : ( I couldnt implement it

 

[rodmc]

can you post your db?

 

[honnour]

I cant because its crypted but I can tell my table name.
its ttelecom. I'm searching from ttelecom table.
Table has ID which is primary key and mobile number ( 555-555 55 55)

 

[rodmc]

ok, I guess you have a query as the basis for your subform?

 

[rodmc]

Private Sub btnClear_Click()

    Me.txtPhoneNo = ""
    
End Sub

Private Sub btnSearch_Click()
    Me.[yousubformname].recordsource = "SELECT * FROM [yoursearchqueryname] " & BuildFilter
    Me.[yousubformname].Requery
    
End Sub

Private Sub Form_Load()
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    
    varWhere = Null
    
    If Me.txtPhoneNo > "" Then
        varWhere = varWhere & "[PhoneNo] = """ & Me.txtPhoneNo & """ and "
    End If

If IsNull(varWhere) Then
    varWhere = ""
    Else
        varWhere = "WHERE" & varWhere
        If Right(varWhere, 5) = " and " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
End If

End Function

call your search button btnSearch and your clear button btnClear, you will need to change some of the names of your controls to suit but that should work

 

[honnour]

tkank you, but I dont have a query for research.
I just have a table and a form with a text box and a button

 

[rodmc]

create a query with the fields you want displayed and use this as the recordsource for the subform (search results), this is the control in the code that reads [yousubformname]

 

[bob fitz]

You could just use the following line of code as Control Source property of the second text box:

=IIf(Format([[COLOR=red][B]Text0[/B][/COLOR]],"000-000 00 00")=Nz(DLookUp("[COLOR=blue][B]phone[/B][/COLOR]","ttelecom","[B][COLOR=#0000ff]phone[/COLOR][/B]= '" & Format([[B][COLOR=#ff0000]Text0[/COLOR][/B]],"000-000 00 00") & "'"),0),DLookUp("[B][COLOR=#0000ff]phone[/COLOR][/B]","ttelecom","[B][COLOR=#0000ff]phone[/COLOR][/B]= '" & Format([[B][COLOR=#ff0000]Text0[/COLOR][/B]],"000-000 00 00") & "'"),"this phone doesnt exist")

You would need to change the Red and Blue text
Text0 Replace this with the name of the text box that the user types the number (5555555555)
phone Replace this with the name of the field in the table called "ttelecom" that holds the telephone number (555-555 55 55)