Populating data in multiple cascading dropdown boxes in Access 2007

Question

Hello all, 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.

The screen contains 3 dropdown boxes (apart from other fields).

1st dropdown

The first dropdown (cboMarket) represents the Market lets users select between 2 options:

• Domestic
• International

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.

2nd dropdown

Once the user makes a selection in this one, the second dropdown (cboLeadCategory) loads up a list of Lead Categories, namely, Fairs & 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.

Structure of the bound table, named Lead_Cateogries for the 2nd combo is:

ID Autonumber
Lead_Type TEXT <- actually a list that takes up Domestic or International
Lead_Category_Name TEXT

3rd dropdown

And based on the choice of category in the 2nd one, the third one (cboLeadSource) is supposed to display a pre-defined set of lead sources belonging to the particular category.

Table is named Lead_Sources and the structure is:

ID Autonumber
Lead_Category NUMBER <- related to ID of Lead Categories table
Lead_Source TEXT

When I make the selection in the 1st dropdown, the AfterUpdate event of the combo is called, which instructs the 2nd dropdown to load contents:

Private Sub cboMarket_AfterUpdate()
    Me![cboLead_Category].Requery
End Sub

The Row Source of the 2nd combo contains a query:

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;

The AfterUpdate event of 2nd combo is:

Private Sub cboLeadCategory_AfterUpdate()
    Me![cboLeadSource].Requery
End Sub

The Row Source of 3rd combo contains:

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;

Problem

When I select Market type from cboMarket, the 2nd combo cboLeadCategory loads up the appropriate Categories without a hitch.

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 Enter a Parameter.

When I enter anything into this prompt (valid or invalid data), I get yet another prompt:

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?

Thanks, m^e

===================================================

UPDATE

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:

SELECT Leads_Sources.ID, Leads_Sources.Lead_Source
FROM Leads_Sources
WHERE (((Leads_Sources.Lead_Category)=[cboLead_Category]))
ORDER BY Leads_Sources.Lead_Source;

Those nasty Enter Param prompts are GONE!!! However, the 3rd combo still stubbornly refuses to load any values. Any ideas?

Answer 1

Never mind. Found the fix. The BoundColumn property of the second combo wasn't set to the correct column. Hence the selection values in it were incorrect and the 3rd combo wasn't able to refer to the linked table properly (with the correct index).

Job done :)

Thanks to all who may have taken time out to review the problem.