Lookup and display a value from a table based on value in other cell?

[Rich_B]

Hi

I have a textbox that I wish to auto populate with data based on the value entered into an adjacent box.

QUOTE_REF_1 is a four digit integer say "1287".

When 1287 is entered into the QUOTE_REF_1 control I want Access to search my table tblCLIENT_DETAILS and find the 1287 row and return the value from the CLIENT_NAME field on that row and display it in the textbox control CLIENT_NAME on my form.

I'm sure this can be done but I'm struggling.

Any guidance appreciated.

Thank you

 

[sneuberg]

I think DLookup https://support.office.com/en-us/article/DLookup-Function-8896cb03-e31f-45d1-86db-bed10dca5937 would do what you want. You would put it in the control source of the text box that is to displayed the client name something like:

=DLookUp("CLIENT_NAME", "tblCLIENT_DETAILS", "[SomeFieldName] = Forms!theformname!QUOTE_REF_1" )

Where you have to substitute your form name for theformname and the real table field name for SomeFieldName. The field, SomeFieldName is assumed to be a number in this example.

 

[sneuberg]

Please note that I edited my previous post and changed the control reference from a relative reference to a full reference in the DLookUp example.

 

[Rich_B]

Please note that I edited my previous post and changed the control reference from a relative reference to a full reference in the DLookUp example.

Thank you for the original post and the amendment.

It is now working perfectly.

Much appreciated.