text box on form to display content of subform

[papa jonah]

I have a subform that allows for entry of up to three codes for every
record on the main form. I want a text box (on the main form) to
display a phrase IF any one of the codes entered on the subform is
="10A2".

How do I get it to look at the different entries to determine if any of
the records match?

TIA

 

[Allen Browne]

Probably easiest to perform a DLookup() on the related table

The Control Source for this text box will be something like this:
=IIf(DLookup("Field1", "Table1", "(Field1 = '10A2') AND (Field2 = "
& Nz([Field3],0) & ")") Is Null, Null, "Message to show.")

where:
Field1 = the Text field that might 10A2;
Table1 = the subform's table;
Field2 = the foreign key of the subform (assumed to be a Number field.)
Field3 = the primary key of the main form.

 

[papa jonah]

Beautiful, thanks.

 

[papa jonah]

Allen,
That did not work - I'm not sure why. At first it just gave me an
error#. Then I added square brackets and then the filed just sat there
and flashed.
When I first attempted this, this is the code I used. It worked as long
as the first of multiple codes was 10A2. If the 10A2 was the second or
third code entered, it returned "NO".

=IIf([Subgroup]![Subgroup]="10A2","Management","NO")

As I don't understand what your suggested code does (I don't speak the
language yet), I don't know how I can use it to make this work. What
would you suggest?

Thanks for your help.

 

[Allen Browne]

Rather than loop through all the records in the subform, I'm suggesting that
we lookup the subform's table directly.

Your subform could contain 3 rows.
The field in the subform that could contain the 10A2 is named _____?
The table that this subform field comes from is named ______?
The subform's table shows the records that match the one in the main form.
The _______ field in the subform's table matches the _______ field in the
main form?

Fill in the blanks. The example I posted used 4 names for those 4 blanks.

There's a basic description of DLookup() and how it works in this article:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html