Trying to get a report element to update from another table

[aroostook]

I hope the subject is descriptive enough...

Anyway, here's the problem. I have a text box element on a report that is associated with one table. I need it to display results based off of a value in that table that relates to a second table.

Example: I have the text box currently displaying data from [Table1]![EmployeeID]. I want it to display [Table2]![FirstName] & [Table2]![LastName] where the data from Table1 is currently showing. So instead of showing "14", it shows "Howard Jones." How do I do this?

Stupidly simple, probably, but I'm striking out finding the solution.

Thanks.

 

[PWise]

Either join table 2 in the record source
or change text box to combobox
2 coulmns
column witdh = 0";3"

row source ==
Select EmployeeID,[FirstName] & " " &[LastName]
from table2

 

[dhookom]

You can't use a SQL statement as a control source. The best solution is to simply include both tables in the record source. Join them on the Primary and Foreign key fields.

Duane
Hook'D on Access
MS Access MVP

 

[aroostook]

...or I could just ditch the report the other guy made and make one from scratch. Oh, and the combobox thing was the bees knees. Yes, I said "bees knees." No, I'm not 73.

Thanks, guys.

 

[dhookom]

A combo box on a report is not the ideal solution for a report. You should do it right be adding table2 to the report's record source.

Duane
Hook'D on Access
MS Access MVP