search more than 1 fields

[spacerocket]

I'm doing a contacts db, and have a search form that contains a combobox for
user to select the field (cboSearchField) to search and a text box for
entering the search criteria (txtSearchString).

How do I search for more than one fields? I would like to be able to search
for the fields PhoneNo1, FaxNo & MobileNo after entering a phone no. in the
search string. Based on the code which I got from a tutorial, I manage to
search successfully, but only within 1 field.

strWhere = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
Form_F_Contacts.RecordSource = "select * from Contacts where " & strWhere
Form_F_Contacts.Caption = "Contacts (" & cboSearchField.Value & " contains
'*" & txtSearchString & "*')"
DoCmd.Close acForm, "F_Search"
MsgBox "Search completed."

 

[Gijs Beukenoot]

spacerocket heeft ons zojuist aangekondigd :

I'm doing a contacts db, and have a search form that contains a combobox for
user to select the field (cboSearchField) to search and a text box for
entering the search criteria (txtSearchString).

How do I search for more than one fields? I would like to be able to search
for the fields PhoneNo1, FaxNo & MobileNo after entering a phone no. in the
search string. Based on the code which I got from a tutorial, I manage to
search successfully, but only within 1 field.

strWhere = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
Form_F_Contacts.RecordSource = "select * from Contacts where " & strWhere
Form_F_Contacts.Caption = "Contacts (" & cboSearchField.Value & " contains
'*" & txtSearchString & "*')"
DoCmd.Close acForm, "F_Search"
MsgBox "Search completed."

I am not sure what your goal is. Do you want the user to 'build' a
search string or do you just want to search these three fields after
entering this number?

If the latter is the case, you could modify your query to :

strWhere = "PhoneNo1 LIKE '*" & txtSearchString & "*'"
strWhere = strWhere & " OR FaxNo LIKE '*" & txtSearchString & "*'"
strWhere = strWhere & " OR MobileNo LIKE '*" & txtSearchString & "*'"

 

[spacerocket]

Thanks. Your answer is exactly what I needed

I have another question. How do I highlight the field where the search value
is found? I've checked the help file and it says I can use SetFocus. However,
I'm not sure how to set it since the search contains many fields. Thanks
again.

 

[Gijs Beukenoot]

spacerocket formuleerde de vraag :

Thanks. Your answer is exactly what I needed

I have another question. How do I highlight the field where the search value
is found? I've checked the help file and it says I can use SetFocus. However,
I'm not sure how to set it since the search contains many fields. Thanks
again.

You'll have to search the resultset (me.recordset or
form_f_contacts.recordset) and check all 3 fields. The one that has the
correct data could be highlited with the setfocus, selstart and
sellength functions. That way, the matching field can be highlighted.
Like Form_f.contacts.Faxno.Setfocus