Creating a Search function in Access Table

Question

I have created a nice search form for my 1 table database that allows users to search the table based on 5 criteria such as name and date. This will produce a query based on what was searched for

However, I would like to add this search form into the table somehow so that when people use the database they can search the table without having to pull up the separate form

Is this possible? I have less than a week of access experience and I am using design view to create my form

Thanks

Answer 1

As Bill points out you can't incorporate this into a table's datasheet, and in any case users should never be given access to a table in raw datasheet view, but only via forms or reports.

I assume what you are aiming at is a single interface which both allows the user to search the table, and display the results of the search.  This can be done with a form in continuous forms view by incorporating the unbound controls to search the table in the form's header, and displaying the rows from the table in the form's detail section.  You'll find an example in ComboDemo.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes an option for 'drilling down' through a hierarchy' via a form which includes three combo boxes in its header.  The fact that the data in this case is hierarchical is by-the-bye, however; the columns (fields) being searched could be independent of each other.

The way it's done is to base the form on a query which references the three combo boxes as parameters:

SELECT Counties.County, Districts.District, Parishes.Parish, Locations.Location

FROM ((Counties INNER JOIN Districts ON Counties.CountyID=Districts.CountyID)

INNER JOIN Parishes ON Districts.DistrictID=Parishes.DistrictID)

INNER JOIN Locations ON Parishes.ParishID=Locations.ParishID

WHERE (Counties.CountyID=Forms!frmDrillDown!cboGotoCounty

OR Forms!frmDrillDown!cboGotoCounty IS NULL)

AND (Districts.DistrictID=Forms!frmDrillDown!cboGotoDistrict

OR Forms!frmDrillDown!cboGotoDistrict IS NULL)

AND (Parishes.ParishID=Forms!frmDrillDown!cboGotoParish

OR Forms!frmDrillDown!cboGotoParish IS NULL)

ORDER BY Counties.County, Districts.District, Parishes.Parish;

The important thing here is that not only does the query test for the value in a column equalling that of the relevant control in the form, but also for the parameter (the control) being Null, i.e. left blank.  The county combo box for instance is referenced in the query's WHERE clause with:

(Counties.CountyID=Forms!frmDrillDown!cboGotoCounty

OR Forms!frmDrillDown!cboGotoCounty IS NULL)

Note how the OR operation is parenthesized.  This is important as it forces the operation to evaluate independently of the AND operations.  The way it works is that if the value in a row equals that of the control in the form the first part of the OR operation will evaluate to True, so that row will be retuned; if the control is Null then the second part of the OR operation will evaluate to True regardless of the value in the column, so all rows will be returned.

By doing the same for each control in the form's header, and tacking the parenthesized OR operations together with AND operators, only those rows for which all three of the parenthesized OR operations evaluate to True will be returned.

To cause the form to show the rows restricted on the basis of the selections in the combo boxes it is necessary to requery the form.  This is done in the AfterUpdate event procedure of each of the three combo boxes with the line:

Me.Requery

There is other code in the AfterUpdate event procedures, but that is only because of the hierarchical nature of the data, and correlates the combo boxes.  Where there is no such hierarchical relationship you only need the single line to requery the form as above.

BTW to view the form's RowSource query do so by zooming the RowSource property in from design view.  Don't use the build button (the one with 3 dots) to view the RowSource in query design view as Access will move things around and the logic of the query's WHERE clause will be obscured.  When writing a query like this, with a series of parenthesized OR operations, always do it in SQL view.  You can use the query designer to build the basic query without the WHERE clause, and then switch to SQL view to add the WHERE clause.

Answer 2

There is no way to put a search form "into" a table. A form is the best way. In fact, you shouldn't allow users direct access to the tables. It's too easy to destroy data that way.