Cascading Combo Box and Subform Issues

[RossIV]

Background:
I have a web database in Access 2010 that is based off of the desktop product inventory database template that you can download from within Access.
I have one form to create a purchase order that pulls a list of suppliers from the 'Suppliers' table, product information from the 'Products' table and manufacturer information from the 'Manufacturers' table. What I am trying to accomplish is when adding a line item (product to purchase), you choose the supplier at the top of the main form which limits the manufacturers in the subform that the supplier has which then limits the available part numbers in the last box. I have attached a screenshot to make this more clear.
Problem:
By default in the template, you can't select a Manufacturer or anything in the subform until you choose a supplier. The fields are locked through an AfterUpdate expression in the suppliers combo box. Once I choose the supplier, the subform unlocks. I can choose any manufacturer since right now they are all from the same supplier. The problem is that the products combo box yields zero results regardless of manufacturer selection. Also, the strange thing is that if I open the subform standalone, the limiting process works just fine.

How can I make it so that the manufacturer combo box is limited by the supplier combo box, and the products combo box is limited by the manufacturer combo box?

Please let me know if I need to explain something else or if you need more information. Thanks in advance for any help!

Combo Box Details:

Supplier Combo Box: (Main Form)
Control source - 'SupplierID'
Name - 'cboSupplierID'
Row Source - 'SELECT Suppliers.ID, Suppliers.Company FROM Suppliers ORDER BY Suppliers.Company;'
After Update - Long, but can provide if needed

Manufacturer Combo Box: (Sub Form)
Control source - 'ManufacturerName'
Name - 'cboMFGID'
Row Source - 'SELECT Manufacturers.ID, Manufacturers.Company FROM Manufacturers ORDER BY Manufacturers.Company;'
After Update - Requery cboProductID

Product Combo Box: (Sub Form)
Control source - 'ProductID'
Name - 'cboProductID'
Row Source: 'SELECT Products.ID, Products.ProductName FROM Products WHERE [SupplierID]=[Forms]![PurchaseOrderDetail]![cboSupplierID] AND (((Products.ManufacturerName)=[Forms]![PurchaseOrderLineItems]![cboMFGID]))'


After Update - ​Long, but can provide if needed

[Dal Jeanis]

1) In the product combo box Row Source, are you really comparing the cboMFGID to the Manufacturer Name?
2) If you are locking cboMFGID for entry before supplier is selected, why aren't you limiting the Row Source of cboMFGID to the selected supplier? (Or are you appllying a filter in the AfterUpdate of cboSupplierID?

[RossIV]

1) In the product combo box Row Source, are you really comparing the cboMFGID to the Manufacturer Name?
2) If you are locking cboMFGID for entry before supplier is selected, why aren't you limiting the Row Source of cboMFGID to the selected supplier? (Or are you appllying a filter in the AfterUpdate of cboSupplierID?

1. Isn't that what I should be doing? Everything works fine when I'm not on the new purchase order form. If I pull up the line item subform standalone, everything works fine.

2. That's what I haven't done yet. I have not limited the row source of cboMFGID yet. I guess I'm not sure how to do that unless it's the same process as limiting the product by manufacturer.

[Dal Jeanis]

1) I'm just trying to check your connections. I meant, is the cboMFGID bound to a key to the Manufacturer, or is it bound to the ManufacturerName? What is actually on the Product Table, the Manufacturer's ID, or their Name?

2) Depends on the table structure. If the SupplierID is found on the manufacturer table, then it's just as easy. If not, then you'll need to code a query that limits the manufacturer based on the manufacturers of the products found in the products table, which is sort of backwards. You've gotten far enough already that you'll figure it out easy enough.

ROFL. Got it. When the subform is standalone, it's in the Forms! collection. When it's a subform, it's NOT a form. It's a control on the parent form. That's why the reference isn't being found.

Let me see. Here's the reference that explains how to refer to subforms and forms and such: http://access.mvps.org/access/forms/frm0031.htm

[RossIV]

Sorry - I just got back to work today and just got access back to the DB.

1. cboMFGID is bound to the ManufacurerName I believe. On the product table the name of the Manufacturer shows up.

2. SupplierID is not found in the manufacturer table and vice/versa.

I'll take a look at that link and see if I can make it work.

Thanks!

Edit: I'm not sure if I mentioned this before but all of the manufacturers are listed in the Manufacturers table and each manufacturer has at least one product. We enter the manufacturers as we have new products with new manufacturers.

Edit 2: I think the issue with not being able to link the Manufacturer drop down to the Supplier drop down is that there isn't a relationship between the two tables.

[RossIV]

Bump...Anyone have any ideas? I really would love to get this working.

[Dal Jeanis]

To repeat myself:

ROFL. Got it. When the subform is standalone, it's in the Forms! collection. When it's a subform, it's NOT a form. It's a control on the parent form. That's why the reference isn't being found.

