Populate text box based on entry in another text box

  • Thread starter Thread starter AimeeK via AccessMonster.com
  • Start date Start date
A

AimeeK via AccessMonster.com

Hello, I'm trying to set up a form in which if a user types a product code
into one text box, in the next text box the product's description is returned.
Here are the fields and controls involved.

[EXCEPT PROD CODE1]...control name is Text260 (I'm very new at developing
these things, so I never got around to renaming the controls)...this is where
the user would enter a product code

[EXCEPT PROD DESC1]...control name is Text227...this is where the description
would automatically be filled in based on the product code entered in the
previous text box

I'm thinking DLookup might be the answer, but I'm having trouble grasping
this. I do have a table listing the product codes and descriptions, which is
named Z006. I need to know in which controls to put the code, which events
in the control should have the code, as well as the syntax.

Any help is most appreciated...thanks!
 
Hello, I'm trying to set up a form in which if a user types a product code
into one text box, in the next text box the product's description is returned.
Here are the fields and controls involved.

[EXCEPT PROD CODE1]...control name is Text260 (I'm very new at developing
these things, so I never got around to renaming the controls)...this is where
the user would enter a product code

[EXCEPT PROD DESC1]...control name is Text227...this is where the description
would automatically be filled in based on the product code entered in the
previous text box

I'm thinking DLookup might be the answer, but I'm having trouble grasping
this. I do have a table listing the product codes and descriptions, which is
named Z006. I need to know in which controls to put the code, which events
in the control should have the code, as well as the syntax.

Any help is most appreciated...thanks!
Click to expand...

Stop.

If you are trying to copy the product description from the Product
table into another table - DON'T. Storing the description redundantly
wastes space, and risks update anomalies; if the description in the
product table changes (say to correct an error), you now have an
unknown number of other records to track down and correct.

Instead, remove the [EXCEPT PROD DESC1] field from your form's
recordsource table altogether, and set the textbox Text227's Control
Source to

=DLookUp("[PROD DESC]", "[PRODUCTS]", "[PROD CODE1] = " & [Text260])

This assumes that the product code is numeric, you'll need quotemarks
if it's text:

"[PROD CODE1] = '" & [Text260] & "'"

Even better, replace the Product Code textbox with a Combo Box so that
the user can select products from a list rather than having to type
each one in. You can set the control source of Text227 to

=cboProdCode.Column(1)

to automatically display the second column (it's zero based) of the
combo box to let you see both the code and the description on the
form.

If you have some GOOD reason to copy the description from one table to
another, post back - it can be done, it's just usually not a good
idea.

John W. Vinson[MVP]
 
John, Thank you for your reply...however, I can't do a combo box because we
have thousands of products, and I don't want the user to have to scroll
through the list to find each one...I copied your code into the control
source for Text227 like you said, and I keep getting an error...I had to make
a couple of changes to it, but this what I have:

=DLookUp("[PROD DESCR]","Z006","[EXCEPT PROD CODE1]" = " & [Text260]")

What am I doing wrong? Thanks again.

John said:
Hello, I'm trying to set up a form in which if a user types a product code
into one text box, in the next text box the product's description is returned.
Click to expand...
[quoted text clipped - 14 lines]
Any help is most appreciated...thanks!
Click to expand...

Stop.

If you are trying to copy the product description from the Product
table into another table - DON'T. Storing the description redundantly
wastes space, and risks update anomalies; if the description in the
product table changes (say to correct an error), you now have an
unknown number of other records to track down and correct.

Instead, remove the [EXCEPT PROD DESC1] field from your form's
recordsource table altogether, and set the textbox Text227's Control
Source to

=DLookUp("[PROD DESC]", "[PRODUCTS]", "[PROD CODE1] = " & [Text260])

This assumes that the product code is numeric, you'll need quotemarks
if it's text:

"[PROD CODE1] = '" & [Text260] & "'"

Even better, replace the Product Code textbox with a Combo Box so that
the user can select products from a list rather than having to type
each one in. You can set the control source of Text227 to

=cboProdCode.Column(1)

to automatically display the second column (it's zero based) of the
combo box to let you see both the code and the description on the
form.

If you have some GOOD reason to copy the description from one table to
another, post back - it can be done, it's just usually not a good
idea.

John W. Vinson[MVP]
Click to expand...
 
I forgot to add that the "Z006" portion is the name of the table where the
product code would be "looked up" and then the product description would be
pulled over from this table.
John, Thank you for your reply...however, I can't do a combo box because we
have thousands of products, and I don't want the user to have to scroll
through the list to find each one...I copied your code into the control
source for Text227 like you said, and I keep getting an error...I had to make
a couple of changes to it, but this what I have:

=DLookUp("[PROD DESCR]","Z006","[EXCEPT PROD CODE1]" = " & [Text260]")

What am I doing wrong? Thanks again.
[quoted text clipped - 36 lines]
John W. Vinson[MVP]
Click to expand...
Click to expand...
 
John, Thank you for your reply...however, I can't do a combo box because we
have thousands of products, and I don't want the user to have to scroll
through the list to find each one...
Click to expand...

Well, they don't really: if your product codes are well behaved, you
could use the Autocomplete option of the combo. Typing the first few
characters of the code (or of the description, if that's the first
visible field in the combo) will jump to that row in the combo box.
But you're right, thousands of rows is awfully big for a combo!
I copied your code into the control
source for Text227 like you said, and I keep getting an error...I had to make
a couple of changes to it, but this what I have:

=DLookUp("[PROD DESCR]","Z006","[EXCEPT PROD CODE1]" = " & [Text260]")

What am I doing wrong? Thanks again.
Click to expand...

You have an extra doublequote after the = sign and another after the
[TEXT260]. Are the fieldnames in table Z006 in fact [EXCEPT PROD
CODE1] and [PROD DESCR]? What's the datatype of [EXCEPT PROD CODE1]?
If it's Number then you should have no delimiters:

=DLookUp("[PROD DESCR]","Z006","[EXCEPT PROD CODE1] = " & [Text260])

and if it's Text you need either singlequote delimiters

=DLookUp("[PROD DESCR]","Z006","[EXCEPT PROD CODE1] = '" & [Text260] &
"'")

or (if the code might contain an apostrophe) doublequote delimiters;
to enter a doublequote in a doublequote quoted string use a double
doublequote:

=DLookUp("[PROD DESCR]","Z006","[EXCEPT PROD CODE1] = """ & [Text260]
& """")

John W. Vinson[MVP]
 
Back
Top