 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Populate text box based on entry in another text box
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. [quoted text clipped - 14 lines] > >Any help is most appreciated...thanks! 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. >>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. [quoted text clipped - 36 lines] > > John W. Vinson[MVP] 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 [quoted text clipped - 11 lines] >> >> 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... 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 [quoted text clipped - 3 lines] > >What am I doing wrong? Thanks again. 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] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.