Let me see. Here's the reference that explains how to refer to subforms and forms and such: http://access.mvps.org/access/forms/frm0031.htm

[RossIV]

The issue isn't a reference issue. I just realized I didn't post an update as to what I do have working now. My apologies.

I now have it working where when you choose a supplier then you can choose a product that the supplier carries but you can't choose a manufacturer first. The list appears, but I can't click on anything. If I choose a product, though, the Manufacturer Name field populates with the corresponding manufacturer.
I still need to be able to select the manufacturer first, then limit the products by that.

[Dal Jeanis]

Sounds like you have the hierarchy backwards.

I tell you what. Pretend your next post is the start of a new thread. Give us all the information on the current situation, pretending we've never heard it before. Just cut your initial post, rework it with anything that has changed, and repost it. Please do post the code for the afterupdate, in CODE tags with [] around them. Or, alternately, post a copy of your database with any sensitive information removed.

[RossIV]

Starting fresh...I can't post the DB because it's 40+ MB at this point. And there's a lot of sensitive info in there too. If I try to copy/paste the AfterUpdate directly, it gives it to me in XML format. I've attached screenshots. If you want, I can post the XML.

Background:
I have a web database in Access 2010 that is based off of the desktop product inventory database template that you can download from within Access.
I have one form to create a purchase order that pulls a list of suppliers from the 'Suppliers' table, manufacturer information from the 'Manufacturers
table, and product information from the 'Products' table.

Goal:
What I am trying to accomplish is when adding a line item (product to purchase), you choose the supplier at the top of the main form which limits the manufacturers in the subform that the supplier has which then limits the available part numbers in the last box. I have attached a screenshot to make this more clear.

Issues:
One of the issues is that there isn't a manufacturer field in the suppliers database, so there isn't a direct relation between Mfg. and Supplier. The only way to get a relationship would be to pull the manufacturers associated with a product from a supplier in the Products database.
By default in the template, you can't select anything in the subform until you choose a supplier in the main form. The fields are locked through an AfterUpdate expression in the suppliers combo box. Once I choose the supplier, the subform unlocks (well, most of it). For some reason, after choosing a supplier, I can choose a product that the supplier carries but I can't choose a manufacturer first. The list of manufacturers appears, but I can't select anything. If I choose a product, though, the Manufacturer Name field populates with the corresponding manufacturer.

Combo Box Details:
Supplier Combo Box: (Main Form)
Control source - 'SupplierID'
Name - 'cboSupplierID'
Row Source:

SELECT Suppliers.ID, Suppliers.Company FROM Suppliers ORDER BY Suppliers.Company

After Update - See attached "cboSupplierID-AfterUpdate1.png" "cboSupplierID-AfterUpdate2.png"

Manufacturer Combo Box: (Sub Form)
Control source - 'ManufacturerName'
Name - 'cboMFGID'
Row Source

SELECT [Manufacturers].[ID], [Manufacturers].[Company] FROM Manufacturers ORDER BY [Company];

Product Combo Box: (Sub Form)
Control source - 'ProductID'
Name - 'cboProductID'
Row Source:

SELECT Products.ID, Products.ProductName FROM Products WHERE [SupplierID]=[Forms]![PurchaseOrderDetail]![cboSupplierID] ORDER BY Products.ProductName;

After Update - See attached image "cboProductID-AfterUpdate.png"

[Dal Jeanis]

Oh, gak, macros. Okay, I'll look at it this evening.

[RossIV]

Cool. I appreciate your help! Sorry if I make things a bit confusing.
Looking forward to your response.

[Dal Jeanis]

Sorry, web databases and data macros are off my beaten trail.

If choosing the product is populating the manufacturer, then one of two things is happening - either changing the product is forcing resolution of the manufacturer due to the query structure, or something magical is happening in macro GetStandardCost. I doubt it's the latter, since we can see the assignment for the standard cost, and they'd use magic for that if they used it for anything.

So, try adding a second manufacturer that supplies the same product, and see if manufacturer still autopopulates. If not, then you've found the magic, at least.

I don't see an AfterUpdate for the cboMFG.

Unfortunately, this macro architecture is unfamiliar to me, so I can't tell you anything certain. This would be a learning experience for me. I'd make sure I understood the [LocalVars], [ReturnVars] and [TempVars] collections, what their limitations might be in usage. Can they be referenced in SQL, or do they have to be passed to the macro or query? If so, how many parameters can you pass. and how?

I'd probably be trying to change that ProductID select statement to use both the supplier and manufacturer IDs. And I'd be setting up an afterupdate on the manufacturer to limit/requery the productid.

When supplier ID changes - as currently, but limit the manufacturer control and reset the productid control.
When manufacturer changes - limit the productid control

That'd be my direction of flailing. Hopefully, there's someone on with experience in the web flavor of Access.

[RossIV]

Acknowledged. I posted on another forum and got another similar response - not much experience with web databases. I'll keep fiddling and see if I can get it to work.
Thanks again!