enter specific data in control

[Loni]

I have a main form based on one table. When ControlA on
this form is updated, I need to show the last value of the
subform's ControlB(subform based on a seperate table with
1:m relationship) that is related to the value in the main
form's ControlA in a box on the main form. This would be
strictly for show and could not be accessed or changed.
I think I need to do a query in the AfterUpdate event of
ControlA but I'm not sure what type of box or control I
need to display the result or how to put the resulting
data into the control.
Thanks Much,
Loni

 

[John Nurick]

Hi Loni,

Probably you need a textbox to display the value and a DMax(), DMin() or
DLookup() expression to retrieve it. Which you need depends on what you
mean by "the last value".

If this is the maximum value displayed in ControlB, it would be
something like
=DMax("FieldB", "SubTable", "FieldA=" & Forms!MainForm!ControlA)
, or
=DMax("FieldB", "SubTable", "FieldA=""" & Forms!MainForm!ControlA &
"""")
if FieldA/ControlA hold text values.

If the last value is the lowest, use DMin() instead of DMax().

If the "last value" in ControlB depends on sorting by some other field,
you'll need to create a query to return the (single) correct value, e.g.

SELECT TOP 1 FieldB
FROM SubTable
WHERE FieldB=Forms!Mainform!ControlA
ORDER BY FieldX
;

You can then use DLookup in the textbox, e.g.
=DLookup("FieldB", "TheQuery")