Creating a search page....

  • Thread starter Thread starter SMAN
  • Start date Start date
S

SMAN

Why in the world is it so dificult to create a search page from a form....I
have two combo boxes, text fields and check boxes and want to create a
search form on a table with it. What's the easist way to do it...actually,
I'll start easy and use only one combo box with a distinct query attached to
it to populate it and a subform for the results page....any ideas???

Thanks

Ken S.
 
The easy way is to simply use the after update event (or, later, you might
even use a search button).

The code I use to do this in the after update event looks like:

dim strSql as string
dim strWhere as string

strSql = "Select * from tblCustomers"

if isnull(me.comboSalesRep) = false then
strWhere = "salesRepID = " & me.comboSalesRep
end if

' I also have a city box to select names only from a particular city.

if isnull(me.comboCity) = false then
if strWhere <> "" then
strWhere = strWhere & " and "
end if
strWhere = strWhere & "City = '" & me.comboCity & "'"
end if

' note how you can add as many conditions as you want.

You then go:

strSql = strWhere & " where " & strWhere

me.MySubFormCtrl.Form.RecordSouce = strSql

You can see some screen shots of the above code in action at:

http://www.attcanada.net/~kallal.msn/Search/index.html

and

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

And, this stuff works even better for reports. Here is some screens that
also use the exact above code idea for the "print" button:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

So, for each control you add, you simply write small bit of code. And, note
how in the last example for reports I tell the users "blank = all" . So, in
other words, if some the text boxes, or combo boxes are NOT set, then they
are ignored...
 
Back
Top