Using An Access Form as a Search tool 7

[Searching]

I know about the Filter function in Access, but is it possible to use an Access form just as a Search tool. I'd like to create a Search Form that will allow a user to enter a word in any one of the fields and to find all records from a particular table that match that field.

BTY I posted this in the Microsoft:Office tread, I think, inappropriately. Is there a way to edit one of our posts or move the post from one thread topic to another? I couldn't find any help on this.

 

[LonnieJohnson]

You can build a query with all the fields that would have the string in it. In each fields criteria put the following:

Like "*" & [Forms]![FormName]![TextBox] & "*"

Of course you would change FormName and TextBox to your form's name and the name of the textbox (search field) on that form.

Hope this helps... ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[mickeyg]

Here is another way to do it...
I have created keywords type search screens and built my SQL string using OR logic. Then I set the recordsource of my form (in datasheet view) to my sql string.

Mickey

 

[LonnieJohnson]

Mickey brings up a point with the OR operator. Doing it my way would treat the SQL behind the form as though it was using an AND operator. If you were to build you query with a query grid instead of SQL statements you would have move this expression down on line for each field. Thus Field1 the expression would be on line 1 of the criteria grid, Field2 on line2 of the query grid...

Either way is fine and actually the same. It's just a matter of using SQL or the query grid. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[Jackie]

This is a great approach! How does it work for Multi Select Listboxes?

I would like to implement it for a listbox with Multi Select property set to "Extended". A multi Select property set to "Extended" allows the user to select more than one option from the list box.

What is the syntax that would appear in the query grid for a multi select listbox?

Thank you in advance.

 

[mrfilez]

If I am understanding the SQL OR approach correctly, it allows you to select more than one criteria to find a record. If this is the case, I would like to be able to do this. Unfortunately, I do not know SQL. I have been unable to dedicate time to learning it so far. I am having to learn all of this on my own. Well, actually that is not true. I am learning it with the help of the wonderful people here. I have seen several ways to do a "search" type form posted, and have tried them. I think that I just do not know enough about what I am doing to get them to work. This sounds like something that, if given a SQL statement example, I could do. I am really starting to get very frustrated with my inability to get this to work. Lonnie, you gave me a suggestion on this, but I was unable to get it to work. I tried for several days, but was unsuccessful. Right now I am not sure what it was that you had suggested.
This looks like something that I could possibly do, as it appears that you just repeat code. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[LonnieJohnson]

Here is the basic approach to doing a name search on a table that has a first name and last name field. You have a field on a form that the user types information for a search. Either when the user clicks a button or hits enter, the following code is fired.

Me.RecordSource = "SELECT * FROM YourTable " _
& "WHERE FIRSTNAME LIKE '*" & SEARCHTEXTBOXNAME & "*'" _
& "OR LASTNAME = '*" & SEARCHTEXTBOXNAME & "*'"

Let me know if this is simpler.

B-) ljprodev@yahoo.com
ProDev
MS Access Applications

 

[mrfilez]

Ok. Let me make sure I am understanding this correctly.

Me.RecordSource = "Select * FROM YourTable" _
Me.RecordSource is setting the record source to whatever table or query I wish to use, Correct?
&"Where Firstname LIKE '*" & Searchtextboxname & "*'" _
This is the criteria that I am using to search with, Corret?

Sorry to seem so stupid, I just want to make sure that I am getting the reasoning behind the code. Otherwise, it is useless for me to even bother with this. I am really wanting to learn all of this stuff. You have been a huge help. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[LonnieJohnson]

You are correct in both cases. This is assuming that your form has a record source and records loaded. Let's say 100 records. Now you want to be able to give you user the ability to look at only the Smiths. What this will do is run a query on the same record source (YourTable that has 100 records) and return only the ones that have the characters SMITH in the first or last name. It will also return SMITHSON or BLACKSMITH because we have have the wild card character (*) in our expression. ljprodev@yahoo.com
ProDev
MS Access Applications

 

