Display lookup value on form

[Guest]

Hi

I have a database with two tables, the first [called QUERIES] of which
contains details of invoices under query in our Sales Ledger. The data for
most of this table is not user-entered but is imported from our Unix accounts
package. One of the columns in this table has the imported value for the Cost
Centre ID related to that query. The second table [called COST CENTRES] just
contains the Cost Centre ID and the Cost Centre Name.

I have one form which displays the details from the first table. Where it
displays the Cost Centre ID, I would like to have a label next to it showing
the Cost Centre Name, looked up from the second table. My question is how to
do this - if the user were entering the data in that field, I would have used
a combo box with a look-up to the second table, and then use =[COST
CENTRES].Column(1) to display the cost centre name on the form. But this
information is already in the table, and all I get when I do this is #Name?

Any help would be appreciated - newbie language please.


Thank you
Martyn

Access 2000
Windows XP

 

[Damon Heron]

This is one way. Your form's record source is based on a saved query,
correct? In design view, open the form's properties, and where it says
recordsource, click on the (...) to the right and build a query. In the
query window, add the 2nd table. If there is no relationship between
CostCentreID between the tables, create one. Then click on the second
table's CostCentreName to add it to the query. Back on the form, add a
textbox and select as a controlsource for the textbox "CostCentreName". If
you want it to look like a label, then change the format backstyle to
transparent and the data to Locked.

HTH
Damon

 

[Guest]

Thank you very much - this worked perfectly.

Martyn

This is one way. Your form's record source is based on a saved query,
correct? In design view, open the form's properties, and where it says
recordsource, click on the (...) to the right and build a query. In the
query window, add the 2nd table. If there is no relationship between
CostCentreID between the tables, create one. Then click on the second
table's CostCentreName to add it to the query. Back on the form, add a
textbox and select as a controlsource for the textbox "CostCentreName". If
you want it to look like a label, then change the format backstyle to
transparent and the data to Locked.

HTH
Damon

Hi

I have a database with two tables, the first [called QUERIES] of which
contains details of invoices under query in our Sales Ledger. The data for
most of this table is not user-entered but is imported from our Unix
accounts
package. One of the columns in this table has the imported value for the
Cost
Centre ID related to that query. The second table [called COST CENTRES]
just
contains the Cost Centre ID and the Cost Centre Name.

I have one form which displays the details from the first table. Where it
displays the Cost Centre ID, I would like to have a label next to it
showing
the Cost Centre Name, looked up from the second table. My question is how
to
do this - if the user were entering the data in that field, I would have
used
a combo box with a look-up to the second table, and then use =[COST
CENTRES].Column(1) to display the cost centre name on the form. But this
information is already in the table, and all I get when I do this is
#Name?

Any help would be appreciated - newbie language please.


Thank you
Martyn

Access 2000
Windows XP