Table uses a select statement as value to populate subform data

[Mary]

I have a project that is using a table that contains a select statement as
the value.

This table is used to output TypeName which is the field title in the
metrics table to a form as if it were row data and TypeQuery will populate
the subform.

The form will display the TypeName and the subform should display the data
for related select statement in TypeQuery. The Yes/No will indicate if that
data is readonly (a calculated amount) if not readOnly then they can enter
new or change data in the subform.

TypeName IsReadOnly TypeQuery
1stAlias Name Yes/No Select YearID, UserRate FROM MetricType_qry
2ndAlias Name Yes/No Select YearID, ClassCompleted FROM
MetricType_qry
etc.
The output should be:
Form:
1st Alias Name
SubForm:
2009 124
2008 234
2007 198

I'm having trouble getting this to work in the subform.
My question is how do I connect to the select statement in the table and
return the selected data to the subform?
Any suggestions would be appreciated.
Thanks.

 

[Jeff Boyce]

Mary

Are you saying that the table definition includes a "lookup" field? If so,
you've (re-)discovered one of the reasons why folks here in the newsgroups
hold "lookup fields" in tables in low regard. The field in the table is
actually storing the rowID of the looked up value, but displaying the looked
up value. This is confusing, no?!

The recommended approach is to convert that lookup field back to the
appropriate data type to hold the rowID, then use a combobox in a form to do
any looking up...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.