Text Box results based upon separate input (1 Viewer)

[dab1477]

Thanks in advance for the assist.

I have a CODE table with 2 columns, Code & Description. I have a form where I input a CODE value; I want a second text box to retrieve the corresponding description from the CODE table. I'm a newbie and am unable to make this occur successfully. I don't want to choose from a combo box, I simply want the second box to return the value in Code Table, column 2, based upon matching the value from Text Box1 to column 1 in the Code Table.

Thanks.

 

[pbaldy]

The combo would be much easier; users can still type into it, they don't have to scroll/select. If you want to use a textbox, you'll either need to use a DLookup or open a recordset to get the corresponding value, using the after update event of the textbox.

 

[dab1477]

Thanks. Not sure I made my situation clear. Thanks for you patience...

I have a table (tbl_InpspectionData). This table has the following 6 columns:
ID Date InspectionDept ReasonCode ReasonCodeDescription Qty

Because of the data collection method, operators input a CODE as an indicator of scrap/reject for EACH occurrence. There may be 40 - 50 different entries. I have setup the form (multi -input) to INPUT The following:

Date InspectionDept ReasonCode and QTY.

The form also has a TEXTBOX for ReasonCodeDescription. This is NOT an input filed, but I simply want it to display. When I input a number (i.e. 40) into the ReasonCode textbox of the form, upon entry into (i.e Got Focus or Entry) the ReasonCodeDescription textbox of the form, displays the corresponding Description. I want the proposed code or function to look at ReasonCode textbox of the Form, match that value within tbl_CodeDescription, and return the corresponding ReasonCodeDescription from the same table into the Form's ReasonCodeDescription textbox. I can do this in EXCEL using VLOOKUP. I an unable to do this within the form.

I am trying DLOOKUP within the control source of the ReasonCodeDescription unsuccessfully. The function looks like this: =DLookUp([Code Description]![Description],[Code Description],[Code Description]!

=[Reason Code_input]). It returns the error #NAME within the ResonCodeDescription textbox of my Form.

Thanks for any assist provided.

 

[pbaldy]

It was clear; the combo is easiest (the combo would be for the code):

http://www.baldyweb.com/Autofill.htm

If you want to use the less efficient DLookup, your syntax is off:

http://www.mvps.org/access/general/gen0018.htm

 

[dab1477]

Thanks so much for the clarification. It worked well. I ended up taking your advice and using a combo box. Sometimes I'm just a hard head! Thanks again. I learned something.

 

[pbaldy]

Happy to help, and welcome to the site by the way!

 

[Laura1]

This post sounds very similar to the issue I've encountered, only in my case the combo box wasn't working because the field in the table I want to display is in itself a lookup to a third table, so when I display it in the combo box, it displays the ID# to the third table that is saved in that field of the 2nd table instead of the display value itself.

I tried the DLookup too but with no luck. I would prefer to find a solution using the combo box.

I can correct the problem by changing that field in the 2nd table from a lookup to another table to a hardcoded lookup to a predefined list, only the list is 500 items long so I'd rather not do that.

Is there a way to force the program to display the value from the 3rd table, not the ID recorded in that field of the 2nd table?

 

[Laura1]

Thanks so much for any help you can provide...

 

[pbaldy]

Can the combo be based on a query that joins the two tables?

 

[Laura1]

... probably, to be honest I haven't gotten into queries too much yet... Can you recommend a tutorial that can show me how to do that?

 

[Laura1]

Probably the standard Microsoft tutorial will help... I will try that and post an update when I've had the chance to review it. (probably not today, I'm on vacation, just trying to catch up)

 

[Laura1]

I've tried using a query, and unfortunately the result is the same. It displays the ID# of the product from the other table, not the product name.

I should explain what I'm trying to do a little better.

I have 3 tables:
TBL_PRODUCTS. This table is a list of products. This includes die-level, submodule and finished module products.
TBL_KIT. This table collects information about which products are linked to which workorders (WO). Workorders are used for all levels of products (die, submodule, finished module)
TBL_TRACK. This table collects information about which workorders (die level and submodule level) were built into workorders at the finished level.

I am now working on the Form for operators to record data in the TRACK table. In this form, they will need to enter both the workorder of the build they are working on, plus the workorders (multiple) of all the submodules and die that are being built into it. When the operator selects the workorder from the combo box (which is looking into the KIT table), it's important that the product associated with that workorder be displayed (not recorded in the table, only displayed) so that the operator can verify that they have typed in the correct workorder#.

So I am using the Lookup wizard to set up all the workorder fields inside the TRACK table as a LOOKUP to:
SELECT [TBL_KIT].[K_Workorder],[TBL_KIT].[K_Product] FROM [TBL_KIT] ORDER BY [K_Workorder] DESC;
Then I am using the Form wizard to create my form, but because the field in [TBL_KIT].[K_Product] is itself a lookup to TBL_PRODUCTS, it is displaying the ID# for the product, not the product name. It is doing this whether I use the Table or the Query.

As I mentioned in my original post, I can remove this issue by changing the [TBL_KIT].[K_Product] field to a defined selection list, however I don't want to do that for 2 reasons:
2. there are about 500 products in the list and I'm not even sure if the program will allow that many...
1. the PRODUCTS table is being used elsewhere too. When we add new products, I would have to manually update all locations where it is being looked up in all forms... not easy to maintain!

I was hoping there would be a way to force the software to display the display value and not the bound value. Or secondly, I was hoping to add a text field to the form, which would just use the data the operator entered into each workorder field and display a lookup to the PRODUCTS table, but I wasn't able to get that working. (I was using DLOOKUP) I could use the wizard to get one working, but when I added more it was always only looking up the first WORKORDER, not the 2nd workorder entered into the form etc. I searched the code and wasn't able to figure out how even the first box knew how to display it!!

Thank you so much for any help you can provide...

 

[Laura1]

... ok, it is working now, I had setup my Query originally to use the product field from the KIT table, but of course it should have been in the PRODUCTS table.

I'm still wishing to add a text field though that would display the PRODUCT name inside the form though. If the operators merely type in the WO#, they won't see the product to verify it.... they will only see it if they use the drop-down list.

I'm going to continue to try to do that. I will submit a new request if I run into issues.

Thanks so much for your help!

 

[pbaldy]

The link above should help you do that.

 

[Laura1]

Hi pbaldy, I've managed to make it work, but I've noticed that if I change my workorder entry in the form, the value in the text box does not change.

To explain, here is my syntax, which I've entered into the Control Source of the unbound text box:
=DLOOKUP("Product","QRY_WO_HYBRID","K_Workorder=SMT_Workorder")
SMT_Workorder is the value that is being entered into the form. If that value changes, the form is still only reflecting the data that was originally entered into the field.

Is there a way to make it update every time the value in that field changes?

Thanks so much for your patience...

 

[Laura1]

It seems to be working now, I had posted in a new forum (because I felt bad for cc'ing dab1477 on all my correspondence about my issue, not knowing how long it would take to solve...) and it was suggested that I add the DLOOKUP to the AfterUpdate and change my syntax in both locations to:

=DLOOKUP("Product","QRY_WO_HYBRID","K_Workorder=" & [SM T_Workorder])

That seems to have it working now.

Thanks so much for all your help!!