Subform/Mainform Search Nightmare

[IrwinHunter]

I am having a problem creating a form that will find specific records in my database.

Background:

I am using Access 97 and Windows NT Service Pack 6.

I have created a simple relational database with one table containing addresses of suppliers and one table containing contacts for those suppliers (there are other tables too, but they're not really the problem).

I have a form with two sections seperated by a tab control. The first tab has the supplier details, the second tab has a sub form which displays the contacts for that supplier.

I want to create a facility whereby a user can search for a supplier OR contact. If I use the 'Find' control from the standard buttons, it works with the supplier records on the supplier tab, but if I put it on the contacts tab, it only picks out those contacts related with the provider you've just tabbed accross from (as you might expect!).

I need users to be able to see other records related to the one they've searched for so a query wouldn't be acceptable. e.g. if a user is looking for a contact named Mr Smith, they may also want to see who the other contacts are and which supplier he works for.

Is there a way of doing this? Possibly from a form?

I've tried doing a macro that uses the find record facility and then opens the main form up, but it always opens it on the first record, not the one that matches the search criteria.

So in conclusion - I need to create a form into which I can enter search criteria for suppliers and / or contacts and then display the results within the main form.

I would really appreciate any help on this as it's driving be mad. From other stuff I've read on this forum, you lot seem to be on a much more advanced level than I am - PLEASE HELP!!!!

Thanx

IrwinHunter.

 

[Aivars]

Hi Irwin!

I think your problem is solved in the MS Access Sample DB (Northwind.mdb, Orders.mdb, Solutions.mdb). Look for solution of your problems in the listed DBs.

Aivars

 

[IrwinHunter]

Thanks for the tip Aviars, but there was no example in the sample dbs that matched my query. All the forms that were featured used a drop down list to select the record, and then the information was diplayed on the same form.

What I want is a search facility were a user can type in the name of a contact then that record is opened in the form with the tabs on (see original description).

i.e. I'm looking for, Bob Smith. I go to the search form and type in his name in the search box. The main form opens on the contacts tab and displays Bob's record. From here I can also view the details of bob's company and the other contacts for the company too.

Sorry Aviars, this is really hard to explain!

If you want I can e-mail my db to you and then you'll know exactly what I mean.

Thanks for your help.

 

[Zeroanarchy]

THis example allowes you to filter a field by a choice of a combo box.

eg. if say you had a list of customers and you wanted to filter the form on customers. make the combo box a list of customers which are stored in the field.
the field = controlsouce. customers from the customers table.

'After Update Event of the Filter for Customer Combo Box
DoCmd ApplyFilter , "[field] = Forms![combobox]![combobox]"

Hope it is of some help to u.

Zero