Search and display in same form

[Anon]

I'm creating a search form for my database, and because of legacy
issues, I need the end user to be able to type in the information they
want to search for directly into the fields of the form, click "search"
and have the results displayed in the same form (or an identical form).

There are multiple fields, and if left blank, should not be used as the
search parameters. Filled fields should reply to ? and * queries as
usual.

Is there a way to do this (Access 97) and if so, can someone explain it
to me, or show me an example?

Thanks.

 

[Marshall Barton]

I'm creating a search form for my database, and because of legacy
issues, I need the end user to be able to type in the information they
want to search for directly into the fields of the form, click "search"
and have the results displayed in the same form (or an identical form).

There are multiple fields, and if left blank, should not be used as the
search parameters. Filled fields should reply to ? and * queries as
usual.

Is there a way to do this (Access 97) and if so, can someone explain it
to me, or show me an example?

Not a particularly unusual feature in a comprehensive
application. If there are only a few data fields, you could
use the data form's Header section for the search controls.
If there are a significant number of data fields, you should
use a separate form for this. I usually start by making a
copy of the data form and remove any buttons or other
controls that are not relevant to a search operation. Then
remove the search form's RecordSource and each control's
ControlSource to make the search form unbound.

The code behind the search button may be relatively easy or
complex according to your experience with this kind of
thing. You're going to have to use code to construct an SQL
Where clause. Depending on what kind of search features you
need to implement, you might be able to use a little code
such as:
If Not IsNull(txtLName) Then
strWhere = strWhere & " AND LName = """ & txtLName & """"
End If

for each search criteria. If you need to use wildcards and
other complex search features, then you should look into
using the BuildCriteria function to construct expressions
involving operators such as Like, Between, IN, etc.

After the Where clause is constructed, you can open the data
form using the OpenForm method's WhereCondition argument to
restrict its records to those matching the user's criteria.

If you need more help once you get started, come back with
specific questions about any detailed problems.