Conditional format datasheet view subform

[Geezer]

I have searched the web for an answer to this qustion, but do not find
anything that addresses my specific situation. I've only been using Access
for a few months so please excuse me if I leave out pertinent information in
the setup below..

I have a main form in single form view [ view_form] on which are
several subforms. The main form contains a combo box (combo0) used to pull
up records and display more detailed information in the subforms.

One subform [csmocode_view_subform] is in datasheet view, gets it's data
from the table [csmocode_tbl]. This table contains two fields [CS MoCode]
and [ProjNum]. ProjNum is the field used in combo0 (child/parent link )
located on the main form. I have [CS MoCode] only, visible/displaying in the
subform. (MoCodes are essentially accounts that exist for the specific
record. ProjNum is the "name" of grants that I'm tracking in the DB.)

A second subform [name_qry subform4] is based on the table [mocode_person_tbl]
.. This table has three fields [ID] which is autonumber, [MoCode] which is
text, and [Person], which is a number. (This table "associates" MoCodes with
specific people. I.E. Joe Blow is associated with MoCode E1000. Any given
MoCode may be associated with any number of people and any number of people
may be associated with a given MoCode. The two tables are linked - one to
many - on [MoCode].[mocode_person_tbl] as many and [CS MoCode].[csmocode_tbl]
as one.)

I am trying to change the backcolor in the display of [CS MoCode] in the
subform [csmocode_view_subform] for each [CS MoCode] that is NOT found in the
table [mocode_person_tbl]. This will highlight the fact that a MoCode exists,
but no person has yet been "associated" with it.

I've tried variations of: DCount("[CS MoCode]","mocode_person_tbl")>0 in
the Expression Is dialog box. (Included in my attempts is full addressing:
DCount("[Forms]!view_form!csmocode_view_subform.form.[CS MoCode]",
"mocode_person_tbl") using dots, !, with and without the .form, but the
expression does not resolve to the answer I'd expect.

Any suggestions for an appropriate expression or code to use on the
main/subform?
Thanks

 

[Allen Browne]

Try something like this in the Expression of your Conditional Formatting:

IsNull(DLookup("ID", "mocode_person_tbl", "MoCode = """ & [CS MoCode] &
""""))

Performance will be woeful, but the general idea is to use DLookup() to see
if the value in the [CS MoCode] can be found in the mocode_person_tbl. If it
can, DLookup() returns the primary key. If it cannot DLookup() will return
Null. The expression uses IsNull() to return True if the value was not
found. DLookup() just looks for the first value (which will be quicker than
asking it to count how many there are.)

There's a faster function that DLookup() here:
http://allenbrowne.com/ser-42.html
but performance will still be pretty bad.

There are much faster alternatives (such as a subquery), but that would make
the subform read-only which is probably no use to you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have searched the web for an answer to this qustion, but do not find
anything that addresses my specific situation. I've only been using
Access
for a few months so please excuse me if I leave out pertinent information
in
the setup below..

I have a main form in single form view [ view_form] on which are
several subforms. The main form contains a combo box (combo0) used to
pull
up records and display more detailed information in the subforms.

One subform [csmocode_view_subform] is in datasheet view, gets it's data
from the table [csmocode_tbl]. This table contains two fields [CS MoCode]
and [ProjNum]. ProjNum is the field used in combo0 (child/parent link )
located on the main form. I have [CS MoCode] only, visible/displaying in
the
subform. (MoCodes are essentially accounts that exist for the specific
record. ProjNum is the "name" of grants that I'm tracking in the DB.)

A second subform [name_qry subform4] is based on the table
[mocode_person_tbl]
. This table has three fields [ID] which is autonumber, [MoCode] which is
text, and [Person], which is a number. (This table "associates" MoCodes
with
specific people. I.E. Joe Blow is associated with MoCode E1000. Any
given
MoCode may be associated with any number of people and any number of
people
may be associated with a given MoCode. The two tables are linked - one to
many - on [MoCode].[mocode_person_tbl] as many and [CS
MoCode].[csmocode_tbl]
as one.)

I am trying to change the backcolor in the display of [CS MoCode] in the
subform [csmocode_view_subform] for each [CS MoCode] that is NOT found in
the
table [mocode_person_tbl]. This will highlight the fact that a MoCode
exists,
but no person has yet been "associated" with it.

I've tried variations of: DCount("[CS MoCode]","mocode_person_tbl")>0 in
the Expression Is dialog box. (Included in my attempts is full
addressing:
DCount("[Forms]!view_form!csmocode_view_subform.form.[CS MoCode]",
"mocode_person_tbl") using dots, !, with and without the .form, but the
expression does not resolve to the answer I'd expect.

Any suggestions for an appropriate expression or code to use on the
main/subform?
Thanks

 

[Geezer via AccessMonster.com]

Excellent Allen! TYVM I had tried variations of DCount and DLookup, but
failed to get all the & and " in the right place and the correct number of
them. This works. I will also look at the ELookup article. Finally, I
appreciate your explanation rather than simply an answer. Now, perhaps I can
suss out the answer myself next time. (Because there WILL be a next time
Thanks

Try something like this in the Expression of your Conditional Formatting:

IsNull(DLookup("ID", "mocode_person_tbl", "MoCode = """ & [CS MoCode] &
""""))

Performance will be woeful, but the general idea is to use DLookup() to see
if the value in the [CS MoCode] can be found in the mocode_person_tbl. If it
can, DLookup() returns the primary key. If it cannot DLookup() will return
Null. The expression uses IsNull() to return True if the value was not
found. DLookup() just looks for the first value (which will be quicker than
asking it to count how many there are.)

There's a faster function that DLookup() here:
http://allenbrowne.com/ser-42.html
but performance will still be pretty bad.

There are much faster alternatives (such as a subquery), but that would make
the subform read-only which is probably no use to you.

I have searched the web for an answer to this qustion, but do not find
anything that addresses my specific situation. I've only been using

[quoted text clipped - 47 lines]

main/subform?
Thanks