Product Search through Form?

[interpower]

Hi

I've started a new database of suppliers, I have a table and a form. I know I can go into query and do a search on the products column of the table but is it possible to create a search box on the form which, when you put a product into the box, will go through all the suppliers who sell that particular product, showing them at the click of a button (or mouse), in turn?

I have plenty of experience at basic level with Access but am I expecting too much of '97 to be able to do this, or is it possible?

 

[JANR]

Yes you can do that, one way is this:

Formname = frmSearch
Textbox on form = txtsearch
Query = Qsupplier

Open the query in designview and add this to the criteria for "products"

= Forms!frmSearch!txtsearch

Then on your form add a button and in the click event open the query,

Docmd.OpenQuery "Qsupplier"

JR

 

[Thinh]

if all the data is avialable meaning the search field is been shown or in the query all you have to do is use the filter function.
give u exact records that contains the value in textbox.
1. me.filter = "[product]=" & me.textbox
me.filterOn = true
give u anything that start with what in the textbox:
2. me.filter = "[product] like " & me.textbox & "*'"
me.filterOn = true

second approach is more appropriate for search function

 

[interpower]

Thank you for your replies. I now have a button on my form which, when a search is completed, lists the companies that supply the product requested. Amazing.

My preference would be for me to be able to click through the selected companies, one at a time, on the form, without having the companies displayed in database format. Is this possible.

 

[interpower]

One thing I have noticed about the way JANR's approach is working is that it only picks up the suppliers where I only have one item in the column to be searched. If there is more than one word in the column it won't pick it up. How do I get around this, I'm sure it's very basic. I think I need a separator or something.

Thanks

 

[JANR]

The way I supplied uses exact match, you can use LIKE operator insted.

LIKE "*" & forms!frmSearch!txtSearch & "*"

JR

 

[interpower]

Excellent JR. That's solved the problem. Many thanks.