showing ID ref in report

[Guest]

I have a report that I want in some cases to get its data from a field that
is sourced by a combo box. When I access the table to look at the data stored
it is storing the text data through a lookup table and displays the data i
want to show on the report. Once I add a text box on the report and source it
I keep getting the ref ID rather than the text. Even though I have other data
on my report that is showing the correct data with the same process. I have
checked the row source and it compares to the others that are showing the
right data. Where am I missing???

I want it to show workout type not the workouttype ID. A few pointers would
be good.

Paul

 

[Allen Browne]

It sounds like you used the lookup wizard in table design, so Access is
lying to you about what it stored in the field. Although it displays the
type, it is actually storing the ID.

To solve the problem, create a query that uses your main table and the table
of workout types. Drag the text field from the workout types table into the
grid, as well as the fields from your main table. Save the query.

Now open the report in design view, and change its RecordSource property
(Data tab of Properties box) to the name of the query. Now you can delete
the problem text box that was showing the ID, and drag the workout type text
box from the field list onto the report. (If you don't see the field list,
it's on the View menu.)

For more info about this issue, see:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

 

[Guest]

Thanks Allen I shall do that !!

It sounds like you used the lookup wizard in table design, so Access is
lying to you about what it stored in the field. Although it displays the
type, it is actually storing the ID.

To solve the problem, create a query that uses your main table and the table
of workout types. Drag the text field from the workout types table into the
grid, as well as the fields from your main table. Save the query.

Now open the report in design view, and change its RecordSource property
(Data tab of Properties box) to the name of the query. Now you can delete
the problem text box that was showing the ID, and drag the workout type text
box from the field list onto the report. (If you don't see the field list,
it's on the View menu.)

For more info about this issue, see:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm