Searching subforms

[jhazard]

I have a form based on a table (tblCompanyName) with a subform embedded in it based on another table (tblContacts).

When the user clicks my search button they can search on criteria based on tblCompany name and the result is fine, my subform displays related data.

However, if the user wants to search on criteria from tblContacts I run the query below and it brings back the correct amount of records in teh main form but none of the related records are displayed in the subform. How can this be done?

"SELECT * FROM tblcompanyname INNER JOIN tblcontacts ON CompanyID = CompanyID WHERE (((CompanyID)=[companyid]) AND (([CustomerName]) Like '*" & DoubleQuote(Me![cboSelect]) & "*'))"

 

[Jack Cowley]

Your contact table should have the CompanyID as the Foreign key. Create a combo box using the Wizard and on the first screen select the 3rd item, "Find a record..." Base the combo on the Contact Table and have the CompanyID as the hidden column. Now the user will see the contacts in this combo box and the combo box will 'select' the CompanyID so you will see the Company in the main form and the subform will have the correct Contact displayed. I hope my explanation made sense!

Jack

 

[jhazard]

Your contact table should have the CompanyID as the Foreign key. Create a combo box using the Wizard and on the first screen select the 3rd item, "Find a record..." Base the combo on the Contact Table and have the CompanyID as the hidden column. Now the user will see the contacts in this combo box and the combo box will 'select' the CompanyID so you will see the Company in the main form and the subform will have the correct Contact displayed. I hope my explanation made sense!

Jack

 

[jhazard]

Hi

My contact table does have the companyid as the foreign. I followed your instructions to put a combo on using the wizard but I dont get the option 'find a record' - the first screen i get is asking me if i want the combo to look up the values or have them typed in. I'm using access 2000. I fell at the first hurdle

 

[jhazard]

OK realised what I was doing wrong (my search form is seperate so I added the combo to that). However adding the combo to my subform now gives me that option. I followed the instructions through and I now have a combo that looks up customername for me but when i select one nothing happens..... (i.e. no records are brought back).

 

[Jack Cowley]

Jenny -

Let's make sure I understand exactly what you have. I am assuming a single form that has the Company data as the main form with Contacts being a subform. I also assume you have a combo box on this form that you use to select a Company and when you do the subform shows Contacts that are related to that Company. If I am correct so far then what you want to do is add another combo box that is based on your Contacts so when you select a Contact name the proper Company will be displayed. To do this follow these instructions:

1. Create your combo and select the first item on the first screen. Select your Contacts table and Select CompanyID as the first column then LastName or whatever other information you want to show. Save the combo box on the main form. Open it to be sure that you are getting the data you want.

2. Open your form in Design mode and open the property sheet for your Company combo box. Select the Events tab and then click on the After Update event. You will see a button to the right with 3 dots. Click on that and a code page will open and you will see code similar to this (depending on your version of Access):

Me.RecordsetClone.FindFirst "[CompanyID] = " & Me![ComboBoxName]
Me.Bookmark = Me.RecordsetClone.Bookmark

3. Copy this code then open the property sheet for your new combo. Select [Event Procedure] for the After Update event and open the code page by clicking the button with 3 dots. Paste the code between the two lines of code you will find ther. Change the name of the combo box to the name of your Contracts combo box. Close and save....

Now if you select a Contact from the Contact combo the main form will move to the Company that has that Contact and you will see her/him in the subform along with any other contacts for that Company.

If this is not the setup you have then give me more details about the "search form" and I will try to help you solve this problem.

Jack