Simple Search Form in Access 2007

[ChicagoPete]

Hi all,
Have a very simple db, holds basic demo data (LName, FName,DOB,SSN,
Add1,Add2, City, St,Zip, etc etc..) so the input form includes all of those
fields. Now the end-users want another form that has all those fields but
they want to, for example, just enter a city name and hit a SEARCH button on
the form that will return all matching records in a grid format. How can I
accompish this... that would look at any/all fields with data in them to
return the correct values?

Thanks

 

[bcap]

Try experimenting with the Split Form view in Access 2007. It's great for
this sort of thing.

 

[Ken Sheridan]

1. Firstly create an unbound dialogue form, frmSearch say, with controls
corresponding to the columns (fields) on which you wish to search, e.g.
txtCity to search by city (though a combo box listing all cities would be
better).

2. Now create a query which returns all rows from the relevant tables and
in each of the columns to be searched enter a reference to the relevant
control on the form in the first 'criteria' row of the column in question,
e.g. in the City column's criteria row you'd put:

Forms!frmSearch!txtCity OR Forms!frmSearch!txtCity IS NULL

You'll find that if you save the query and reopen it in design view Access
will have moved things around. Don't worry, it will work just the same.

3. Do the same for the other columns.

4. Now create a form in continuous form or datasheet view with the query as
its RecordSource property.

5. Go back to your first unbound form created in step 1 and add a button to
open the form you created in step 5 (the button Wizard can set this up for
you).

In the unbound form the user can enter parameters into any of the controls,
either singly or in combination, e.g. they might enter Chicago into txtCity,
John into txtFName and Doe into txtLName to find all the John Does in
Chicago; or they might just enter Chicago into txtCity to find everyone in
Chicago.

All they then have to do is press the button to open the bound form
restricted to those rows which match the parameters entered.

You might want another button on the unbound form to clear all the controls
ready for a new search. For this you'd put the following code in the
button's Click event procedure:

Dim ctrl As Control

On Error Resume Next
For Each ctrl in me.Controls
ctrl = Null
Next ctrl

If you are unfamiliar with entering code in event procedures its done like
this:

Select the button in form design view and open its properties sheet if its
not already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

Ken Sheridan
Stafford, England

 

[ChicagoPete]

Ken,
It worked great until I added the "OR Forms!frmSearch!txtCity IS NULL" to
the query criteria then it returned ALL records, so I removed the "OR
Forms!frmSearch!txtCity IS NULL" portion and it worked great!

I still cannot get the frmSearch to clear using the me.controls = Null. I
reposted that portion of my question in FORMS.

Cheers!

 

[Ken Sheridan]

Omitting the OR operation will be fine if (a) the dialogue form contains only
one control in which to enter or select a parameter value, or (b) it contains
multiple parameter controls and values are being inserted into all of them.
The purpose of testing for OR IS NULL is that it makes a parameter optional.
Say you have just two parameter controls txtCity and txtLName then the WHERE
clause of a query would be like this:

WHERE (City = Forms!frmSearch!txtCity
OR = Forms!frmSearch!txtCity IS NULL)
AND (LName = Forms!frmSearch!txtLName
OR = Forms!frmSearch!txtLName IS NULL)

If a value is entered in txtCity then the first part of the parenthesised
expression (City = Forms!frmSearch!txtCity OR = Forms!frmSearch!txtCity IS
NULL) will evaluate to TRUE for any row containing the value in the City
column. As the expression uses a Boolean OR the whole parenthesised
expression will consequently evaluate to TRUE. If LName is left blank
(NULL), then the second parenthesised expression (LName =
Forms!frmSearch!txtLName OR = Forms!frmSearch!txtLName IS NULL) will also
evaluate to TRUE for all rows as the second part of the expression will
evaluate to TRUE for any row in the table. Consequently the WHERE clause as
a whole evaluates to TRUE for any row with the value in the City column,
regardless of what's in the LName column.

If the OR IS NULL operations are omitted then each expression must evaluate
to TRUE for a row to be returned, i.e. the values in the City and LName
columns must both match those entered in the form, and neither control can be
left blank.

Unfortunately, if you enter the criteria in query design view rather than in
SQL view Access moves things around quite a lot, making the underlying logic
of the WHERE clause far less easy to see. I'd always write and save a query
like this in SQL for this reason.

To clear the controls you don't use Me.Controls = Null, you loop through the
form's Controls collection setting each control to Null, ignoring the error
which is raised if a control is a type which cannot be set to Null:

On Error Resume Next
For Each ctrl in Me.Controls
ctrl = Null
Next ctrl

Ken Sheridan
Stafford, England

 

[ChicagoPete]

Thanks Ken...

"clear as mud" but I got it!

Omitting the OR operation will be fine if (a) the dialogue form contains only
one control in which to enter or select a parameter value, or (b) it contains
multiple parameter controls and values are being inserted into all of them.
The purpose of testing for OR IS NULL is that it makes a parameter optional.
Say you have just two parameter controls txtCity and txtLName then the WHERE
clause of a query would be like this:

WHERE (City = Forms!frmSearch!txtCity
OR = Forms!frmSearch!txtCity IS NULL)
AND (LName = Forms!frmSearch!txtLName
OR = Forms!frmSearch!txtLName IS NULL)

If a value is entered in txtCity then the first part of the parenthesised
expression (City = Forms!frmSearch!txtCity OR = Forms!frmSearch!txtCity IS
NULL) will evaluate to TRUE for any row containing the value in the City
column. As the expression uses a Boolean OR the whole parenthesised
expression will consequently evaluate to TRUE. If LName is left blank
(NULL), then the second parenthesised expression (LName =
Forms!frmSearch!txtLName OR = Forms!frmSearch!txtLName IS NULL) will also
evaluate to TRUE for all rows as the second part of the expression will
evaluate to TRUE for any row in the table. Consequently the WHERE clause as
a whole evaluates to TRUE for any row with the value in the City column,
regardless of what's in the LName column.

If the OR IS NULL operations are omitted then each expression must evaluate
to TRUE for a row to be returned, i.e. the values in the City and LName
columns must both match those entered in the form, and neither control can be
left blank.

Unfortunately, if you enter the criteria in query design view rather than in
SQL view Access moves things around quite a lot, making the underlying logic
of the WHERE clause far less easy to see. I'd always write and save a query
like this in SQL for this reason.

To clear the controls you don't use Me.Controls = Null, you loop through the
form's Controls collection setting each control to Null, ignoring the error
which is raised if a control is a type which cannot be set to Null:

On Error Resume Next
For Each ctrl in Me.Controls
ctrl = Null
Next ctrl

Ken Sheridan
Stafford, England