Combo box on nested subform can't reference combo box on parent subform. (1 Viewer)

[DataBass]

I have a form in single form view, called frmProjectsNew. On that form, I have a subform in datasheet view, called frmIncomeSub. Nested within frmIncomeSub, I have another subform in datasheet view, called frmExpensesSub. Both subforms are linked properly with Master & Child fields.

On the nested subform, frmExpensesSub, I have a combo box called cboCategory that gets its values from a query called qryPhaseCategory. Here is the SQL statement for the Row Source property of the cboCategory:

SELECT qryPhaseCategory.Category FROM qryPhaseCategory 
WHERE (((qryPhaseCategory.ParentPhase)=Me.Parent!cboPhase)) 
ORDER BY qryPhaseCategory.[SortNumber];

cboPhase is a combo box on the parent subform, frmIncomeSub. No matter how I make the reference to cboPhase, brackets, no brackets, ., !, full reference, Parent etc., I am ALWAYS prompted to enter the value for cboPhase.

As a test, I put a MsgBox in the On Enter event of the cboCategory combo box, with the prompt (Me.Parent.cboPhase) and it Always returns the correct value of cboPhase in the parent subform. I have no idea why the combo box can't retrieve this value. :banghead:

Can anyone help me out with this?

 

[isladogs]

Hi
Welcome to the forum
If you use the code tags button (#) at the top of the message when typing, you won't get the phantom space. It also makes code easier to read as all indentations are preserved

Use Me.Parent.Form.cboPhase or just Parent.Form.cboPhase

SELECT qryPhaseCategory.Category FROM qryPhaseCategory 
WHERE (((qryPhaseCategory.ParentPhase)=Parent[COLOR="red"][B].Form.[/B][/COLOR]cboPhase)) 
ORDER BY qryPhaseCategory.[SortNumber];

 

[DataBass]

Colin,
Are you serious? It was that simple? Really? Unbelievable...

Parent.Form.cboPhase worked. For some reason Me.Parent.Form.cboPhase didn't. I don't get it, but I'm happy it works.

I can't thank you enough for your help with this.

Would you be able to explain why my MsgBox could get the value with Me.Parent.cboPhase but cboCategory required Form in the SQL statement?

Thanks again,

 

[isladogs]

Here's a useful link explaining how to refer to controls on a subform / parent form etc:
http://allenbrowne.com/casu-04.html

No idea offhand why the MsgBox worked without the Form. part
Does it still work if you include that in the MsgBox?

 

[DataBass]

Yes, the message box still works if I use Me.Parent.Form.cboPhase.

I wonder if this is "normal" behavior

 

[Minty]

I don't think that you can refer to the current form object using Me. in a query, it has to be the Form. object.

When calling a message box in VBA the Me. reference will work as it can "see" the current form.

 

[DataBass]

That makes sense. Thanks