Search Form - text string.

[Matt Reed]

Being brand new in Access I need some advice where to start.

Have a database listing Seminar Articles.
It consists of a main table (tblArticlesMain) with lookups into several
other tables.
[ID],[Title],[Lookup to tblAuthor],[Lookup to tblYear],[Lookup to
tblSeminar], [Lookup to tblVolume],[Lookup to tblTab]

There is a form built to input data and it works fine.
The question is how to let the users search for data. On items like the
Author's name I have a form with a ComboBox listing the Author's name and a
subform with a listing of all the fields from tblArticlesMain. This is
working fine. Can repeat for any individual search using a combobox to
select an item in the field.
What I want to do is add a textbox were I can enter a word and then check
then [Search] the title field for any listing that includes that word
anywhere in the Title.

Where do I start?

Have Main Form, textbox, command button, subform and I am totally lost..

Any and all direction gladly accepted.

Matt

 

[John Vinson]

Being brand new in Access I need some advice where to start.

Start by getting rid of all your Lookup fields, for one thing! See

http://www.mvps.org/access/lookupfields.htm

for a critique.

Have a database listing Seminar Articles.
It consists of a main table (tblArticlesMain) with lookups into several
other tables.
[ID],[Title],[Lookup to tblAuthor],[Lookup to tblYear],[Lookup to
tblSeminar], [Lookup to tblVolume],[Lookup to tblTab]

so what your table contains is numeric AuthorID, YearID, SeminarID,
VolumeID and TabID fields. The fact that your table contains numbers
is concealed from your view by the Lookup misfeature - it LOOKS like
it contains an author name, BUT IT DOESN'T.

There is a form built to input data and it works fine.

You could build the form with the combo boxes without the Lookup
fields. Lookups are fine on Forms; they're dreadful in tables, as
you're finding out.

The question is how to let the users search for data. On items like the
Author's name I have a form with a ComboBox listing the Author's name and a
subform with a listing of all the fields from tblArticlesMain. This is
working fine. Can repeat for any individual search using a combobox to
select an item in the field.
What I want to do is add a textbox were I can enter a word and then check
then [Search] the title field for any listing that includes that word
anywhere in the Title.

Use a criterion of

LIKE "*" & [Forms]![YourForm]![Search] & "*"

in your Query.

 

[Matt Reed]

Thanks John,

Your note and the included site bring up some good points.

I believe I'm going to head out get one of your books.

But to start with - I used those dreaded "Lookup fields" to reduce errors
when entering the data. On many of the fields listed (year, tab, volume)
this is not such a big issue. Yet on the Seminar name, it could be quite
long and only a little different than some of the others. These seminars
are repeated year to year and each time the are presented they include 10-30
different titles. So having someone repeatedly typing in these names would
greatly increase the chances of an input error.

So if I do not use Lookup fields what should I use?
I believe this is a basic table design question and I need to learn the
basics better (first). If one of your books, someone else's book or a web
site handles this topic well - please point me in the right direction rather
than trying to teach me the basics on a newsgroup.

Thanks for your input, I would much rather learn that I going in the wrong
direction than how to fiddle with the system to make something work anyway.

Matt

Being brand new in Access I need some advice where to start.

Start by getting rid of all your Lookup fields, for one thing! See

http://www.mvps.org/access/lookupfields.htm

for a critique.

Have a database listing Seminar Articles.
It consists of a main table (tblArticlesMain) with lookups into several
other tables.
[ID],[Title],[Lookup to tblAuthor],[Lookup to tblYear],[Lookup to
tblSeminar], [Lookup to tblVolume],[Lookup to tblTab]

so what your table contains is numeric AuthorID, YearID, SeminarID,
VolumeID and TabID fields. The fact that your table contains numbers
is concealed from your view by the Lookup misfeature - it LOOKS like
it contains an author name, BUT IT DOESN'T.

There is a form built to input data and it works fine.

You could build the form with the combo boxes without the Lookup
fields. Lookups are fine on Forms; they're dreadful in tables, as
you're finding out.

The question is how to let the users search for data. On items like the
Author's name I have a form with a ComboBox listing the Author's name and a
subform with a listing of all the fields from tblArticlesMain. This is
working fine. Can repeat for any individual search using a combobox to
select an item in the field.
What I want to do is add a textbox were I can enter a word and then check
then [Search] the title field for any listing that includes that word
anywhere in the Title.

Use a criterion of

LIKE "*" & [Forms]![YourForm]![Search] & "*"

in your Query.

 

[John Vinson]

Thanks John,

Your note and the included site bring up some good points.

I believe I'm going to head out get one of your books.

Thanks... but I haven't published any. My esteemed colleague John
Viescas has, and I can heartily recommend any of them.

But to start with - I used those dreaded "Lookup fields" to reduce errors
when entering the data.

Yes. They do so IF YOU ARE ENTERING DATA USING A DATASHEET.

My objection is not to using Combo Boxes and lookups. My objection is
to using them *in a Table*. It's a good tool, it's just the wrong
place to use it.

So if I do not use Lookup fields what should I use?

You should use a Form with combo boxes, or listboxes or any of the
numerouus other tools Access provides. Absolutely, you want the combo
box functionality - but on a Form!