By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,767 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

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

P: 11
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.
Sep 2 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
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
Sep 2 '08 #2

P: 11
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.
Sep 2 '08 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[ProductName]", "[tblProduct]", "[ProductID] = " & [ProductID])
  2. =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
Sep 2 '08 #4

NeoPa
Expert Mod 15k+
P: 31,344
At its simplest I think you need to build your form on a query with SQL similar to the following :
Expand|Select|Wrap|Line Numbers
  1. SELECT tO.OrderID,
  2.        tO....,
  3.        tP.ProductName
  4.  
  5. FROM tblOrder AS tO LEFT JOIN tblProduct AS tP
  6.   ON tO.ProductID=tP.ProductID
  7.  
  8. ...
Sep 2 '08 #5

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[ProductName]", "[tblProduct]", "[ProductID] = " & [ProductID])
  2. =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.
Sep 3 '08 #6

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[ProductName]", "[tblProduct]", "[ProductID] = " & [ProductID])
  2. =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.
Sep 3 '08 #7

Expert Mod 2.5K+
P: 2,545
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
Sep 3 '08 #8

P: 11
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.
Sep 4 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.