Viewing data from drop down boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First of all I don't consider myself a whizz with Access but I have used it
before and I have been learning it as I was asked to do a project which I
think I am a little out of my depth with so any help is appreciated.

I have a database that contains information on 4000+ clients with numerous
fields such as Name, Address, etc, etc. I have been asked to create a form
that will allow somebody to use drop down boxes to search for 4 specific
fields (Insurer, Funder, Reason for Failure, Case Details). I want the user
to be able to drop down any of the drop down boxes or a combination of the 4
drop boxes to retrieve information in the database and bring it back to me in
a report.

As i said I am not the most advanced with access and I think I am very much
out of my depth here. Can anybody tell me if I am going the right way about
what I am trying to do or should I try something else? If anybody
mis-understands my scenario let me know and i'll try and explain it better.
Cheers
 
The usual way is to create a new form, not based on any table, in which you
put your four combo boxes, each with its record source set to the correct
field of the table. Save this: it will be the dialog box presented to the
user.

You then make a query, based on the table, with all of the fields you want
to include in the report including the four search fields. In the criteria
row of each of these four put the full name of the matching combo box in your
new form. This will be something like Forms![name of new form]![name of
combo box] and is easiest done with the expression builder in which you can
search for the right form and the combo box you need. You can test it by
opening your new form, choosing values and then leaving it open while you run
the query.

Then base a report on your query and design it how you like. Go back to the
new form and add a command button - you can use the wizard - that opens this
new report.

You're now about there - just need a convenient switchboard/form button to
open the search form.

Good luck!
 
If you have some VBA skills, you could test to see if each combo IsNull(),
and build a filter for the form from the ones that have a value.

The string needs to be a little different depending on the data type of the
field, e.g. Text fields need " around the search value, whereas date fields
need # as the delimiter.

For an example that illustrates how to handle each type, download:
http://allenbrowne.com/unlinked/Search2000.zip
Requires Access 2000 or later.
 
Back
Top