Form/Subform - Update control help!

[f_ali]

Hello all,
I'm in the process of creating an inventory database and have encountered a significant road block.
I have a form/subform problem where I have a combo box called "Supplier" on the main form, and I have products (another combobox) in the subform.
I'm trying to get ONLY the products of the selected supplier (in the main form) to show in the combobox of products in the subform.
I was able to accomplish this (update products based on supplier), within the same form, but cannot accomplish this when dealing with a form/subform.


I'm not too familiar with VB or SQL code, so please help solve my problem step by step !!!
Thanks so much.

[pe_z]

Hello all,
I'm in the process of creating an inventory database and have encountered a significant road block.
I have a form/subform problem where I have a combo box called "Supplier" on the main form, and I have products (another combobox) in the subform.
I'm trying to get ONLY the products of the selected supplier (in the main form) to show in the combobox of products in the subform.
I was able to accomplish this (update products based on supplier), within the same form, but cannot accomplish this when dealing with a form/subform.
I'm not too familiar with VB or SQL code, so please help solve my problem step by step !!!
Thanks so much.

Any ideas how to get it done please, I have the same problem.

[f_ali]

You need to have the Row Source of the subform's combo box be a query that uses the main form's combo box as a criterion value:

SELECT * FROM TableName WHERE Fieldname = Forms!MainFormName!ComboBoxName

where MainFormName is the name of the main form, and ComboBoxName is the name of the combo box control on the main form.

Then, in the AfterUpdate event of the main form's combo box, you should run simple VBA code that requeries the subform's combo box:

Private Sub ComboBoxName_AfterUpdate()
Me!SubformName!SubFormComboBoxName.Requery
End Sub

where SubformName is the name of the subform control (the control that holds the subform object) and ComboBoxName is the name of the combo box in the subform


*Thanks to Ken (MS ACCESS MVP, UA VIP ) - the source of this answer