Searching from a form!!!

  • Thread starter Thread starter abdul wahab via AccessMonster.com
  • Start date Start date
A

abdul wahab via AccessMonster.com

i know this has come up before - however, i am a novice and do not
understand. sorry, but here goes.
i have a "Cars" table in which details of cars are kept. I have a form
"frmSearch" which i want to use to find cars.
i want to be able to put in a few values regarding my car database, eg.
mileage, make, colour,model. Then I want the form to show me all the cars
that fit the description. If there are none, i need somekind of error
message.

if any1 can help me out - i shall be extremely grateful.
ps. great forum this is.
 
The easiest thing to do is to use the Filter By Form functionality.

If you have not set up a custom toolbar for this form, then the filter by
form icon should be showing on the toolbar when you open the form. If you
have created a custom toolbar, you can just add the filter by form and
filter commands to the custom toolbar.

The filter by form icon looks like a funnel with a database window next to
it. When you click on it, it gives you your form all fields blank. You
then enter the data you want to search on into the fields that contain that
data. For example, if you want to search on make and model, you would enter
the make and model into those fields and leave everything else empty. When
you click on the filter icon (it's the funnel by itself), it filters the
recordsource for your form so that it only shows those entries that meet the
filter conditions you entered. Assuming you're displaying the record
navigation buttons, it will tell you how many records it found that meet the
conditions. If no records are found, it will display 0 records.

You can also do something like this in code, but that involves dynamically
building a SQL select statement and then making the SQL statement the filter
or the recordsource for the form. You would have to display a form to
accept the data to be searched (essentially a duplicate of your entry form),
then cycle through the fields to see which had data entered into them, and
then open another form to display the results. It's a lot of work to do
exactly what the filter by form already does.
 
Hi, Abdul.

The first key to implementing such a thing is to have control over solid
data entry throughout your database, to avoid problems like having the
following Makes accumulate over time:

Chevrolet
Chevy
Chevvie

If you've done this, and you're tables are normalized, and are using combo
boxes for data entry to limit the available entries, then it makes
implementing what you want easy. If not, you'll have to do some work, so
post details of the database as it exists now.

Assuming you can proceed, how you implement your form depends on how you'll
use it. If you will *always* enter a value in each control on the form, then
your query can include a reference to the form control in its "Criteria" row,
e.g.,

= Forms!MyFilteringForm!Model

as the criteria forthe Model field. And, presumably, you'd want mileage to
be less then your entered value, so its criteria might be:

< Forms!MyFilteringForm!Mileage

If you intend to leave some items blank on occasion, the query will try to
match the Null value, and will only return records that have a Null value in
that field, rather than not filtering the records at all. In this case, you
can build a filter string to use for the query. I like to use a textbox
control on the form, which I leave visible while debugging, and make
invisible once it's working.

The general strategy is to write a custom procedure that gets called in the
AfterUpdate event of each filtering control. It loops through all the
controls on the form, and if it's a textbox or combo box control, builds a
filtering string. I use the Tag property of each control to hold the field
name I want to match. Something like:

Dim ctl As Control
Me!txtFilterString = "" ' Initialize invisible Textbox control
that holds filter

For Each ctl in Me.Controls ' Loop through form's controls collection
If (ctl.ControlType = acComboBox OR ctl.ControlType = acTextBox) Then
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & ctl.Tag & "]=" & ctl.Value &
" AND "
End If
End If
Next ctl

' Strip off final " AND "
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

Then, use a command button to preview the report, applying the filter if its
not blank:

Dim strDocName As String
Dim strFilter As String

strDocName = "YourReport"
strFilter = ""

' If no criteria selected, preview report with no filtering
If Nz(Me!txtFilterString) = "" Then
DoCmd.OpenReport strDocName, acViewPreview
Else
' Otherwise, apply the filter
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

Hope that helps.
Sprinks
his would mean using combo boxes for
 
hi, many thanks for the above posts - most helpful.
there is now a search form in place. the problem is, i have cascading combo
boxes - which lookup the "Make" of the car, and then the "model" are only
shown for that make.
On the search form, it collects its data from the Cars table and displays
these as numbers. these numbers correspond to the numbers for the cars used
for the cascading combos - however, i need the names.
i hope that all makes sense and wud be extremely grateful if anyone can
help.
thanks once again...
 
Back
Top