I have a subform called transactions. On it I have a combobox with the
following options:Pending, Cleared, NSF, Closed, Bad. I would like to
place a box on my main form and if any record on the subform shows the
word "Bad" I would like to display this on the main form, kind of like
a warning message.
Any help is most appreciated
TIA

In news:(E-Mail Removed),
(E-Mail Removed) <(E-Mail Removed)> wrote:
> I have a subform called transactions. On it I have a combobox with the
> following options:Pending, Cleared, NSF, Closed, Bad. I would like to
> place a box on my main form and if any record on the subform shows the
> word "Bad" I would like to display this on the main form, kind of like
> a warning message.
> Any help is most appreciated

It's much easier to look in the table on which the subform is based,
rather than looking in the subform itself. To do that, you need to
specify (1) the name of the table, (2) the names of the fields in the
main form and the subform that link the subform to the main form, and
(3) the name of the field in the subform's table that contains the "Bad"
marker value, and what that value is. The fact that a combo box is used
on the subform to display the information may hide that from you. If
that field is actually a text field containing the value "Bad", then you
can use a text box on the main form with a ControlSource similar to
this:

=DLookup("StatusField", "Transactions",
"AccountID=" & [AccountID] & " AND StatusField='Bad'")

(Note: the line breaks in the above are just for readability in this
post. In the form itself, the controlsource expression must all be on
one line.)

The above controlsource expression is based on the following
assumptions:

1. "Transactions" is the name of the table the subform gets its data
from.

2. "AccountID" is the name of the field in Transactions that links
it to the main form's table, and is also the name of the control on the
main form to which the subform is linked.

3. "StatusField" is the name of the field in Transactions that
contains the status option "Bad".

4. StatusField is a text field that actually contains the literal
value "Bad".

Naturally, you should modify the field and table names above to accord
with your real setup. And if the status field, whatever its true name,
doesn't actually contain the literal value "Bad", the expression will
have to be modified. For example, if it's really a numeric field
containing values from 1 to 5, with 5 representing the "Bad" status, you
could use an expression similar to this:

=IIf(DLookup("StatusField", "Transactions",
"AccountID=" & [AccountID] & " AND StatusField=5")=5,
"Bad", "")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)