Dec 14 2008, 09:23 AM
Hi, i have two tables:
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,
Dec 14 2008, 09:43 AM
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
Dec 14 2008, 10:19 AM
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
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.
Dec 14 2008, 10:36 AM
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?
Edited by: mike60smart on Sun Dec 14 10:39:20 EST 2008.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here