Searching Database

Hi,

I'm not sure if it's possible to have a search form where you can type anything into a textbox and then search the WHOLE database for that one or PART of a word?... (much like a search engine of yahoo and google)

I have a form that is able me to search only exact words of it. (using dropdown lists).. If I type it.. it gives me nothing.

Thanks

Not sure what you meant by WHOLE database. But as far as searching a table and a particular field for records that match what you type in, the syntax will use the Like operator, and the wild card "*". Example searching the LastName field in a query in which you want to type just a few letters to find all matching records, put this in for the criteria of LastName:

Like "*" & [Enter Text] & "*"

When you run the query, the parameter box will open...typing in the letters "th" (without quotes) would return records with Smith and Thomas as an example. You can change the parameter to using a textbox on your form. Typically using a combobox won't allow entries that are not on the list already, so just use an unbound textbox for this purpose.

lwells

um.. yea that's part of what I want...

By the WHOLE database.. I mean typing in "th" in a "Keyword" field and then the query will return every single thing that has "th" in it... even if there's "th" in a paragraph.. (For example.. I have 2 tables: Table1 and Table2... If there is "The animal is smelly" in Table1.. and "What is going on over there?" in table 2.. the query will return values of "The" from Table1 and "there" from table2)

how do I set up the query to do this?

Thanks

To expand on the above msg..

I tried it and it works fine.. but.. I find that I cannot put spaces and find two words together.. (for example... I want to find all records have "Thomas" AND "Smith" AND "Boy".. typing "Thomas Smith Boy"... which will return " Boy Thomas Smith" and will NOT return only "Smith" or "Thomas" or "Boy"..)

How do I do this?

Thanks so much@!

Well, I don't think I am following the logic of what you want the user to be able to do. If you are wanting to find the name of an employee for example, why would you perform a search in the table used for products? Just like with google, typing the letters "th" in the search engine isn't going to be productive unless you define the search a little better. Perhaps you can explain what this search form is suppose to do and what are the results you are trying to obtain.

lwells

Hi,

I'm sorry for the confusion.

What I have is a form that perform searches. I want a textbox where the user can type in keywords (more than one word) and it will search the entire database (all the fields in the database).

For example:
Table1: Sodium Chloride, Sodium Carbonate, Sodium Flouride, Sodium Bromide
Table2: Sodium Chlorate, Sodium Dihydrate

I type in "Sodium" then the result the query should return is
Sodium Chloride
Sodium Carbonate
Sodium Flouride
Sodium Bromide
Sodium Chlorate
Sodium Dihydrate

And if I type in "Sodium Chlo", the query should return
Sodium Chloride
Sodium Chlorate

That is what I want the search form to do

Thanks

Okay, so far I understand, except the part of how you want the results to display. If you have two tables then you will have two queries to run...one for each table (or did I miss something here?). The statement: "query should return..." implied just one query.

But the issue will run a little more complicated than just that, if in your above example "Sodium" will appear in more than one field on each of the tables and you want to search those fields as well ,then you probably will want to work with using some vba code to loop through the records and fields, rather than trying to use just a query by itself. More interesting though would be to understand your table structure and the need to store the same data in multiple tables and fields, thus requiring this type of search to begin with. Typically data that will be indexed to search by will be in one field only.

lwells

I don't quite understand what you mean "store the same data in multiple tables and fields". I don't have any experience in VBA coding what-so-ever, so I think that is a major problem.

One thing I found is that I can use one query to search all the fields in the database with your "Like "*" & [field name] & "*"" code: I set up the query, then put all the fields in the query to search.. then I put the code you gave me into the criteria in separate rows for each of the field names. This gives me good result... BUT the problem is I don't know where the keyword appears in the record..

Example: if I type in "chem" in the keywords field (in the search form) it returns 3 records. And within each record, the "chem" word is somewhere in a field/sentence in a field.

So is there a way to indicate where the word is located? (by showing it in different color or bolding it or highlighting it or (The best) showing the data that is in the field where it found the word?
(What I want to display to the user is a report or form that shows where the user can find the word he/she typed in.)

Thanks

No problem, terminology can always be a little confusing.

If you are making a search using the word "chem" and it is returning three records, then "chem" is in one or more of the field(s) where you placed this criteria. Unfortunately access doesn't have a way of highlighting or making just that one word bold in a field to make it easy to find after running your query.

But as an option, using the Ctl+F or the Find from the Edit menu (or the Binoculars icon) you can paste the "chem" (without quotes) into the Find textbox and use your wild cards *. That will search through all the controls (fields) and will highlight or stop at the first word that contains "chem" in it. It won't highlight all them at once, but it will highlight the first word or phrase if finds.

lwells

Just thought I would add...the Find would be used if you are displaying your record in a Single form view (one record at a time) or when viewing your query.

Oh... that's unfortunate..

Is there a way I can use the Find operation in a macro or VBA?

What I want to do is the user types in the keyword and searches.. then another form (keyword result form) opens and displays all the record numbers that matches the keyword... then there's a button ("Go") beside the record number and the user can click on it and it opens another form [Specific Record Form] that displays the record that the user clicked on [up to now it's working well]....

