text box on form to display content of subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

papa jonah - 18 Nov 2005 18:22 GMT

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

Reply to this Message

Allen Browne - 19 Nov 2005 08:59 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 3 lines]
> How do I get it to look at the different entries to determine if any of
> the records match?

Reply to this Message

papa jonah - 21 Nov 2005 15:15 GMT

Beautiful, thanks.

Reply to this Message

papa jonah - 22 Nov 2005 16:23 GMT

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.

Reply to this Message

Allen Browne - 23 Nov 2005 01:33 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
> That did not work - I'm not sure why. At first it just gave me an
[quoted text clipped - 11 lines]
>
> Thanks for your help.

Reply to this Message