How to display text in a text box whose source is an ID number

[yltang]

I have the following table:

tblProduct: ProductID, ProductName

I would like to add a textbox in a form bound to ProductID. How do I do to let the text box show the ProductName, instead of the ProductID? Thanks in advance.

[Stewart Ross]

It's usually done using a combo box whose row source is a query based on your product table. The combo would have two columns, the product ID and the product name, with the width property for the ID column set to 0 (hidden in other words).

The Access combo box wizard does this for you on a step-by-step basis.

-Stewart

PS the combo would be bound to field Product ID in the underlying table, just as you suggested in your question

[yltang]

It's usually done using a combo box whose row source is a query based on your product table. The combo would have two columns, the product ID and the product name, with the width property for the ID column set to 0 (hidden in other words).

The Access combo box wizard does this for you on a step-by-step basis.

-Stewart

PS the combo would be bound to field Product ID in the underlying table, just as you suggested in your question

Thanks Stewart,
Sorry that I didn't make my question more clear. Actually, there are two tables with the main fields as follows:

tblProduct: ProductID, ProductName, ...
tblOrder: OrderID, ProductID, ...

I need to have a textbox in the order input form "frmOrder" such that the ProductName will show up in the textbox. For some reasons, I can't use combo boxes. Sorry again and any further suggestions? Thanks.

[Stewart Ross]

A combo is the best solution here, but if you can't use one for some reason there are two other options:

(1) in your underlying query which acts as the rowsource for the subform you can join order and product together so that the product name is listed along with the product ID when the product ID is selected, which would allow you to place a bound product name textbox onto your subform, or

(2) you can place an unbound textbox on your subform and use the domain lookup function DLookup to lookup the corresponding product name for the product ID field. You would set the row source of the textbox to the following, choosing the first version if ProductID is a number or the second version if it is a string:

=DLookup("[ProductName]", "[tblProduct]", "[ProductID] = " & [ProductID])
=DLookup("[ProductName]", "[tblProduct]", "[ProductID] = '" & [ProductID] & "'")

In both cases you would also set the Enabled property of the textbox False and Locked True (to prevent users from trying to alter the contents). I also use a different background colour for lookup controls to differentiate them from user data entry controls.

-Stewart

[NeoPa]

At its simplest I think you need to build your form on a query with SQL similar to the following :

SELECT tO.OrderID,
       tO....,
       tP.ProductName
 
FROM tblOrder AS tO LEFT JOIN tblProduct AS tP
  ON tO.ProductID=tP.ProductID
 
...

[yltang]

A combo is the best solution here, but if you can't use one for some reason there are two other options:

(1) in your underlying query which acts as the rowsource for the subform you can join order and product together so that the product name is listed along with the product ID when the product ID is selected, which would allow you to place a bound product name textbox onto your subform, or

(2) you can place an unbound textbox on your subform and use the domain lookup function DLookup to lookup the corresponding product name for the product ID field. You would set the row source of the textbox to the following, choosing the first version if ProductID is a number or the second version if it is a string:

=DLookup("[ProductName]", "[tblProduct]", "[ProductID] = " & [ProductID])
=DLookup("[ProductName]", "[tblProduct]", "[ProductID] = '" & [ProductID] & "'")

In both cases you would also set the Enabled property of the textbox False and Locked True (to prevent users from trying to alter the contents). I also use a different background colour for lookup controls to differentiate them from user data entry controls.

-Stewart

Got it! Problem solved. Thanks a million.

[yltang]

A combo is the best solution here, but if you can't use one for some reason there are two other options:

(1) in your underlying query which acts as the rowsource for the subform you can join order and product together so that the product name is listed along with the product ID when the product ID is selected, which would allow you to place a bound product name textbox onto your subform, or

(2) you can place an unbound textbox on your subform and use the domain lookup function DLookup to lookup the corresponding product name for the product ID field. You would set the row source of the textbox to the following, choosing the first version if ProductID is a number or the second version if it is a string:

=DLookup("[ProductName]", "[tblProduct]", "[ProductID] = " & [ProductID])
=DLookup("[ProductName]", "[tblProduct]", "[ProductID] = '" & [ProductID] & "'")

In both cases you would also set the Enabled property of the textbox False and Locked True (to prevent users from trying to alter the contents). I also use a different background colour for lookup controls to differentiate them from user data entry controls.

-Stewart

One more question. If the DLookup fails to find a valid return value, the textbox will display "# Error". How to let the textbox display nothing in such a case? Thanks for your time.

[Stewart Ross]

Dlookup itself returns null if it cannot match a value. The '#Error' value returned arises typically because the criteria value passed to DLookup is null (this will always be the case on a new record, for instance). To guard against this you can enclose your DLookup in an IIF statement to test for null and return a null in these circumstances like this:

=IIF(IsNull([ProductID], Null, DLookup(... as before ...))

-Stewart

[yltang]

Dlookup itself returns null if it cannot match a value. The '#Error' value returned arises typically because the criteria value passed to DLookup is null (this will always be the case on a new record, for instance). To guard against this you can enclose your DLookup in an IIF statement to test for null and return a null in these circumstances like this:

=IIF(IsNull([ProductID], Null, DLookup(... as before ...))

-Stewart

Problem solved! Thanks a lot, Stewart.