Report field populate from table lookup

[accessisnew]

I created a report from a table and it is working fine.

I have a field in the report header called “Source” and now I need to add a field in that header based on the value in the header field “Source”. The new report field is called “Text32”.

I created a table, “Source_Name” that has two columns, “Source” and “Source Name” so that I can do a lookup based on the value in the report header “Source”. I am trying to populate the new “Text32” field using the Control Source property of the new text box with the expression

=DLookup("Source_Name.[Source Name]", "Source_Name", "Source_Name.[Source]=Reports!reportname![Source]")

I keep getting an error in the new “Text32” field when I view the report.

Am I using the wrong technique? Is my syntax bad?

Any help or a pointer to an existing thread would help.

 

[dhookom]

Why can't you add the table "Source_Name" to your report's record source and join the Source fields so you can have the [Source Name] field in the field list?

Duane
Hook'D on Access
MS Access MVP

 

[accessisnew]

I created a relationship/join between the two tables. Then in the report for Text32 opened the properties but still did not see Source Name in the drop down list for Control Source.

Maybe I'm just not understanding the suggestion.

 

[dhookom]

Did you make any changes to the report's record source as suggested? What is the report's record source?

Duane
Hook'D on Access
MS Access MVP

 

[accessisnew]

The original reports Record Source is a table that had all the data I originally needed. I created a new table that contains the lookup name and the new field that I want to add to the report. After I created the join for the original table to the new table, I tried to add the new Source_Name table to the reports Record Source but it appears the Record Source only allows one table name.

 

[dhookom]

Again "What is the report's record source?"
Did you attempt to click the little [...] button to the right of the Record Source property?

Duane
Hook'D on Access
MS Access MVP

 

[accessisnew]

The Report Source is the original table, t_NEW_xxxs which is in the white space to the left of the [...]. When I click the [...], I get a new window that is asking if I want to create a query based on a table.

 

[dhookom]

Respond to the new window with "Yes" and then add your table to your existing table in the query.

Duane
Hook'D on Access
MS Access MVP

 

[accessisnew]

Got it. Thanks for your help.