Combo Box not updating in nested subform

[tomabil]

Hello,

I'm new to this forum and having googled this problem for hours i give up and post my problem here to see if any of you experts can help me out here. This is the problem:

I have a main form with a combo box where Cathegorys are named and a region is specified for the particular cathegory. In a subform to Cathegory Items are specified, and finally the Items subform has a subform where several RegionalSettings can be selected for each item.

Here's the catch: the RegionalSettings combo must be filtered according to which region is specified for the current Cathegory. The RowSource is specified like this:

SELECT tblRegionalSetting.ID, tblRegionalSetting.Setting
FROM tblRegionalSetting
WHERE (((tblRegionalSetting.RegionId)=[Forms]![tblCathegory]![cboRegion]))
ORDER BY tblRegionalSetting.Setting;

This seems fine looking at the first few records, but when I use the standard record browser to move to the cathegory with a different region, the combo isn't updated to show allowable regionalSettings for items of that cathegory. This is despite have a requery in the main form's Current event such as:

Private Sub Form_Current()
Form_tblItemSetting_subform.cboRegionalSetting.Requery
End Sub

My final observation is that if I move the combo to either the cathegory main form or the item sub form the combo updates fine (with proper alterations to the code ofc). The problem seems to occur when the combo is nested to a third level.

I've attached a database wihich describes the problem, any ideas?

 

[thhui]

Could you post a mdb 2000 format database for my testing?

 

[JANR]

put this in the After_Update event of cboregion and Not in the On_Current event

Private Sub cboRegion_AfterUpdate()
Me!tblItem_subform.Form!tblItemSetting_subform.Form!cboRegionalSetting.Requery
End Sub

Also change the View property of tblItem_subform from DataSheet to SingleView

JR

 

[tomabil]

put this in the After_Update event of cboregion and Not in the On_Current event

Private Sub cboRegion_AfterUpdate()
Me!tblItem_subform.Form!tblItemSetting_subform.Form!cboRegionalSetting.Requery
End Sub

Also change the View property of tblItem_subform from DataSheet to SingleView

JR

Thank you jR!

I'll put the requery in cboRegion_AfterUpdate() as you suggested but will keep it in Form_Current() too since otherwise it doesn't seem to update properly when browsing records. Had to change tblItem_subform to SingleView as well since it doesn't seem to recognize the reference Me!tblItem_subform.Form!tblItemSetting_subform.Form!cboRegionalSetting when it is put in the Form_Current() event unless View property is SingleView instead of DataSheet.

Many thanks!

/Tomas