Display a field based on an ID

[cynteeuh]

Pardon if this is somewhat of a duplicated question, but I couldn't find an
answer that quite fit my scenario.

I have a rpt linked to a tbl that has a field w/a lookup combo box that is
bound by the primaryID. I'm wondering if it's possible to display the field
description vs. the ID on my rpt without creating a qry? I know how to get my
results by creating a qry and linking the qry vs. the tbl, but I'd like to
eliminate the qry portion if possible.

Is there anyway to pull/display the description field v. ID (e.g. Safeway v.
SFWY) on the rpt that's linked to the tbl that contains the lookup combo box?

Thanks in advance for your help!

 

[Marshall Barton]

Pardon if this is somewhat of a duplicated question, but I couldn't find an
answer that quite fit my scenario.

I have a rpt linked to a tbl that has a field w/a lookup combo box that is
bound by the primaryID. I'm wondering if it's possible to display the field
description vs. the ID on my rpt without creating a qry? I know how to get my
results by creating a qry and linking the qry vs. the tbl, but I'd like to
eliminate the qry portion if possible.

Is there anyway to pull/display the description field v. ID (e.g. Safeway v.
SFWY) on the rpt that's linked to the tbl that contains the lookup combo box?

Well, you could use DLookup, but it might be too slow to be
useful. What's you hangup with using a query, it's the
standard way to work with data.

 

[cynteeuh]

No real hangup other than not wanting to change record source and field
names. I thought there might be an easier solution by just changing the
current text box control source on the rpt by a formula.

Thanks for your help!

 

[Marshall Barton]

I don't understand why you would have to change all the
field names just because you use a query instead of a table
as the report's record source.

The way I read your situation, you have a report with record
source set to tblA with controls bound to the fields in the
table. The query I would expect to do the job is something
like:

SELECT tblA.*, tblLookup.Description
FROM tblA INNER JOIN tblLookup
ON tblA.DescID = tblLookup.ID

When you use that as the report's record source, you would
only have to change the one text box from DescID to the
Description field in the query.

 

[cynteeuh]

I had hoped to only change the one control source with an expression, but
you're absolutely right. Thank you!

I don't understand why you would have to change all the
field names just because you use a query instead of a table
as the report's record source.

The way I read your situation, you have a report with record
source set to tblA with controls bound to the fields in the
table. The query I would expect to do the job is something
like:

SELECT tblA.*, tblLookup.Description
FROM tblA INNER JOIN tblLookup
ON tblA.DescID = tblLookup.ID

When you use that as the report's record source, you would
only have to change the one text box from DescID to the
Description field in the query.
--
Marsh
MVP [MS Access]

No real hangup other than not wanting to change record source and field
names. I thought there might be an easier solution by just changing the
current text box control source on the rpt by a formula.