[mrfilez]

Ok, I am in the process of trying this out. I will let you know how it comes out. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[mrfilez]

Ok. I feel really stupid. I have not even gotten started and have hit a brick wall.

I have roaylly confused myself. Am I supposed to create a SQL Query with this code, or build a form and put this code in somewhere? I assume that I would build a form, because of the Me.RecordSource, and then put the code into the AfterUpdate event of a field.

If this is the case, I am not sure how to represent all of the fields in the Records to be displayed.

I am sorry that I am so difficult to work with. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[LonnieJohnson]

Let's keep it simple until you see it work.

1. In the Forms tab of database window, click the new button.
2. Click AutoForm: Tabular.
3. Go to the DropBox below and choose the table you want.
4. Click OK. (Access will now build you a form)
5. Go to the design view of this form.
6. Add a textbox and a command button to the Footer of the form. (Call the textbox txtFind and the command button cmdFind_
7. Now when we paste our code in the command button's click event and it should look like...

Private Sub txtFind_Click()
Me.RecordSource = "SELECT * FROM YourTable " _
& "WHERE NAMEFIELD LIKE '*" & txtFind & "*'"
End Sub

Now the only thing you have to change is YourTable to the name of your table and NAMEFIELD the name of a field that has names in it.

Lemmeno...

B-) ljprodev@yahoo.com
ProDev
MS Access Applications

 

[mrfilez]

Ok, I got the form made the way you said. I set everything up. When I type in the name and hit the button, it gives me one blank row. I tried several records. I really hate to take up anymore of your time, so do not worry about it. I will get something figured out. I should be able to buy another Access book in about 2 months. I was going to buy one this week, but the motherboard on my computer went out yesterday. Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[mrfilez]

I figured out where my problem is. I have a several tables. I have a table that my main form is based on that is made up of lookup fields to my other tables. This is the table that I am using to do my searches on, since it is the only table that contains the "full" records. Each field has an ID and the normal field. If I search for the ID# instead of the name then it displays the record(s). Any idea on how to fix it to where I only have to type in the name? Having to search by the ID is not really searching.
Thank you for your help and time. It is greatly appreciated!!!

Thanks,
Don
please CC both addresses
f613493c@mailfxhome1.fedex.com
mrfilez@midsouth.rr.com

 

[ssehgal]

Hi I am designing a website right now and am very new with microsoft access. I want to design a page that searhes information on my site using a data base. The problem is I dont know how to start using access and linking the database with the search. Does any body know? Thank you

S.Sehgal

 

[ImpMan]

There are many ways to do this, here is another (though you may have it sorted now).

I often have a form that views all the records in an query. The query shows all the results of the search.

To change the search I type in a text string in the text box (txtSearch). I then trigger an event from a button or AfterUpdate event that builds a string as described above called strSQL. The string is the SQL that will end up in the query.

Then use the following code.

Set qdf = CurrentDb.QueryDefs("qrySearch&quot
qdf.SQL = strSQL
qdf.Close

me.requery
me.refresh

This will then update the query and refresh the form view of the underlying data. (if that doesn't work use
Me.RecordSource = "select * from qrySearch"

This has the advantage of saving the last search.

PS.
If anyone has trouble writing SQL then create a typical query using the normal design view then change views. From the SQL view you can easily cut and paste strings of SQL. This can be used within forms that create SQL to modify what the user sees.

 

[VH]

LonnieJohnson:
Just a correction to the code:
Private Sub txtFind_Click()
Me.RecordSource = "SELECT * FROM YourTable " _
& "WHERE NAMEFIELD LIKE '*" & txtFind & "*'"
End Sub
It should be read:
Private Sub cmdFind_Click()
Me.RecordSource = "SELECT * FROM YourTable " _
& "WHERE NAMEFIELD LIKE '*" & txtFind & "*'"
End Sub