What is your preferred search method?

[vangogh228]

As there are several ways to search a database for a specific record, what is your preferred or favorite method??? Do you simply have your database end-users perform a Find function, or do you have them Filter for the record, or do you normally use some other method on a regular basis??? Do you facilitate this process with command buttons that initiate the searching???

I am trying to normalize how I teach this function and your input is appreciated.

 

[RichMorrison]

Typically I use a form with a query as RecordSource. The query has one or more criteria based on search fields from the form. The form has a "Find" command button.

RichM

 

[The_Doc_Man]

Actually, different methods are right for different situations.

The Find icon in the toolbar is easiest for an ad-hoc search that might not be frequently used. An actual search form might be preferred for something that is continually searched.

For example, I have a database describing a server farm of about 310 servers. (Government site.) We frequently search for servers by name, site (location), IP address, and MAC address. We sometimes search by the administrator's name, project name, or specific task name. We rarely search by Make or Memory size.

We have pre-set ways to search for the "frequent" and the "sometimes" cases, but the other searchs we just do using the Find icon on the toolbar.

So if I were teaching the course, I would discuss multiple methods and touch the concept of how often you are likely to use them. No single method is "right" or "wrong" as long as it works for you and you understand it. However, some methods might be more or less efficient.

I would also mention the importance of placing indexes on fields that are frequently searched.

 

[ColinEssex]

I frequently use the search method where you create a 'Search' button on the form.

The user id prompted to enter the first few characters of the surname or whatever.

Then the code behind the button bookmarks any matches and displays a MsgBox "Fred Bloggs - is this correct?" Y or N. If yes then it displays the whole record on the form, if not it goes on to the next match to the end.

If there are no more matches, then the MsgBox says "No more matches for XYZ".

Using the "Find" method with the little binoculars means that the user has to put the curser in the specific field to search on. Its ok and I'm not knocking it but this can be a problem for some users!!! They put it in a number field and wonder why it can't find a name or whatever.

Hope this helps

Col

 

[vangogh228]

Yes it does. Thank you. I would like to hear from some more folks, though.

 

[Shep]

Many of my search functions consist of a textbox in which the user types a keyword, a command button to initiate the search and a listbox whose rowsource is generated behind the command button. The rowsource may be a left and right hand wildcard search, right hand only or may be an exact match, depending upon the type of data being searched. The listbox may also be based on a saved parameter query. The textbox/command button combo will supply the parameter, in this case.

Another very visual technique, useful when many fields and records are involved, is to create a large listbox. Add an option group of command buttons equal to the number of fields you wish to display and arrange them in a likely spot, such as in a row above or below the listbox. Use the after_update event of the option group to generate SQL, which becomes the rowsource for the listbox. In effect, clicking one of the buttons sorts the listbox on that field. I add an ascending/descending option group on occasion to ensure that I will be writing this procedure for far longer than I wished. To add to my misery, I'll sometimes add another textbox which is used to further filter the results shown in the listbox, such as when the user spots a particular field they'd like the listbox to show exclusively. An example that comes to mind is a part number. If the listbox contains several part numbers, the user can single click one line, which places the part number in the filter textbox. A nearby command button generates a new rowsource for the listbox which filters the previous rowsource, causing it to show only records containing that part number.

Shep

 

[moocowman]

Search Form

This is the way I did it:

My example is a search form from which users can search by multiple fields. To simplify, I'll use the fields FirstName and LastName. Each search expression is joined by the AND function. For example, if the user enters John in FirstName and Doe in LastName, clicking the cmdFind button will search for all records with first name John AND last name Doe.

The code also uses the * wildcard at the end of each expression so the user can enter just the first portion of a name. For instance, to find John Doe, the user can use Jo in FirstName and D in LastName.

The resulting combination of the search expressions looks like this: [First] Like 'Jo*' AND [Last] Like 'D*'
This search string is then used in the DoCmd.OpenForm 's Where argument.

I created a form with two unbound fields, FirstName and LastName. I then created a button to perform the find function. The following is the code I used for that button's On Click event(comments have been added to assist in understanding what I have done, remove these from the code):

Dim stLinkCriteria As String

If Not IsNull(FirstName) Then
If stLinkCriteria = "" Then
stLinkCriteria = "[First] Like " & Chr$(39) & Me!FirstName
Else
stLinkCriteria = stLinkCriteria & " AND [First] Like " & Chr$(39) & Me!FirstName
End If
If Right$(Me!FirstName, 1) = "*" Then
stLinkCriteria = stLinkCriteria & Chr$(39)
Else
stLinkCriteria = stLinkCriteria & "*" & Chr$(39)
End If
End If

If Not IsNull(LastName) Then
If stLinkCriteria = "" Then
stLinkCriteria = "[Last] Like " & Chr$(39) & Me!LastName
Else
stLinkCriteria = stLinkCriteria & " AND [Last] Like " & Chr$(39) & Me!LastName
End If
If Right$(Me!LastName, 1) = "*" Then
stLinkCriteria = stLinkCriteria & Chr$(39)
Else
stLinkCriteria = stLinkCriteria & "*" & Chr$(39)
End If
End If

DoCmd.OpenForm "frmMain", , , stLinkCriteria

As you can see, there are 2 blocks of code that are almost identical. You can repeat this as many times as you want for as many search criteria fields you want to use. Just replace the names of the fields. Feel free to e-mail me if you have any questions on how this works.

junk@numberfive.net

 

[eforce]

Search by Form

I am having difficulty with how to have a Search done by Form that gives the same results as a "Filter by Form" I currently have users simply use a "Filter by Form" and then "Apply Filter" which works just lovely. The only problem is that as you probably know; This feature doesn't work in the Runtime versions you create to distribute a program out to users who donot have MS Access.

I would like to use the same form users use to input data as the search form. I have text fields and check boxes on this form.

Why hasn't Microsoft fix this issue so that "Filter by Form" works in runtime versions of Access?

Thanks,
E-Force
"Delta's last resort"