SQL in BUttons

[jaycie]

strSQL = "SELECT *From Product"
strWhere = " WHERE (((Products.TagNumber) Like Me!TagSearch)) AND ((Products.CategoryID)=Me!CategorySearch));"
strSQL = strSQL & strWhere

Me!SearchResult.RowSourceType = "Table/Query"
Me!SearchResult.RowSource = strSQL
Me!SearchResult.Requery

I am trying to incorporate a search button that displays the result in the display box <SearchResult>. I can make the form work just fine using a query form and incorporating that into my form. However, I want to learn more than that to create more advance features in my search form. I am trying to use the code above in the OnClick event of my search button. But, the results are not coming up. Is my code wrong? (Up there I am searching by TagNumber and CategoryID)

Please help!
Thanks!

 

[[nateobot]]

2 things i jumped out at me.

1. There is no space between * and From
2. You are selecting from table Product yet your criteria says tabel Products

Edit: third problem

3. strWhere = " WHERE (((Products.TagNumber) Like Me!TagSearch)) AND ((Products.CategoryID)=Me!CategorySearch));"

should look more like

strWhere = " WHERE (((Products.TagNumber) Like '" & Me!TagSearch & "')) AND ((Products.CategoryID)= & '"Me!CategorySearch & "'));"

 

[jaycie]

strWhere = " WHERE (((Products.TagNumber) Like '" & Me!TagSearch & "')) AND ((Products.CategoryID)= & '"Me!CategorySearch & "'));"
--------------------------------------------------------------------------------
Thanks for your response. But, I tried that statement, and the program kept on giving me an error, saying: "End of Statement expected."

What's going on? Is the fact that I am using Access 97 make a different? Or do I have to declare something else previous to the statement?

 

[Travis]

Try adding this line after you create the SQL string:

Debug.Pring strSQL

This will dump the string to the "Immediate" Window. Copy and paste this to the QBE Grid of a new Query. Try running the Query from there. The Error should then become obvious.

 

[The_Doc_Man]

Is the form bound or unbound?

If it is bound, then you might be executing your code but when you do the requery, you end up resetting it - because the requery reloads the data on which the search depends.

Also

Access is notoriously uncooperative when it has too much data as well as when it has not enough. What happens if the search comes back with more than one value?

Things to think about.