Display info not in source

[Ivor Williams]

I have a table tblLocations with two fields - LocationID and Location. This
table is the data source for a combo box on a form. A location is chosen
using the combo box which sets the criteria in a field in a query. When the
query is run, the Location is displayed, which is OK. The query is a data
source for a report. On the report, the LocationID is displayed, which is
not OK. How can I have the Location rather than the LocationID display in
the report? If I add the tblLocations table to the query, the results in the
query are skewed, so that is not an option.

Ivor

 

[Ken Snell]

Is the form still open when the report is generated? If so, you can use a
control source expression similar to this for the report's textbox:

=Forms!Formname!ComboboxName.Column(x)

where x needs to be replaced by the column number minus 1 that corresponds
to the location name in the combo box's row source query. Thus, if the
location name is the second column in the combo box's row source query, x
should be replaced by 1 (Column is a zero-based property).

Alternatively, you could use a DLookup function as the control source for
this report's textbox (again, assuming that the form is open):

=DLookup("LocationNameFieldName", "tblLocations", "[LocationID]=" &
Forms!Formname!ComboboxName)

Or, more easily, use the LocationID value of the report's recordsource (be
sure to put a control {named LocationID, the same as the field's name} on
the report that is bound to the LocationID field, but make the control
invisible):

=DLookup("LocationNameFieldName", "tblLocations", "[LocationID]=" &
[LocationID])

I don't know why including tblLocations table in the report's recordsource
query should cause problems, but without knowing more about the structure of
the query, tables, etc., I can't say more.