Search String in Database Tables

[atl10spro]

Hello Everyone,

I am new to MS Access and although I have created several different
databases I lack the VB knowledge to code a search function. I am
turning to your expertise for assistance. I am using MS Access 2003.

This is what I am looking for:

A text field for the user to enter the search string or keyword.

Once the user enters the string or a partial string (i.e. *house* or
hou* or *use), the search funtion locates all the records in ALL
database tables that match the string. It is OK for the user to enter
*str or *str* or str* to locate the string (keyword) in all the fields
of ALL database tables.

For output, a new form comes up showing the entire row in the database
table(s) that match the string.

The user moves the mouse pointer to the record, shown on the output
form, to be updated or deleted.

The user updates the record and presses a save button on the form to
store the data back in the table(s). The form also has a delete button
to get rid of the record.

It is OK to illustrate the string search functionality using one
database table; instead of searching multiple tables in the database,
if it is easier to code.

Your expert advice is greatly appreciated.

Thank you,

Edwin

[pietlinden]

at*******@gmail.com wrote:

Hello Everyone,

I am new to MS Access and although I have created several different
databases I lack the VB knowledge to code a search function. I am
turning to your expertise for assistance. I am using MS Access 2003.

This is what I am looking for:

A text field for the user to enter the search string or keyword.

Once the user enters the string or a partial string (i.e. *house* or
hou* or *use), the search funtion locates all the records in ALL
database tables that match the string. It is OK for the user to enter
*str or *str* or str* to locate the string (keyword) in all the fields
of ALL database tables.

For output, a new form comes up showing the entire row in the database
table(s) that match the string.

The user moves the mouse pointer to the record, shown on the output
form, to be updated or deleted.

The user updates the record and presses a save button on the form to
store the data back in the table(s). The form also has a delete button
to get rid of the record.

It is OK to illustrate the string search functionality using one
database table; instead of searching multiple tables in the database,
if it is easier to code.

Your expert advice is greatly appreciated.

Thank you,

Edwin

Sounds like a dodgy design if you're storing the same information in
different fields in different tables (well, unless it's a foreign key).
it's possible to do what you're asking, but if you wanted to union the
results together for all the tables (so you'd get one big list of
matches), the result would not be updateable. I guess you could create
listbox or something and add the number of records matching in each
table...

the basic syntax is

SELECT *
FROM [someTable]
WHERE [somefield] LIKE "*" & Forms!MyForm!txtSearchString & "*"