From search using wildcards

[alexpeterson]

I'm trying to create an access database which holds a list of contacts. I want the data to be displayed in a simple form (which I have created) and have a search facility, whereby I enter the company's name in a text box, click the button and the result is displayed. to do this, I've butchered the "text search example" from

http://www.microsoft-accesssolutions.co.uk/text_search.htm.

It's not pretty and I can only (barely) understand the methodology, but I need an extra feature which I cannot work out how to add. Essentially, the system works if I enter the entire company name. I need to be able to enter the first bit (or ideally any part) of the name and have the system return the matching record. I can sort of make it work, by entering an asterix (wildcard) in the text/search box. This will return the desired record, but it will also show the error message box stating that it couldn't find it, which is not really true.

I've pasted the code that is executed when the button is pressed. Any help would be greatly appreciated.

=============================================================
Private Sub cmdSearch_Click()
Dim strIDRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strCompanyName")
DoCmd.FindRecord Me!txtSearch

strCompanyName.SetFocus
strIDRef = strCompanyName.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID
'and clears search control

If strIDRef = strSearch Then
strCompanyName.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", , "Invalid Search"
txtSearch.SetFocus
End If
End Sub
=========================================================

Thanks,

Alex.

 

[swtrader]

Are you sure you can't use the powerful features of queries and drop down boxes to accomplish essentially the same thing?

If someone is searching for ABC Widget Works, Inc. and they type in "AB", they know the company they are looking for. If it's not in the contact list, then it's not in the database.

What if it's not in the database? Do you want them to be able to add it?

swtrader

 

[alexpeterson]

Thanks swtrader,

I thought about drop down lists, etc, but I would rather use a text box. Do you know of any other websites with code ont them, that I could use instead of this one as it quite hard to edit.
Thanks
A

 

[ProgramError]

Hi Alex,

If you use a query and use the following in the field of the searchable criteria.

like "*" & [nameofthetheinputtextbox] & "*"

This should give you the results you are looking for. Alternatively you may want to try the dlookup method which will find the first occurance of the text you are searching for.


Ian Mayor (UK)
Program Error
Programming is 10% coding and 90% error checking.

 

[KevinBrennan]

This is an execllent example, and I have a question about extending it. What if you want to use want to use wildcards or regular expressions which would return more than one record, then allow the user to choose their desired match?