DLookup shows primary key and not corresponding value in lookup table

[apk19]

I have a subform with a few unbound text boxes using DLookup to find values in a query. All of the textboxes (and DLookup functions) work except one and I think I know why but don't know how to solve it.

The function in question looks up a value in a table that uses a lookup function into another table, which has only two columns: the PK and the value. The DLookup sees this and returns the PK when I actually want it to return its value.



I know this is breaking Law 2, but there are 109 different values in that table and those values are used either once or twice for every record.

The problem is that I don't know what to change to fix this: The table/field that uses the lookup (trying to bind the value), the query (looking up the value in the lookup table), or the DLookup function itself. I've tried tinkering with them all to no avail.

[Micron]

If you MUST persist with the lookup field, my suggestion would be to either
- query the query that uses the PK to return the value (add the second table to this query)
- or create a custom function that uses the ID returned by DLookup. The function would construct a sql statement and assign it to the control.
Just guessing - I've always avoided lookup fields like the plague.

[apk19]

Hi Micron,

Yes, unfortunately it was a necessity...but thank you for the suggestions! I managed to get it up and running using a subquery and using DLookup directly onto that query.