text box on form to display content of subform

  • Thread starter Thread starter papa jonah
  • Start date Start date
P

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
 
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.
 
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.
 
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
 
Back
Top