populate field via select based on record key

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a report that is relatively simple that I want to
dynamically populate one field of the report based on a
another key field of the same record of the report.

I tried setting the control source of the field that I
want to populate to a select statement that included the
key field in the report. i.e =(select text from table_a
where keyfield = Me!keyfield). It does not give me an
error when the report runs, but I just end up with #Name?
in the field that I want populated.

Is there a way to do this so that when the report runs and
values are returned in the key field, to at that time, do
a lookup in another table and populate a field in the
report.

Thanks,

Dave
 
Can you add table_a to the report's record source and join the keyfields? If
not, you could use a combo box bound to KeyField and a row source of:
SELECT KeyField, Text FROM table_a;
Hide the first column.
You could also use DLookup() but it is very slow and should be avoided.

Bottom line... you can't use a SQL statement as the control source of a text
box.
 
Thanks,

The query is a little involved (contains subquery, etc.)
that I was trying to avoid joining the two tables.
Especially in light of needing distinct values in the
final set. I did not want to have to do a group by
including the large text field that was the field that I
wanted to do a seperate lookup on using the key field.

You answered my basic question on if I could use a SQL
statement as the control source of a text.

Dave
 
Back
Top