Using a Parameter Query in a Report?

[EricG]

I have a table called "Changes" with several fields that is feeding a
sub-report of my main report. One of the fields in the table is named
"Contract". This field is actually an index into a lookup table that has the
text version of the contract name.

What I want to do is somehow translate the index in "Changes" back into the
contract name and put that name in the sub-report. The lookup table is named
"tlkp_Contract" and has "ID" and "Contract_Name" as its fields. I created a
query that looks up "Contract_Name" when you supply the "ID" as a parameter.
I would like to use that query in my report as the data source for the
Contract field on the report, but I can't figure out how to feed the
parameter to that query directly from the "Changes" table that is feeding the
sub-report. I just get a input box asking me to supply the value of ID, and
then I get #ERROR on the report in the contract field.

I would appreciate it if any of you Access wizards would suggest a better
way to do this.

Thanks in advance,

Eric

 

[andrewmrichards]

I have a table called "Changes" with several fields that is feeding a
sub-report of my main report.  One of the fields in the table is named
"Contract".  This field is actually an index into a lookup table that has the
text version of the contract name.

What I want to do is somehow translate the index in "Changes" back into the
contract name and put that name in the sub-report.  The lookup table isnamed
"tlkp_Contract" and has "ID" and "Contract_Name" as its fields.  I created a
query that looks up "Contract_Name" when you supply the "ID" as a parameter.  
I would like to use that query in my report as the data source for the
Contract field on the report, but I can't figure out how to feed the
parameter to that query directly from the "Changes" table that is feedingthe
sub-report.  I just get a input box asking me to supply the value of ID, and
then I get #ERROR on the report in the contract field.

I would appreciate it if any of you Access wizards would suggest a better
way to do this.

Thanks in advance,

Eric

Hi Eric

What you need to do is create a query which is based on the fields you
want from tbl_Changes, except for the Contract ID field, plus the
Contract_Name field from tlkp_Contracts. As long as the two tables are
related, this should work fine.

Then use this query as the source for your subreport, rather than the
Changes table. You should then find that you have a Contract_Name
field (taken from the tlkp_Contracts table) to use on your subreport,
rather than your existing Contract_ID field.

Does that make sense?

Best regards

Andrew

 

[EricG]

Actually, that makes amazing sense! I found a partial solution using the
"CHOOSE" function, but I had to manually enter the contract names. Your idea
is much better and should be easy to implement.

Thanks!

Eric