VBA WHERE code in Combo box not working when using Subform

[HelenWy]

Hi there

I'm using a subform which has 3 combo boxes in it - the 2nd one is dependent on what you pick from the first, and the 3rd is dependent on what is picked from the 2nd. I have code in the "on change" event procedure as follows:

Option Compare Database
 
Private Sub cboDivision_Change()
Me.cboArea = Null
Me.cboArea.Requery
End Sub
 
Private Sub cboArea_Change()
Me.cboLocality_Cluster = Null
Me.cboLocality_Cluster.Requery
End Sub
 
Private Sub Form_Current()
Me.cboArea.Requery
Me.cboLocality_Cluster.Requery
End Sub

I have the following in the 1st combo box row source (cboDivision):

SELECT Division.ID, Division.Division FROM Division;

And I have the following in the row source of the 2nd combo (cboArea):

SELECT Area.ID, Area.[Division ID], Area.Area FROM Area WHERE (((Area.[Division ID])=forms![Division detail]!cboDivision));

And this is the row source of the 3rd combo (cboLocality/Cluster):

SELECT [Locality/Cluster].ID, [Locality/Cluster].[Area ID], [Locality/Cluster].[Locality/Cluster] FROM [Locality/Cluster] WHERE ((([Locality/Cluster].[Area ID])=forms![Division detail]!cboArea));

This works perfectly when I use the subform on it's own (not within the main form), but when I try to use the combo boxes with the subform within the main form, I get an error as the main form (Complaints form) doesn't know where to look for the combo boxes on the subform (Division detail)!

Can anyone advise what I should do to get this to work?

Thanks!

 

[Kafrin]

I think you need to reference your combo boxes by going through the main form, so it looks like:

WHERE

.[Field]=forms![Main form name]![Subform control name].form![Control name]

You find the control through the route Forms Collection > Main Form > Subform Control on Main Form > Identify Subform Control as Form > Control Name

So you need to use:

WHERE Area.[Division ID]=forms![Complaints]![Division detail].form!cboDivision

and

WHERE [Locality/Cluster].[Area ID]=forms![Complaints]![Division detail].form!cboArea

...assuming that the subform control (ie the box that the subform sits in on the main form) has the same name as the subform.