Display info not in source

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
I

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
 
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.
 
Back
Top