reference control on main form from nested subform 2

[sterlecki]

I have a form (main) with an unrelated (no Parent/child relationship) subform (subform1) nested in subform1 is subform2 that has a parent/child relationship with subform1.

Within the footer of subform2 I have placed a 1 row listbox where the rowsource is a table and the criteria in the Whereclause references the value of a combobox on form(main).

The sql statement works fine in a query and also works as a listbox control when the control is placed on form(main).

I cannot get it to return a value when the listbox is placed in the footer of subform2.

Form Main = frm_TestInput
subform1 = frm_TestData
subform2 = frm_TestDetail

The sql that works for the listbox when placed on Form(Main) is:

SELECT lkup_LeaseRatios.BOperMMCF, lkup_LeaseRatios.LeaseNumber FROM lkup_LeaseRatios WHERE (((lkup_LeaseRatios.LeaseNumber)=[Forms]![frm_TestInput]![cbo_LeaseNumber]));

I'm pretty sure that it's a reference problem pertaining to [cbo_LeaseNumber] but I'm stuck on how to fix it.

 

[MichaelRed]

From the SubForm, you use the parent property to reference the 'Next level up' (obviously parent.parent references the 2nd level up ...

To reference a subform, just realize that (from the 'parent' perspective it (the subform) is just a control - with other controls on it. Again, to delve into deeper layers just reference the next level subform as a control on previous layer ...

A search here in using the keyword "Parent" shoud return numerous references.




MichaelRed

 

[sterlecki]

Hi MichaelRed

I'm still not getting a result. Remember subform2 is nested in subform1 and these are linked. Subform1 does not have a Parent/Child relationship to the Main Form.

I have tried several permutations of my original post based on your comments without success. Also I'm not certain if you can use the me! or me. reference in a rowsource statement? I've always used the Forms![FormName]![controlname] format. I generated the SQL from a query using the criteria line.

I have tried:

=me.parent.parent.[cbo_LeaseNumber] unsuccessful
=me.parent.[cbo_LeaseNumber] unsuccessful
etc.

Based on statement of my original post which works on the Main Form what would you try?

 

[MajP]

I may be wrong, but I actually think your control should work as is. My guess is that it is not requerying. The problem may be that a form loads from the inside out. So your subform listbox references a control on the main form before the main form loads. Try requerying the listbox on the listbox's onEnter event.

 

[TheAceMan1]

How are ya sterlecki . . .

I agree with [blue]MajP[/blue] in that the listbox rowsource is just fine. Your problem has to do with the combobox returning a [blue]Null[/blue], which occurs when no combobox selection is made (occurs when the form is first opened). [blue]This will cause the listbox to be empty![/blue]

Since the listbox is dependent on the combobox what you need to do is control the rowsource of the listbox via the combobox [blue]After Update[/blue] event. You do this by writing the SQL of the listbox to its [blue]rowsource. [/blue] [purple]Note: by default in design view no sql is assigned[/purple].

The combobox [blue]After Update[/blue] event would look like:

[blue]   Dim SQL As String
   
   SQL = "SELECT BOperMMCF, LeaseNumber FROM " & _
         "lkup_LeaseRatios " & _
         "WHERE ([LeaseNumber]=[Forms]![frm_TestInput]![cbo_LeaseNumber]);"
   [frm_TestData].Form![frm_TestDetail].Form![purple][B][I]ListboxName[/I][/B][/purple].RowSource = SQL[/blue]

Each time a combobox selection is made, the listbox SQL is rewritten which requeries the listbox.

[purple]Note: when you first open the form, the listbox should be empty until you make a selection from the combo![/purple]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[sterlecki]

Thanks MajP and TheAceMan1 you were correct. I started to have a clue when I did a debug.print of the listbox when it was on the mainform and it returned null even though it was displaying the correct value.

My original SQL works fine so here's what I did to display the listbox in the subform and also select the first(only) list item so I could grab it if needed.

'This routine refreshes the subforms listbox, sets the focus, and selects the first record so it's value is not null
Forms!frm_TestInput![frm_subfrm_TestData].Form![frmsubfrm_TestDetail].Form.Refresh
Forms!frm_TestInput![frm_subfrm_TestData].Form![frmsubfrm_TestDetail].Form![lst_HistOilGasRatio].SetFocus
Forms!frm_TestInput![frm_subfrm_TestData].Form![frmsubfrm_TestDetail].Form![lst_HistOilGasRatio].ListIndex = 0
Me.txt_TestNumber.SetFocus

'Debug.Print Forms!frm_TestInput![frm_subfrm_TestData].Form![frmsubfrm_TestDetail].Form![lst_HistOilGasRatio]

Is there a way to just make the default value of the list box the first record within the properties sheet?


A star for each of you.

 

[sterlecki]

As you both know the above code (previous post0 runs in the cbo_LeaseNumber_AfterUpdate() event. Sorry I left that out.

 

[sterlecki]

I also bypassed several lines (2,3,4) of the above code by setting the default value of the list box to

=[lst_HistOilGasRatio].[ListIndex](0)

As I only want the first row anyway.

So simplified in the AfterUpdate event of the combobox
cbo_LeaseNumber is just one line of code to refresh the subform.

Forms!frm_TestInput![frm_subfrm_TestData].Form![frmsubfrm_TestDetail].Form.Refresh