combox box based on another combo box 1

[molly]

I am using a subform on a tab. I need to have a second combo box base itself off a 1st combo box. I tried following the whitepaper FAQ702-4289 but still have a problem.

Rather than explain, I offer an attached minuture file.

I am close.
thanks
Molly

 

[PHV]

You only need this in the form's class module:

Private Sub cboCategoryID_AfterUpdate()
Me!cboProductID.RowSource = "SELECT ProductID,CategoryID,ProductName FROM tblProduct WHERE CategoryID=" & Me!cboCategoryID
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[molly]

Here is a summary of my successful a dependent combo boxe on the Same form. Thanks PHV for the help. Molly
==================

PURPOSE: Have a dependent 2nd combo box show only the products that pertain to the 1st combo box selection. They are both on the same subform.

In frmMyProducts:

1 - In the #1 combox box field Name cboCategoryID with a Control Source of CategoryID:

Row Source :
SELECT tblCategory.CategoryID, tblCategory.CategoryName FROM tblCategory;

After Update Event:
Private Sub cboCategoryID_AfterUpdate()
Me!cboProductID.RowSource = "SELECT ProductID,CategoryID,ProductName FROM tblProduct WHERE CategoryID=" & Me!cboCategoryID
End Sub


2 - In the #2 combox box field Name cboProductID with a Control Source of ProductID:

Row Source:
SELECT tblProduct.ProductID, tblProduct.CategoryID, tblProduct.ProductName FROM tblProduct WHERE (((tblProduct.CategoryID)=Forms!frmMain!cboCategoryid));

After Update Event:
Private Sub cboProductID_AfterUpdate()
cboCategoryID.Requery
End Sub

3 - The tblCategory has 2 fields: CategoryID and CategoryName

4 - The tblProduct has 2 fields: ProductID, ProductName, CategoryID

5 - The junction table tblMyChosenProducts has 2 fields: CategoryID and ProductID

 

[PHV]

Why requery the category combo in the after update event procedure of cboCategoryID ????

 

[molly]

PHV - You are right. I just took the following out and all still works. I guess i was trying to follow that FAQ sheet but got myself lost.

Private Sub cboProductID_AfterUpdate()
'cboCategoryID.Requery <<<<< i now took this all out
End Sub

thanks PHV so much. You are making this simplier.

Molly

 

[molly]

PHV - i made the fix as you said and put version #3 file just in case you or someone wants to review or learn from your work on this.

I wonder if I need any Got Focus commands ?

Molly

 

[molly]

I see that I have one more problem. If I later return to the cboProductID, I cannot simply change the existing value. I get no choices. It seems that i need to re-pick the cboCategoryID and then re-pick a cblProductID. But the cboCategoryID was fine and i only wanted to change the cboProductID.

Per your thought, i have taken out the:
Private Sub cboProductID_AfterUpdate()
'cboCategoryID.Requery <<<<< i now took this all out
End Sub

But even if i put it back, the problem still exists in trying to change an existing cboProductID value.

Can you help? Molly