my question is.. is there a way when the user clicks on "Go".. it runs a macro that runs the find operation and automatically puts the keyword in the Find textbox and then goes to the first word in the record that matches it?

Thanks

Hmmm....not off the top of my head. But I am kicking an idea around in my head...but have a question in your application flow.

Your search form initially is basically just a form with a text box and a button probably to run the query and open the next form(keyword results). Now this form displays all the record numbers..how is this displayed? You mentioned a "Go" button along side the record number, so I guess you are displaying the record numbers in a list box am I correct. And after the user selected which record number to view another form opens Specific Record Form and this is the form where you would like to highlight the field that has whatever was entered into your search form.

The reason I am asking, is I might be able to incorporate some code in the open event of this form that will look at all the controls on this form and find the matching word in your search form and make the control stand out in Bold type or Change Text Color or something to draw the attention to the user...but only if the search form is still open.

lwells

If you want to try your hand at a little VBA code try something like this.

First, open a new module. To do this in your database window select Module from the left side pane. Click New and this will take you to a blank module, not associated with any form. Paste the following code directly under the Option Compare Database at the top:

Option Explicit
Function IsLoaded(ByVal strFormName As String) As Integer
Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function

You will use this IsLoaded function later in your code. Now click the save button and give the module a name or use the default name that access gives you.

Next open the form [Specific Record Form] in design view. Go to the form properties and select On Open. When you place your curser in the text box next to it, there will be a drop down arrow appear on the right side of the text box. Next to it will be the elipse button (the one with three "..." dots in it). Click on the elipse button and select Code Builder. This will open the forms module to the On Open event. Paste the following code in the area between Private Sub Form_Open(Cancel As Integer) and End Sub:

Dim ctl As Control
Dim strTemp As String, strFirstFind As String
Dim i As Integer
If Not IsLoaded("frmSearchForm") Then Exit Sub

strFirstFind = Forms![frmSearchForm]![txtSearch].Text

With Forms(Me.Form.Name)
On Error Resume Next
strTemp = ctl.ControlSource
If Err.Number <> 0 Then
For i = 0 To .Controls.Count - 1
Set ctl = .Controls(i)
If ctl.Enabled = True Then
Err.Clear
strTemp = ctl.ControlSource
If Err.Number = 0 Then
Exit For
End If
End If
Next i
End If
ctl.SetFocus
On Error GoTo 0
End With

DoCmd.FindRecord strFirstFind, acAnywhere, False, acSearchAll, False, acAll, True


Be sure to use the name of your search form in place of the frmSearchForm above.
Change the txtSearch to the name of your text box that you type your search words in.

Then on the tool bar select Debug / Compile to check for errors in the code. Close and save the form.

Now when you enter your search word or words and when the user selects the specific record form [Specific Record Form] to open, the code will search to find what was typed in your search form on this record and highlight the word no matter what field it is in.

See if this will get what you are wanting.

lwells

Hi,

First: Actually I just have them as textboxes in the "Details" section of the form.. so it goes on and on and on...

Second: This is exactly what I wanted... but it doens't seem to highlight the word.. I tried it and there's no syntax errors in the code.. but the words in my [specific record form] does not highlight (unless you set it to bold .. I couldn't see it because all my words are bolded already).. and I don't know where to change the effect to capture the user's attention.. Can you please point it out to me?

This is my code:

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
Dim strTemp As String, strFirstFind As String
Dim i As Integer
If Not IsLoaded("database search tool") Then Exit Sub
strFirstFind = Forms![database search tool]![Keyword].Text
With Forms(Me.Form.Name)
On Error Resume Next
strTemp = ctl.ControlSource
If Err.Number <> 0 Then
For i = 0 To .Controls.Count - 1
Set ctl = .Controls(i)
If ctl.Enabled = True Then
Err.Clear
strTemp = ctl.ControlSource
If Err.Number = 0 Then
Exit For
End If
End If
Next i
End If
ctl.SetFocus
On Error GoTo 0
End With
DoCmd.FindRecord strFirstFind, acAnywhere, False, acSearchAll, False, acAll, True
End Sub


Thanks a bunch!!

When you are typing in your textbox [Keyword], are you adding any characters or quotes? You should be able to just type the word with or without caps but no special characters or wild cards or quotes and it should find the word in your text boxes. If not, let me know and I will work on it some more. I left my beta .mdb at home but will get it working for you one way or another.

lwells

I don't put any quotes or special characters when I do the search. I just type in the word.

Just wondering.. what does the code do to the word itself? (bold, italics, highlight?)

Thanks a lot..you're the best!!

Is it possible to copy the tables involved, the query and the three forms into another database with some sample records to let me look at it. You can send it to my email address lwells1433@aol.com and I will see what is going on and make it work properly, then you can just import the forms back into your original .mdb. I made a quick .mdb here and didn't find what caused your problem either. It should highlight the first matching word to your search word, and it wouldn't matter if the text was bold or not.

lwells