Please Help

[matto336]

I am attempting to create a form which will give me some flexibility for the
criteria of a query. What I wish to do is allow the user to input criteria
into a form using several available options, for instance a state and city,
say New York City, New York, and see all available matches, but also have the
option to simply input New York as the state and have the query return all
results with the more broad criteria. I haven't got the slightest idea on how
to start so anything would be helpful. Basically my goal is to have the
option for the most specific query criteria to the broadest criteria through
several controls on a form. Thanks for any help.

Matt

 

[Beetle]

There is a very good example of this type of search form at;

http://allenbrowne.com/ser-62.html

 

[Jim Bunton]

what you need to do in general terms is adjust the recordsource os the form
depending on the criteria set.

say you have a column in your table called city and another called state.

then place 2 combo boxes in the forms header
rowsource for combo 1 "SELECT DISTINCT City From MyTable" (gives the name of
all the cities used so far)
rowsource for combo 2 obvious?!

Onclick event for combo 1
dim sql as string
sql = "Select * From MyTable WHERE city = '" & me.citycomboname & "'"
(note the single quotes used because city is a string)
me.recordsource = sql
me.requery

note:
me.citycomboname returns the value selected by the combo and the &'s use
this to build an sql query. If you have problems with building this put a
break point on the line after the sql then run the event. In immediate mode
type ? sql then paste the sql created into the query builder : query > New >
show the sql ans paste your's into here. Try run the query - if it doesn't
work hack it until it does then change your onclick code to create this
query - which works!

You will need to build in a little house keeping to show your user which
combo is being CURRENTLY used (eg alter a label to show this or set the
others to "".

You can use the same approach to create more complex results but beware,
City = New York and State = Texas is pretty likely to return no records!

Jim Bunton