How do I translate a number to the code in a table it refers to?

[Guest]

Hi,

I'm using Access 97 and I want to create an Access report on a
table that has the following table & fields:

tblTapes
ProgramID
ProgramName
YearReleased
Rating (this is actually the RatingsID number from the following
table selected on a form from a combobox.)

tblRatingCodes
RatingsID
RatingsCode
RatingsExplaination

When I use the data to print to an existing form, I use a DLookup
in Visual Basic. I just can't figure out how to use it on a Access
Report.

Any help would be appreciated.

Thanks,
LadyAmethyst

 

[Allen Browne]

Base the report on a query that reads the fields from both tables.
1. Create a query that has both tblTapes and tblRatingCodes.

2. In the upper pane of query design, you see a line joining the 2 tables.
Double-click that line. Access offers a dialog with 3 options. Choose the
one that says:
All records from tblTapes, and any from ...
(If you do not do this, any tape that has no rating will not be included.)

3. Drag the fields you need from both tables into the grid.

4. Save the query.

5. Use this query as the RecordSource for your report.

It is also possible to use a DLookup() expresion in the Control Source of a
text box on the report, e.g.:
=DLookup("RatingsCode", "tblRatingCodes", "RatingsID = " & [Rating])
However that will be much less efficient than the query.

 

[Guest]

Thanks Allen. Just what I needed.

LadyAmethyst

Base the report on a query that reads the fields from both tables.
1. Create a query that has both tblTapes and tblRatingCodes.

2. In the upper pane of query design, you see a line joining the 2 tables.
Double-click that line. Access offers a dialog with 3 options. Choose the
one that says:
All records from tblTapes, and any from ...
(If you do not do this, any tape that has no rating will not be included.)

3. Drag the fields you need from both tables into the grid.

4. Save the query.

5. Use this query as the RecordSource for your report.

It is also possible to use a DLookup() expresion in the Control Source of a
text box on the report, e.g.:
=DLookup("RatingsCode", "tblRatingCodes", "RatingsID = " & [Rating])
However that will be much less efficient than the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm using Access 97 and I want to create an Access report on a
table that has the following table & fields:

tblTapes
ProgramID
ProgramName
YearReleased
Rating (this is actually the RatingsID number from the following
table selected on a form from a combobox.)

tblRatingCodes
RatingsID
RatingsCode
RatingsExplaination

When I use the data to print to an existing form, I use a DLookup
in Visual Basic. I just can't figure out how to use it on a Access
Report.

Any help would be appreciated.

Thanks,
LadyAmethyst