search ability on nvigation form

[emcf]

Morning all,

we have a database (not set up by me) that holds various records in different tables. There are about 6 forms in total that are accessed via a main navigation form. I have been asked to set up a search facility on this main navigation form that will search the underlying tables for keywords.
to explain the situation further - the tables hold information on firms we do business with (mortgage packaging companies, solicitors, valuers etc), details of large loans (eg borrower name, accountant, packager, valuer), details of suspected fraud cases, and details of our underwriters.
what i need to do is have some sort of search located on the main nav form that will allow the user to enter, say, the name of a packaging company and then return all the records that this firm appears in - the firm may appear in the table holding large loan details, suspected fraud table and, of course, the packager table.

is this possible with access and if so am i on the right track in thinking it can be done with a command button??

any guidance gratefully received.

ta,

 

[FoFa]

It can be done, how easy or complicated is a matter of system design. Either current or future. First question is once the search is complete, how are the search results to be returned? Second question are all your tables joined some how? Third, is the app. shared, and how many users if it is? Fourth, any security issues like userA can not search table PAYCHECK_HISTORY (as an example).

 

[emcf]

thanks fofa,

point 1): results are to be returned in the form of a report

point 2): db was not designed by me and the tables are linked but not to the best of access' abilities. I will probably have to end up starting from scratch and properly linking all the tables.

point 3); app is shared, accessed by about 5 users from a shared drive on our network.

point 4):no security issues - if they have access they can see everything.

 

[FoFa]

You could setup a big joined query and use the search criteria in it, or you could use a union query to search each table, but you have to return like columns if you do that. You could setup a temp table, use something like a logged in ID or even timer as a unique user stamp and populate this table with the matches from each table and base a report on that.

 

[goaksmith]

On the website www.mile50.com there are search engines that you can download. You copy and paste some tables, queries, modules and a form into your database do a few minor steps and you are up and running. It gives you step by step instructions. I found it a lot simpler than creating my own search engine.