Search String Function

[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

 

[Guest]

Hi Edwin,

Gosh!

This requirement will prove to be very complex. However let's try to go
some way towards solving it.

First I would recommend you examine the syntax of the Like function and
train your users to use exactly this syntax when specifying the search
string. This at least avoids you having to reconstruct a search string
within your project. (I can think of no other way of comparing and selecting
records other than by use of the Like function. Anyone else?)

Columns within a table can be one of many data types. Your explanation
implied that you want to search strings so you need to reject other data
types such as Boolean (Yes/No), Hyperlinks, Numbers, etc. I think the Like
function will crash if you don't supply a string as an argument. I suppose
you could always use the CStr function to convert numbers and boolean values
to strings. Anyway whatever you choose you will need to step through the
metastructure of the table to determine what columns it contains and what the
column data type is. You then need to ignore any column that is not Text or
Memo. To access the metastructure you need to include a reference to the DLL
called 'Microsoft ADO Ext for DDL and Security.' (This is the ADO library; I
can't identify the equivalent DAO library. Anyone?)

Alternatively you can hard-code selected column names into your routines.

Having found a match you need a mechanism for capturing details about it.
The mechanism itself is not too difficult - a table, a collection object or
even a dictionary will serve the purpose. I would recommend a table since a
table lends itself to being bound to a combo box, list box or continuous
form. But I foresee difficulties since every table will have a different
structure; how are you going to devise a consistent generic extract from
different table structures? Apart from table name being a text string there
is nothing certain.

I would suggest your output lists the table names and the number of matching
records. Then if the user wishes to drill down provide a mechanism whereby a
continuous form opens geared to that particular table and that shows only the
matching records. Unfortunately this restricts the flexibility of the
solution and means you need to reprogram for any change in the tables.

From there on in it's quite conventional.

Regards,

Rod

 

[atl10spro]

Hi Rod,

Thank you very much for all your recommendations. I will definetely
take them into consideration.

Regards,

Edwin