Hi, i have two tables:

tbl_company, tbl_contacts

tbl_company is the parent of tbl_contacts. Each company record has multiple contacts records. I have set up a form to contain fields from tbl_company. I also have a subform which contains, in tabular format, the fields from tbl_contacts. On the one screen will display the form with one company record and a tabular list of contact record associated with the company.

What i would like to do is create a search to filter records from both tbl_company & tbl_contacts. I have tried this by creating a query containing both these tables and using it as a control source for the form. My problem lies when multiple contacts are returned under the one company, when i click to go to the next record (the next company) i have to click the same number of times that there are contacts in the tabular list. This is annoying. Any ideas how i can properly set up my search, any examples out there?

Thankyou in advance,

Hi

Not really understanding your requirement

The normal use of a Main Form / SubForm layout is to enable you to select a Company and be able to see all Contacts for that Company

You wouldnormally have a ComboBox in the Header of the Form that allows you to select a Company and its details are displayed as wellas all contacts associated with the Company.

What do you mean when you say "from both tbl_company & tbl_contacts" - what do you want to see

Regards

Mike

Hi, thanks for your response. I would like to set up a search form which allows me to e.g. search by company name and first name in one search. So affectively searching from two tables.

My problem is with having a query which contains information from both tbl_company & tbl_contacts that when the results have been filtered, on some instances i have to click a couple of times on the form to go to the next company record. This is due to the number of contacts associated with the one company name.


e.g.: filter tbl.company.companyName = 'nike', tbl_contacts.FirstName = 'John' would return

companyName FirstName
nike John
nike John
nike John

However, because the query lists the company 'nike' 3 times it takes 3 clicks to go to the next record on the main form. I would like set this search up so the results returned allow me to move to the next company with one click of the next record button.

I hope this makes sense.

Edited by: reptar on Sun Dec 14 10:26:06 EST 2008.

Hi

I dont think you are understanding my exlanation If you have a MainForm/SubForm setup

Then when you select a Company ie Nike from the Main Form then ALL records related to this company will be displayed.

In your actual database how many Contacts does 1 Company have?

From my experience you would normally set it up that A Company has 1 Main Contact is this NOT how you have it?

If you have Multiple Contacts then why not set up your Forms so that they are in a Linked Form setup

ie Select the Company and then click a Button on this form to Open another Form to display the Contas associated with the Company - Then search for the Contact

Can you upload your DB?

Mike



Edited by: mike60smart on Sun Dec 14 10:39:20 EST 2008.