CODEWITHSUNDEEP
ms-accessms-access-2010
sigil
sigil

Reputation: 9546

Add column to table form to show value from a query

I have table x, where x.b is the primary key:

+-----+-----+
|  a  |  b  |
+-----+-----+
| xyz | 123 |
| abc | 456 |
| abc | 999 |
+-----+-----+

Table y, where y.b is the foreign key for x.b:

+----+-----+-------+
| ID |  b  |   c   |
+----+-----+-------+
|  1 | 123 | x105  |
|  2 | 123 | a309  |
|  3 | 456 | b123  |
|  4 | 999 | q234  |
|  5 | 999 | z525  |
+----+-----+-------+

A query yQuery based on y to find the value of c for the highest ID for each b, which results in:

+----+-----+-------+
| ID |  b  |   c   |
+----+-----+-------+
|  2 | 123 | a309  |
|  3 | 456 | b123  |
|  5 | 999 | z525  |
+----+-----+-------+

I have a form xForm that's currently displaying table x. I want to add a column that shows the c result from yQuery, so that xForm would look like this:

+----+-----+-------+
| ID |  b  |   c   |
+----+-----+-------+
|  2 | 123 | a309  |
|  3 | 456 | b123  |
|  5 | 999 | z525  |
+----+-----+-------+

I tried adding a textbox to xForm where the control source of the textbox is:

=[yQuery]![c]

But that just gave me a column of #Name? errors. I'm not sure how to set up the textbox so that its source is the xForm!ID field.

Upvotes: 1

Views: 538

Answers (2)

Angus Walker
Angus Walker

Reputation: 398

you could just use this dlookup instead:

=DLookup("[c]","yQuery","[b] = " & [Control Name for B in your form])

or maybe

=DLookup("[c]","yQuery","[b] = """ & [Control Name for B in your form] & """")

if b is not numeric

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123419

One option would be to have the form bound to a query that pulls the information from table [x] joined with query [yQuery] on [ID]. However, if [yQuery] has a GROUP BY clause then any query that incorporates [yQuery] might produce a recordset that is not updateable.

Another option would be to use a DLookup() as the Control Source for the textbox in question, something like

=DLookup("c","yQuery","ID=" & [ID])

Upvotes: 1

Related Questions