Showing value based on another value in a form

[Valerie Wong]

Hi please if anyone could help on the following question it would be much
appreciated.

I have two tables, with fields described below

1) Project
- Project ID (Primary Key)
- Project Name

2) Transaction
- Transaction ID
- Project ID
- Transaction Size
- Transaction Date

In a form I show information of Transaction (Table 2). However instead of
Project ID, I would like to show Project Name. How could I do this, what
should I write in the control source?

Many thanks in advance.

Valerie

 

[Danny Lesandrini]

You need to use a combo box control instead of a text box. (The text box
will simply display the ProjectID.)

The combo box should have it's column count property set to 2.
The column widths should be something like 0;2; (zero for the ID and some
value for the ProjectName)
Set the row source of the combo box to SELECT * FROM Project ORDER BY
ProjectName

This should be about it.

 

[Valerie Wong]

Many thanks Danny,

The form displayed the Project Name I wanted. However as each Record has
only one Project ID and therefore one Project Name, I did not want the list
of Project Names, but just that one Project Name that the Project ID
corresponds to. How could I do this please?

Regards,
Valerie

 

[Danny Lesandrini]

In that case, use a regular text box and add the project name to the query
behind the form.

SELECT tblProject.ProjectName, tblMainTable .*
FROM tblMainTable
INNER JOIN tblProject ON tblMainTable.ProjectID = tblProject.ProjectID

You'll have to change table names, but you get the idea. This will make the
project name uneditable. Only a combo box will allow edits, since the
ProjectID is what needs to change, not the project name text.