Lookup field value in table from a query

[TomMcCray]

I'm sure my answer is here somewhere in the forum, but I'll be darned if I can find it with various keyword searches.



I have an MS Access 2013 database I have inherited that has significant existing reports, tables, forms and queries.


As a result, I'm reluctant to make significant changes since it seems things are working, but I need to add some values in a table.


In the table "tblX" I want one field ("fldX") to obtain its value from an existing query ("qryY") based on a field common to both ("fldY").


I have created a Lookup in fldX, and I can get the value using a combobox ONE time, but if the value in qryX changes, the value in the tblX fldX lookup does not update.


So, basically I want fldX to reflect the value in fldY of qryY, and to be "hot-linked" so if the value in qryY changes, the value in tblX fldX updates automatically.


Surely possible within a lookup query tblX, but I'm missing something...


Thanks in advance!

[Ajax]

using lookups in tables is a bad idea - use a form.

[TomMcCray]

using lookups in tables is a bad idea - use a form.

Yes, yes, I know. I have seen the "warnings", even though it is a canned function ("Lookup Wizard") in MSACCESS.

Creating a form (and hooking to a table) just to do what a simple "VLOOKUP" does natively in Excel seems a bit ridiculous.

Surely there is a simple solution.

[kd2017]

Ajax,
How dare you, an access guru and veteran of this forum, draw from your years of experience to give this person good advice and "warnings" when they clearly already know better... How dare you?!

[ssanfu]

I have created a Lookup in fldX, and I can get the value using a combobox ONE time, but if the value in qryX changes, the value in the tblX fldX lookup does not update.

That problem is well known and why the "WARNINGS".

..... even though it is a canned function ("Lookup Wizard") in MSACCESS.

Yep, everything MS does woks perfectly! Well, except for Windows (all of the "Service packs" and weekly "Updates")

Creating a form (and hooking to a table) just to do what a simple "VLOOKUP" does natively in Excel seems a bit ridiculous

Except Access and Excel are two completely different object models.......

Surely there is a simple solution.

Well, yes. DON'T use table level look up fields.

[Minty]

If the value can be looked up the simply make a form based on a query to present/display the data in the way that you want.

Fundamentally, you are confusing a table that stores data, as a place to work with the data, which it is not and never should be.
Use a form with a combo as the simple equivalent of a VLookup.

I have just removed about 30 "lookup-fields" from a clients system that were causing performance issues, difficulty with modifying forms and generally being a PITA!