Using a sub-form field in a calculation

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have an unbounded form with a dropdown box for states.
Based on the value selected in that dropdown box, I have a
value selected from a query that is shown in a sub-form.
Now I want to do some calculations on that returned value
but if I try to use that field I get #Name? as calculated
value. Any thoughts or help would be appreciated.
 
In the underlining query that creates my subform I have
the selection criteria of [Forms]![frmState]![State
Name]. That query results in only one row being
returned. I want to use that row result to do a
calculation in my main form. I put the query in a subform
because I'm going to need to do this same thing with
multiple tables (not all of which are based on state) and
I thought that I could grab the number the same way I did
for the query. But when I refer to the field (=[Forms]!
[frmTrend]![Text5]) I get the #Name? error. I've also
tried using the query field as my control but that doesn't
work either. Any help would be appreciated.
 
Bob,

Subforms are not registered in the Forms collection, because they open as
controls on their container form, so the syntax "Forms!frmTrend!Text5" will
not work from outside the subform. You'll need to use
"Forms!frmState!frmTrend.Form!Text5".

The above assumes that frmTrend is the subform.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Bob said:
In the underlining query that creates my subform I have
the selection criteria of [Forms]![frmState]![State
Name]. That query results in only one row being
returned. I want to use that row result to do a
calculation in my main form. I put the query in a subform
because I'm going to need to do this same thing with
multiple tables (not all of which are based on state) and
I thought that I could grab the number the same way I did
for the query. But when I refer to the field (=[Forms]!
[frmTrend]![Text5]) I get the #Name? error. I've also
tried using the query field as my control but that doesn't
work either. Any help would be appreciated.
-----Original Message-----
Bob,

How are you referring to the subform control? It should be something like
the following:
Me.subformname.Form.controlname

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html





.
Click to expand...
Click to expand...
 
Back
Top