title: Populating data in multiple cascading dropdown boxes in Access 2007
body: <p>I've been assigned the task to design a temporary customer tracking system in MS Access 2007 (sheeeesh!). The tables and relationships have all been setup successfully.  But I'm running into a minor problem while trying to design the data entry form for one table... Here's a bit of explanation first.</p>
  
  <p>The screen contains 3 dropdown boxes (apart from other fields).</p>
  
  <p><strong>1st dropdown</strong></p>
  
  <p>The first dropdown <strong>(cboMarket)</strong> represents the <strong>Market</strong> lets users select between 2 options:</p>
  
  <ul>
  <li>Domestic</li>
  <li>International</li>
  </ul>
  
  <p>Since the first dropdown contains only 2 items I didn't bother making a table for it. I added them as pre-defined list items.</p>
  
  <p><strong>2nd dropdown</strong></p>
  
  <p>Once the user makes a selection in this one, the second dropdown <strong>(cboLeadCategory)</strong> loads up a list of Lead Categories, namely, Fairs &amp; Exhibitions, Agents, Press Ads, Online Ads etc. Different sets of lead categories are utilized for the 2 markets. Hence this box is dependent on the 1st one.</p>
  
  <p>Structure of the bound table, named <strong>Lead_Cateogries</strong> for the 2nd combo is:</p>
  
  <pre><code>ID Autonumber
  Lead_Type TEXT &lt;- actually a list that takes up Domestic or International
  Lead_Category_Name TEXT
  </code></pre>
  
  <p><strong>3rd dropdown</strong></p>
  
  <p>And based on the choice of category in the 2nd one, the third one <strong>(cboLeadSource)</strong> is supposed to display a pre-defined set of lead sources belonging to the particular category.</p>
  
  <p>Table is named <strong>Lead_Sources</strong> and the structure is:</p>
  
  <pre><code>ID Autonumber
  Lead_Category NUMBER &lt;- related to ID of Lead Categories table
  Lead_Source TEXT
  </code></pre>
  
  <p>When I make the selection in the 1st dropdown, the <strong>AfterUpdate</strong> event of the combo is called, which instructs the 2nd dropdown to load contents:</p>
  
  <pre><code>Private Sub cboMarket_AfterUpdate()
      Me![cboLead_Category].Requery
  End Sub
  </code></pre>
  
  <p>The <strong>Row Source</strong> of the 2nd combo contains a query:</p>
  
  <pre><code>SELECT Lead_Categories.ID, Lead_Categories.Lead_Category_Name
  FROM Lead_Categories
  WHERE Lead_Categories.Lead_Type=[cboMarket]
  ORDER BY Lead_Categories.Lead_Category_Name;
  </code></pre>
  
  <p>The AfterUpdate event of 2nd combo is:</p>
  
  <pre><code>Private Sub cboLeadCategory_AfterUpdate()
      Me![cboLeadSource].Requery
  End Sub
  </code></pre>
  
  <p>The <strong>Row Source</strong> of 3rd combo contains:</p>
  
  <pre><code>SELECT Leads_Sources.ID, Leads_Sources.Lead_Source
  FROM Leads_Sources
  WHERE [Lead_Sources].[Lead_Category]=[Lead_Categories].[ID]
  ORDER BY Leads_Sources.Lead_Source;
  </code></pre>
  
  <p><strong>Problem</strong></p>
  
  <p>When I select Market type from cboMarket, the 2nd combo cboLeadCategory loads up the appropriate Categories without a hitch.</p>
  
  <p>But when I select a particular Category from it, instead of the 3rd combo loading the lead source names, a modal dialog is displayed asking me to <strong>Enter a Parameter</strong>.</p>
  
  <p><a href="http://img163.imageshack.us/img163/184/enterparamprompt.png" rel="nofollow noreferrer">alt text http://img163.imageshack.us/img163/184/enterparamprompt.png</a></p>
  
  <p>When I enter anything into this prompt (valid or invalid data), I get yet another prompt:</p>
  
  <p><a href="http://img52.imageshack.us/img52/8065/enterparamprompt2.png" rel="nofollow noreferrer">alt text http://img52.imageshack.us/img52/8065/enterparamprompt2.png</a></p>
  
  <p>Why is this happening? Why isn't the 3rd box loading the source names as desired. Can any one please shed some light on where I am going wrong?</p>
  
  <p>Thanks,
  m^e</p>
  
  <p>===================================================</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I found a glitch in the query for the 3rd combo.. It wasn't matching up with the value of the second combo. I fixed it and now the query stands at:</p>
  
  <pre><code>SELECT Leads_Sources.ID, Leads_Sources.Lead_Source
  FROM Leads_Sources
  WHERE (((Leads_Sources.Lead_Category)=[cboLead_Category]))
  ORDER BY Leads_Sources.Lead_Source;
  </code></pre>
  
  <p>Those nasty Enter Param prompts are GONE!!! However, the 3rd combo still stubbornly refuses to load any values. Any ideas?</p>