Multiple Synchronized / Cascading Comb Boxes

[roee12031]

Greetings,

I really hope someone can help me with creating combo boxes based on the previous combo box choice. I have have managed to get combo box 2 to be based on combo box 1 but for some reason combo box 3 will not populate with anything. I have added the afterupdate to combo box 2 and placed the SELECT statement to include the previous form! info. I have scoured the web and spent hours trying to fix this. Please help. thanks!

Table Info (check pictures for more info):
Supplier: ID, Company, POC, Email, ETC
Products: Product ID, Supplier, Product Code, Product Name, Description

Combo Box 1:

Name: Supplier
Column Count: 2
Column Width: 0";1"
Row source Type: Table/Query
Row Source: SELECT [ID], [Company] FROM Suppliers ORDER BY [Company];
Event:

Private Sub Supplier_AfterUpdate()
Me.Product_Code = Null
Me.Product_Code.Requery
Me.Product_Code = Me.Product_Code.ItemData(0)
End Sub

Combo Box 2:

Name: Product Code
Column Count: 2
Column Width: 0";1"
Row source Type: Table/Query
Row Source: SELECT Products.[Product ID], Products.[Product Code] FROM Products INNER JOIN Suppliers ON Products.Supplier=Suppliers.ID WHERE (((Suppliers.ID)=[Forms]![Inventory Details]![Supplier])) ORDER BY Products.[Product Code];
Event:
Private Sub Product_Code_AfterUpdate()
Me.ProductName = Null
Me.ProductName.Requery
Me.ProductName = Me.ProductName.ItemData(1)
End Sub

Combo Box 3:

Name: ProductName
Column Count: 2
Column Width: 0";1"
Row source Type: Table/Query
Row Source: SELECT Products.[Product ID], Products.[Product Name], Products.[Product Code] FROM Products WHERE (((Products.[Product Code])=[Forms]![Inventory Details]![Product Code])) ORDER BY Products.[Product Name];
Event:
NOTHING

I hope this helps!!! If not , I have a link to my actual database. I have no idea why its 9MB with very few records (too large to upload here) .

www DOT mediafire DOT com/?n47wzpwj5cu5e79

 

[delikedi]

You intend to provide product code to combo3 but pass the product ID instead. combo2 is storing product ID (I'm assuming the bound column property of combo2 is 1). Therefore combo3 is looking for a product whose product code is the same as its product ID, which appearently does not exist. You can test this theory by adding a dummy record whose product ID and product code are the same. If this theory is true, then there are sevaral easy solutions.

 

[delikedi]

I guess probably the easiest solution would be to change the bound column property of combo2 to 2.

 

[roee12031]

Thanks delikedi!! Now that i think about it.. its soo simple to realize what the problem is. Now the only problem of changing the bound column is that if i change it to bound column 2, the form will attempt to pass a string of text into a number field. I think my easiest and most logical solution is to change the 3rd combo box to an autofil text box. If i think about it, there will not be more than one item with that product code. So now i have to think of a way to have that text box just automatically fill with the product name of the product code called from combo box 2. Any ideas. Ill start the process. thanks for the solution or in this case what was causing the issue.!

V/R,

Roel

 

[roee12031]

Delikeldi,

Quick update.. rather than changing everything.. I just made my SELECT statement for combo box 3 to equal the product ID instead of the product code since combo box 2 was passing the product ID. Everything is perfect. thanks