Combo Box that displays choices based on another box in form 1

[cursillo]

Hi:<br><br>I'm putting together a relatively simple database, but want the forms to be as rigidly controlled as possible.&nbsp;&nbsp;Thus, I am using liberal combo boxes to allow almost all entries to be via pull-down boxes.&nbsp;&nbsp;As an example of my problem, I have a table of distributors of our product that includes company name and address.&nbsp;&nbsp;In another table, I have the company name and the individual contacts at each location.&nbsp;&nbsp;On my form, one field prompts the user to select a distributor from the pull-down menu.&nbsp;&nbsp;No problem.&nbsp;&nbsp;The next field is for the particular contact at that distributor.&nbsp;&nbsp;I only want the pull-down menu to include contacts that work at that particular distributer, not every contact at every distributer.&nbsp;&nbsp;This type of &quot;Combo box filtering&quot; will be used extensively in other scenarios on the form.&nbsp;&nbsp;I'm extremely well versed in Excel and an educated novice in Access.&nbsp;&nbsp;I have made pivot tables that perform something similar in Excel, but figured there was a way to use more finesse in Access.&nbsp;&nbsp;Any help would be great.<br><br>Thanks,&nbsp;&nbsp;cursillo

 

[DougP]

There is.<br>Pass the paramter from the one combo box to the SQL statement of the other combo box.<br>I HOPE you have a Distributor ID field in each table.<br>It will make this a no brainer.<br>So each Combo box has the Company ID field in it.<br>So If you pick &quot;Company XYZ&quot; whose ID is 123 from Combo0. then the Contacts for &quot;Company XYZ&quot; are in the Contacts table as 123 <br>Here is a SQL for my sample<br>SELECT DISTINCTROW Contacts.ContactsID, Contacts.CompanyID, Contacts.FirstName, Contacts.LastName FROM Contacts WHERE (((Contacts.CompanyID)=[Forms]![Form2]![Combo0]));<br><br>Also you have to put a requery statement in the &quot;After_Update&quot; Event of the Combo0 which will refresh the list each time a different Company is choosen.<br><br>Private Sub Combo0_AfterUpdate()<br>&nbsp;&nbsp;&nbsp;&nbsp;Combo2.Requery&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; just this line<br>End Sub<br><br><br>OK<br><br><br>