Searching

  • Thread starter Thread starter Harmannus
  • Start date Start date
H

Harmannus

Hallo,

How do i make a search screen based on a text box in which a user can type
any information and on clicking search: if there is 1 match the record is
shown, more then one mach a continous form is shown.

So i have a table customers with name, address, city, telephone etc. The
search has to span all these fields

I tried a lot but did not get very far.

Hope somebody can show my some examples.

Regards,

Harmannus
 
Do you need to use a single text box to search on several fields, or could
you use separate text boxes for each field? The latter would be more
normal, but it's not what you seem to be asking for.

If you need to use a single text box then the first thing to do would be to
create a query containing a calculated field that concatenates all the other
fields together, something like:

SELECT NameOfTable.*, (name & " " & address & " " & city & " " & telephone)
AS SearchField
FROM NameOfTable

(BTW If you really do have a field called "name" then it's probably a good
idea to call it something else).

You could then use some VB code in the AfterUpdate event of your text box to
modify the RowSource of the subform:

'Code start
dim strSQL as String

strSQL = "SELECT NameOfQuery.* FROM NameOfQuery " & _
"WHERE SearchField Like '*" & Me.NameOfTextBox & "*'"

Me.NameOfSubform.Form.RowSource = strSQL
'Code end

Personally, I think I'd avoid a single search box and go for separate boxes
for each field. The code would be similar, but more complicated, but I think
the searching would be simpler and the searches would execute faster.
 
Thanx for the response & the code!

I will look into it and consider your suggestion!


Regards,

Harmannus
 
Back
Top