Text Search Form

[Access_idiot]

As you can tell by my user name, I am an Access idiot. I would appreciate any help I could be offered but try to keep it somewhat understandable for someone at my pathetic skill level.

I work for a small bank and am trying to set up a database that we can use to check a goverment list of terrorist (OFAC) before we perform certain transactions.

I've downloaded the list (it comes in 3 seperate sheets - each individual has a unique ID #, then each spreadsheet contains different information about that individual but always under the same unique ID number). I've imported those spreadsheets to Access, and set up a query that links the 3 tables. The query I set up prompts for the user to enter the name, and if it is on the list returns the information, if not on the list it returns a blank screen.

To appease auditors, I wanted to see if a form could be created, where the name is entered in a text box and all possible matches with their information could appear below....if there is not a match then it would return a statements saying "No possible matches". This screen could be printed out and retained for audit evidence.

I would be happy to email a copy of the spreadsheet as is for input or help.

Thank you in advance!!!!!!

 

[Corticus]

If you've already made a query to combine the three tables worth of data on ID, then you're almost there.

In design view, under criteria for the name field, add something like [enter name]. This will prompt you to enter a name, and then when the query is run, you will see only records containing that name. You can then hit Ctrl-P to print.

It's not elegant, but will get the job done quickly.

For a nicer looking solution, repeat the above, but base a form off this query. Put the form in datasheet view (under properties|format|default view). Save and close this form Then make another form, add a textbox for the name and insert the previously mentioned form as a subform. Save the form, and leave it open. Now go back to the query, right click on the criteria you entered for "name". Select "build" and "browse" to the textbox you added to your form. This reference to the textbox will become the name criteria for this query.

Finally, add a button to the out form, right click and select "Build code", and insert:
DoCmd.Requery
in the on_click event.
This will cause the subform to requery when you enter a new name and press the button.
You can also add 2 buttons on the outer form using the wizard to print and preview this query (or subform). If you really want it to look nice, create a new report, and set the reports recordsource to the query defined above, and have the print a preview buttons refer to this report.