Cascading Combo Problem (1 Viewer)

[Bob M]

I am totaly perplexed. I have a table which among other fields has two. One is ProductID the other is SupplierID. Each product is supplied from a specific supplier. I have a form with a combo box names SupplierID. The rowsource for thois combo box is:

SELECT distinctrow Suppliers.SupplierID, Suppliers.SupplierName from Suppliers ORDER BY Suppliers.SupplierName.

I also have a subform with a combo ProductID with a rowsource

SELECT Products.ProductID, Products.VendorProductNo, Products.ProductDescription, Products.UnitPrice, Products.SupplierID WHERE Products.SupplierID = Forms![form].SupplierID ORDER BY Products.ProductDescription.

I am trying to restrict the number of Products that can be selected for new entries to those supplied in the past by this supplier.

When I view the main form and records from the subform everything appears ok except for the productID combo box where nothing appears except for records added after I populated the SupplierID field in the products table. When I take out the WHERE keyword all the fields including the ProductID display fine.

Like I said, I totally perplexed.

HELP!!!

 

[Jack Cowley]

You need to correctly refer to your main form from the combo box in the subform. This site will give you the correct syntax...

hth,
Jack

 

[Bob M]

Cascading Combo problem

I tried referencing the parent form's combo box as set forth in the web site you gave me. I have the following recordsource:

SELECT Products.ProductID, Products.VendorProductNo, Products.ProductDescription, Products.UnitPrice, ProductsSupplierJoin.SupplierID from Products INNER JOIN ProductSupplierJoin.ProductID on Products.ProductID = ProductsSupplierJoin.ProductID WHERE ProductSupplierJoin.SupplierID = Me.Parent!SupplierID

and I get the following message box:

Enter a Parameter value
Me.Parent!SupplierID

The Join table contains all supplier id's and their product ids.

I'm going to lunch. I need a break!

 

[Pat Hartman]

The syntax that you are using is only correct in VBA WITHIN a form's class module. SQL is not VBA and it is not within a form, therefore you need to use an external type syntax.

WHERE ProductSupplierJoin.SupplierID = Forms!YourMainform!SupplierID;

 

[Bob M]

Combo Box Problem

I corrected the syntax but still don't see anything in the ProductID combo box on the subform. The strange thing is if I add a new record on the subform and then go back to view it using the same query I see the vendor product number no problem.

 

[Jack Cowley]

Somewhere along the line you need to requery the combo box after you add a record. Search help for the Requery method for more information on this....

hth,
Jack

 

[KevinM]

Why do it the hard way?

Let Access help.

There's no need to manually type/correct the SQL syntax.

Open the rowsource for the Product combo and add the relevant fields and form reference to the Supplier in the query grid.

Much easier.

 

[Bob M]

Thanks people, but I've already tried both. The problem is not when I add a new record. It's when I view existing records. This gets better still.

I another path into the same form filtered through a form that allows the user to select a supplier then look at open and closed POs. Everything looks fine when the form is opened that way. I also added a way in where the user can just view any PO they like and add also. When the form is opened that way, the problem resurfaces.

My conclusion and I could be wrong. It looks like the problem is related to how the form is opened.

 

[KevinM]

In the AfterUpdate Event of the Supplier combo you should have something like this....

Forms!MainForm!Subform!ProductCombo.Requery

 

[Bob M]

Sorry Kevin, but I've already tried that as well. Funny thing is that I can see the Product Descriptions for only one supplier. I've looked at the records of that supplier vs other and can't see any difference.

I'll keep plugging, there has to be an answer to this.