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