Populating data in multiple cascading dropdown lists in Access 2007

I am assigned a task to create a temporary customer tracking system in MS Access 2007 (sheeeesh!). The tables and relationships were configured successfully. But I am facing a minor issue when trying to create a data entry form for one table ... Let's explain a bit first.

The screen contains 3 drop-down lists (besides other fields).

1st dropdown

The first dropdown (cboMarket) represents the Marketplace allowing users to choose between two options:

• Internal
• International

Since the first dropdown only contains 2 items, I didn't bother writing a table for it. I added them as predefined list items.

2nd dropdown

As soon as the user makes a choice in this, the second dropdown list (cboLeadCategory) loads the list of the top categories, namely fairs and exhibitions, agents, advertisements, internet classifieds, etc. sets of two categories are used for two markets. Therefore, this field depends on the former.

The structure of the linked table named Lead_Cateogries for the second 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 second, the third (cboLeadSource) should display a predefined set of source sources belonging to a specific category.

The table is called Lead_Sources , and the structure is:

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

      

        
        
        
      

    

When I make a selection in the first dropdown, the Combo's AfterUpdate event is fired , which instructs the second dropdown to load the content:

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

      

        
        
        
      

    

The line source of the second combo contains the request:

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;

      

        
        
        
      

    

Second Combo AfterUpdate Event:

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

      

        
        
        
      

    

3rd combo line source 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 the Market type from cboMarket, the second combo-cboLeadCategory loads the corresponding categories without a hitch.

But when I select a specific category from it, instead of the 3rd combo load of the original source names, a modal dialog is displayed asking for Enter Parameter .

alt text http://img163.imageshack.us/img163/184/enterparamprompt.png

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

alt text http://img52.imageshack.us/img52/8065/enterparamprompt2.png

Why is this happening? Why not the third box downloads the source names at will. Can anyone shed some light on where I am going wrong?

Thank you, t ^ e

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

UPDATE

I found a glitch in the request for the 3rd combo. It didn't match the value of the second combo. I fixed it and now the request is:

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

      

        
        
        
      

    

These nasty hints Enter Param GONE !!! However, the third combo still stubbornly refuses to load any value. Any ideas?