Textbox Controlsource referencing subform - [] driving me nuts!

Question

This is driving me CRAZY.

I'm revisiting an app I've worked on for a few years, to make some changes.

A form has a textbox (well, many textboxes) whose ControlSource is like

=MyVBAFunction(NameOfSubFormControl.Form.NameOfTextBox)

No problems.

Now I've saved a new renamed version of the form, changed the subform control to refer to a new form.  (Subsequently, I've also deleted the subform control, recreated it with its original name, referenced it to the new form - just to see if that would help).

Now the textbox on the main form refuses to get its value - it gets "#Name?".  I can't set its Controlsource to the same as in the old version - as soon as I try (in the Properties box), Access sticks its stupid [] around everything, so that I get:

=MyVBAFunction([NameOfSubFormControl].[Form].[NameOfTextBox])

If I open the form, and then set the COntrolSource through VBA in Immediate, like this

ThatMainForm!TheTextBox.ControlSource="=MyVBAFunction(NameOfSubFormControl.Form.NameOfTextBox)"

then I check TheTextBox's Value in Immediate, and - hey presto, it's getting its value.  But I can't set the ControlSource to this correct string in Design.

[] around "Form" is incorrect - Form is not a member of the default collection of SubFOrmControl, but aproperty .  But Access is insisting on sticking these bloody things around "Form", making my life ____.  How can I stop it doing this?  It's Acc 2003 SP3, if that helps.

I've tried using SubFormControl.Form!TextBoxName, with a ! instead of a . - but still the [] appear.

thanks for any ideas!

Answer 1

Tis it is but you could have just written a bit of VBA ibn On_Current event...

If IsNull([FieldName]) Then

     Do This

Else

     Do That

End If

Glad you got it working!

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 2

Thank you Gina.  After posting my question I already tried what you suggested - referencing the containing form through Forms!MainFormName rather than through Me or an implied Me.  I think I read about this somewhere as a possible solution - or maybe (I can't remember) I found that this was the solution to another problem (oh yes, I remember now, it's the required syntax to refer to same-form fields/controls in Conditional Formatting expressions).

Didn't work unfortunately.

Looking into it further, the problem seems to be caused by the referenced subform having an empty recordset - which is possible and allowed by design.  The referenced textboxes on the subform are totals in the footer, using SUM([Field]) as their Controlsource; so the problem is not with controls in a Detail section "not existing" when a recordset is empty and (as is the case here) the subform's AllowAddingRecords or whatever it's called being False.   This latter is something I've come across before.  The textboxes in the footer exist all right, I can see them.

It's odd that opening the main form with an empty subform seems to break Access' parsing of the Controlsources, in a way that doesn't susequently get resolved at run-time.  Opening it again having kludged the subform to show some records doesn't resolve the problem, but putting

Me[mainform].TextBox.ControlSource="=Me.SubFormControl.Form!TextBox"

into the main form's Open event proc seems to "kick" Access into revisiting and re-parsing all the Controlsources - all the Textboxes that use this kind of ControlSource referencing the subform then suddenly work, even though only one of them has had its COntrolsource changed in VBA at run-time!

The actual intention is to pass the subform's Textbox value into a VBA function, and use the result of that as the value of the Textbox on the main form.  Extremely odd results.  "Automation Error", and VBA errors "The expression has no value".  What seems to be getting picked up from the subform's Textbox when the recordset is empty is a bizarre value (which the VBA accepts as a Variant) - it's not Null, not Empty, and not Nothing.

This is all too unreliable to be released, so I've found a workaround - a VBA procedure in the main form sets all the Textboxes' values by running an SQL table-function (SUM all columns) directly on the subform's ultimate data-set.  Since the subform is read-only, this is effective enough and I don't have to remember to re-run it in case the data's been changed.

It's all very bizarre.

Answer 3

Try...

Forms!NameOfMainForm!NameOfSubFormControl.Form!NameOfTextBox

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.