combobox and/or textbox for searching

[Guest]

I have a form that I use to input records into a table. I would like to use
another instance of that same form to conduct and display searched for
records.

I want to be able to point to a specific field and then enter search
criteria in a textbox to search the table for matching records. The field
could be anyone of about 20 in the table. I thought using the combo box would
make the users ability to choose which field they searched on simpler and
then enter a criteria in the textbox and a command button to execute.

I have only one table and only one form, so it seems it should be easy?

I am fairly lame at this access stuff, mostly because I'm learning and busy
trying to do my other job too. If anyone has a straight forward suggestion or
example I would appreciate it.

 

[Carl Rapson]

I have a form that I use to input records into a table. I would like to use
another instance of that same form to conduct and display searched for
records.

I want to be able to point to a specific field and then enter search
criteria in a textbox to search the table for matching records. The field
could be anyone of about 20 in the table. I thought using the combo box
would
make the users ability to choose which field they searched on simpler and
then enter a criteria in the textbox and a command button to execute.

I have only one table and only one form, so it seems it should be easy?

I am fairly lame at this access stuff, mostly because I'm learning and
busy
trying to do my other job too. If anyone has a straight forward suggestion
or
example I would appreciate it.

You could do it that way, as long as the user only needs to search on a
single field at a time. Another method is to create a form that contains
controls for every possible field to search on; the benefit to this is that
the user can then search on more than one field simultaneously. In both of
these cases, you will need to manually apply your filter to the form; I do
it by build a complete SQL SELECT statement, incorporating the selected
search fields, and making it the form's Record Source.

The simplest approach, however, is to use the same form you already have and
click on the Filter By Form button in the menu bar. Then, the user can enter
criteria into multiple fields and click Apply Filter to display the matching
records. The benefit to this method is that it requires no additional
programming on your part.

Carl Rapson

 

[Guest]

What you are asking is not that straight forward, but I will give you an idea
of how it can work.
Use two combo boxes.
The first will be used to select a field to look in.
It should have a row source type of Field List. and it should be based on
your table. When you drop it down, it will list all the fields in the table.

Then you will need to use the After Update event of the first combo to set
up the second combo so it will work for you. That would involve creating a
new row source for the second combo.

Now you can select the value from combo. But, there are still a few odds
and ends. You have to use the standard method of making the record
containing the value the current record in the form. To do this, you have to
know the correct data type of the field so you can use the correct delimiters
in the code. And, you have to make a decision on how you will present the
data. In many cases, there will be multiple records that have a matching
value. Are you going to show all matching records, the first one, etc?

 

[Guest]

Carl,

Would it be possible to get some help? I can send whatever info for you look
at. Apparently there are many option for doing this. The user needs to be
able to search as many fields as possible because these records are based on
fault history and will be searched to find answers to possible repeat faults
instead of reinventing the wheel everytime. A fault history database. There
are many possible ways to ask a question in the field and being able to
search for part number or nomenclature or discrepancy will be the most
effective. Thanks for the advice and I appreciate any assistance. I am trying
to learn SQL, but I am under time constraints to get this working.

Tom

 

[Carl Rapson]

I don't know what you mean by "help", but I can tell you that I can't
provide any custom programming services. Perhaps someone else here who does
that kind of thing could jump in with an offer.

As I mentioned, you should be able to use Filter By Form to search, which
wouldn't require any VBA or SQL programming at all. When the form is open,
click on the Filter By Form button in the menu bar. Enter the search
criteria in the appropriate fields and click Apply Filter. Your form should
then be filtered to show only the records matching the criteria you entered.
Access Help has more information about Filter By Form, as well as examples
of how to enter search criteria using wildcards.

Carl Rapson

Carl,

Would it be possible to get some help? I can send whatever info for you
look
at. Apparently there are many option for doing this. The user needs to be
able to search as many fields as possible because these records are based
on
fault history and will be searched to find answers to possible repeat
faults
instead of reinventing the wheel everytime. A fault history database.
There
are many possible ways to ask a question in the field and being able to
search for part number or nomenclature or discrepancy will be the most
effective. Thanks for the advice and I appreciate any assistance. I am
trying
to learn SQL, but I am under time constraints to get this working.

Tom

<snipped>

 

[Guest]

I will want to show multiple records if they exist.

 

[Carl Rapson]

FilterByForm will show multiple records, if they all match whatever criteria
were entered. What happens is that the form's normal Record Source is
filtered to only include matching records. If you have the Navigation
Buttons enabled on your form, you will see the number of records at the
bottom of the form, along with an indication that the form is filtered. You
can use the Navigation Buttons to move between the records.

Carl Rapson