Search Form - How do I modify a query object from VB?

[medmister]

OK, I have been struggling with this one.
I created an inventory database. I made a "Search" Form that is supposed to crossreference a few tables to give you all the information on a group of hardware.

There are 9 fields which all default to "*" which are for entering the criteria for the search, and then a button, which refreshes the subform that displays the results of a query.

The search query criteria says that every field has to be LIKE the one in its corrosponding box on the search form. That way, the user only has to enter criteria in the fields they want to use to narrow down the results, and the fields left as "*" will match everything.

The problem here is that I'm not getting complete results when I search. Apparently WHERE LIKE "*" does not really mean any value is acceptable.

What I wanted to do then, is make it generate the SQL search query on the fly, and then display the results in the subform. For each criteria box that has something in it on the form, it would append an additional WHERE onto the Query, and then run it.
There are two ways I can think of doing this, either modifying the criteria of a query object, or putting together the SQL statement in VB and then displaying the results in the subform somehow. I can't figure out how to do it with either approach, after spending lots of time searching the web and access help.

Please, give me some tips!

 

[Darrylles]

Hi medmister,

I have done this a few times, and my solution is to build the SQL string via VBA.

The SQL string starts as "SELECT * FROM {query name}".
{query name} joins ALL of your tables, selecting all values.

Then count number of fields changed by user.
If count > 0 then
SQLStr = SQLStr & " WHERE "
EndIf

Then use IF statements to build the SQL string up...

IF field1 <> "*" THEN SQLStr = SQLStr & field1 & " LIKE " & txtField1

You'll need to check when SQLStr has had the first condition added in order to know whether or not to add " AND " when you come to subsequent additions.

Hope this helps,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[medmister]

That looks like what I was planning as far as building the SQL string, but with that approach, how do I make the results display in the subform?

 

[medmister]

Thank you very much, BTW!

 

[medmister]

Never mind, I figured it out. I just create a table for the results and set the source object to that table.